Hi,
I have a data like below in CSV file:
1,N,"AAAAA L,BBBBBB R","CCCCCC DDDDD"" AAA FFF","NO 55""",FSFSFSD CVCVC
1 Column: 1
2 Column: N
3 Column: AAAAA L,BBBBBB R
4 Column: CCCCCC DDDDD" AAA FFF
5 Column: NO 55"
6 Column: FSFSFSD CVCVC
Can anyone suggest how to read the this csv file in datastage. I have tried reading using sequential file stage(Delimited as comma and Quotes as None), its not able to read 4 column.
Reading Double quotes in CSV file
Moderators: chulett, rschirm, roy
Unless something has changed that I'm not aware of, the advice I've seen here is to use a Server job and its sequential file stage, that or use it in a Server Shared Container in your PX job. For whatever reason, it can handle those embedded quotes much more better than the Parallel version can.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I am able to read it properly using server sequential file stage but the issue is I have many files to read. In server job I cannot pass schema file name. I have to hardcode the column names in server sequential file stage. So i cannot use single job to read multiple csv file.
Can you suggest some other way to achieve and read csv file by passing schema file (RCP)
Can you suggest some other way to achieve and read csv file by passing schema file (RCP)
Atul
You can leverage Unix's capabilities to meet this requirement as given below:
In Seq file stage's properties
In Seq file stage's properties
- 1. Set value of FILTER toThis command will replace the "delimiter commas" with "~" (assuming ~ does not occur in data), while commas within quotes will be ignored.
Code: Select all
nawk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);} print $0;}'
2. Set value of "Delimiter" to ~ (replacee delimiter).
Re: Reading Double quotes in CSV file
Craig, You have a point about embedded double quotes ... though with solution mentioned above, all columns are read, as embedded double quotes and commas etc no longer trip DataStage.atulgoel wrote:I have tried reading using sequential file stage(Delimited as comma and Quotes as None), its not able to read 4 column.