martyuni
asked on
Problem importing a comma delimited file
I am building an SSIS package in SQL Server 2008. One part of it is Data Flow Task that has a flat file being imported into a table in my database. I go into the task and create a Flat File Source. I point it to a txt file provided by another department. The file has fields seperated by commas and the data inside of double quotes for each peice, but it is not a csv file type. So I set the format as delimited, text qualifier as double quote, and column delimiter as comma. My problem is that in one of the fields they have a comma in the data which the connector is making into a new field. Here is an example:
"LastName","FirstName","Cl
This is being imported as
LastName
FirstName
Clerk II
Health
Physical Educ & Leisure Services (instead of Health, Physical Educ & Leisure Serivces)
How can I import that correctly?
"LastName","FirstName","Cl
This is being imported as
LastName
FirstName
Clerk II
Health
Physical Educ & Leisure Services (instead of Health, Physical Educ & Leisure Serivces)
How can I import that correctly?
Although technically not a solution...When I'm given a file I normally like to given delimiters that won't cause be this kind of problem. I personally like pipe delimited sources but usually any character that you would never find in your fields will do. Can you contact the folks that is providing the file and see if changing the delimiter is an option?
ASKER
This file is used by more then just me so having a change now would not be possible.
I would say that the whoever else gets the file is probably having the same problem. Contact the other users...
ASKER
We are the only group using SQL Server. I also use this file on another import in a DTS package in SQL Server 2000 and this is not a problem. Something with SSIS not being able to do it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
my previous post #25664770 show the cause of this problem and solution exactly. Author just need to try it!
Regards,
Regards,
I objected already on this topic. solution is clear on my #25664770 comment.
Regards,
Regards,