I am at the end of my wits trying to figure this out. So Help me please.
I have a simple variable length text file that uses pipe (|) as field delimiter and double quotes(") as text qualifier.
Here is a sample record -
"DMX MUSIC"|"Ch 01 S"|"OVERTURE to DER FREISCHUTZ (1817-21)"|"KUHN/STAATSKAPELLE DRESDEN/WEBER"|"CARL MARIA VON WEBER: OUVERTUREN"|"cpo"|"10052 1985"|"cpo RECORDS"|"07/01/2012"|"00:00:00"|"cpo RECORDS"|""|"0018950"
I am using the below schema file to read the text file and my job design is simple -
seq.filestage >>>>>>> odbc_connector stage.
//EO DB DMX detail table load
//Creatd On : 12/2013
//Creatd By : EJF
record nullable
{intact, record_delim='\n', final_delim=end,delim='|', quote=none}
(ServiceName:string[max=20];
Channel:string[max=20];
Title:string[max=2000];
Artist:string[max=2000];
Album:string[max=2000];
JunkRecordLableCode:string[max=20];
SuppCatalogID:string[max=200];
JunkRecordLableName:string[max=2000];
JunkPerfSttDt:string[max=20];
Duration:string[max=20];
LableName:string[max=2000];
LableParent:string[max=2000];
PlaylistFrequency:string[max=200]
![Wink ;)](./images/smilies/icon_wink.gif)
Note that i am using QUOTE = NONE in the schema file.
If i do that it works and the records are parsed correctly however, as expected, the leading and trailing double quotes become part of the data that is loaded in the table. Which is not what i want. I obviously want the data loaded in the table to be free of those infernal double quotes.
So, i change the QUOTE property in the schema file to quotes=double
But now the sequential file stage starts rejecting the row and seems to be unable to parse it.
I replaced all double quotes in my input file with the caret (^) and changed the schema file to say quotes='^' - same result. Record is rejected.
So the question is what do i need to do, what gods i need to pray to, what animal i need to sacrifice in order for me to specify a QUOTE in the schema file ?