I have a source file in .csv format, and the delimiter for field is set to ',', but one of the column value has ',' as part of the value. This particular record failed. How should I fix?
Example, the file name is company.csv. The delimiter is set to ','.
The values are
company id company name
======= ==========
1 "ABC Corp"
2 "John, Smith, Son'
The 2nd record failed because it has ',' in the company name.
Thanks.
CSV file issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 48
- Joined: Wed Jun 01, 2005 7:10 am
If you are sure that you will get the values seperated by comma in the file Company Name, then read the entire record as a single column and then split based on the field length. Remember while splitting, the starting position of the second column will be n+1 as there is a comma seperating the first and second column.
Another option is to count the delimiter in each record and then using field function try splitting the columns provided you know the column where the column value will be seperated by comma.
Or ask the people responsible for generating the source file to provide the source file in other than comma delimited format, for example pipe "|" delimited.
Another option is to count the delimiter in each record and then using field function try splitting the columns provided you know the column where the column value will be seperated by comma.
Or ask the people responsible for generating the source file to provide the source file in other than comma delimited format, for example pipe "|" delimited.
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney