Reading CSV file with comma and double quotes in Datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
Reading CSV file with comma and double quotes in Datastage
I'm getting input csv file as 1,"2,2.1",3,"4,4.1". This sample record is having 4 columns with value in first column 1, second column 2,2.1, third column 3 & fourth column 4,4.1, i.e. columns having comma is enclosed in double quotes. Double quotes can be on any column if it's value is having comma. Can anyone please suggest is there any simple way to read this kind of csv file.
Another requirement is, if a column value is having double quotes then it will be enclosed with another double quotes. For example, if fifth column is having value 5,"5.1" then whole record will look like 1,"2,2.1",3,"4,4.1","5,""5.1""".
Another requirement is, if a column value is having double quotes then it will be enclosed with another double quotes. For example, if fifth column is having value 5,"5.1" then whole record will look like 1,"2,2.1",3,"4,4.1","5,""5.1""".
SyedMuhammadMehdi
I'm curious - have you tried to read it yet? If so, what happened? The double-quotes should 'hide' the extra commas and treat them as data. If a Parallel job is having issues, try a Server job as they can handle flat files of that nature better. If for some reason that is not an option, try the Sequential File stage inside a Server Shared Container in your Parallel job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
Try to read the file with comma delimited and do not mention double quote in the job in file definition.
Later in transformer you can handle quote character.
Try to read the file with comma delimited and do not mention double quote in the job in file definition.
Later in transformer you can handle quote character.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
I had tried using unstructured stage but it is not working for csv and in no way parallel job sequential file is working for this. As you said server sequential will work will try it but I'm thinking how we can handle quotes in a particular column as in some case we will get quotes for a column and in some not based on comma.
SyedMuhammadMehdi
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India
-
- Participant
- Posts: 43
- Joined: Wed Feb 12, 2014 12:34 pm
- Location: Hyderabad, India