Problem importing a comma delimited file

martyuni
martyuni used Ask the Experts™
on
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","Clerk II","Health, Physical Educ & Leisure Services"....

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?
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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?

Author

Commented:
This file is used by more then just me so having a change now would not be possible.

Commented:
I would say that the whoever else gets the file is probably having the same problem. Contact the other users...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
Consultant, Trainer
Commented:
User delimiter in your lines differently,
I mean, instead of :
"LastName","FirstName","Clerk II","Health, Physical Educ & Leisure Services"....
try something like this:
LastName"FirstName"Clerk II"Health, Physical Educ & Leisure Services"....

this will work correctly
Remember that your delimiter must be between your values only
if you use " as delimiter in first line then your columns will be:
[empty] /  [LastName] / [,] / [FirstName] / [,] / [Clerk II] / [,] / [Health, Physical Educ & Leisure Services] / ....

but in second line you get this results:
[LastName] / [FirstName] / [Clerk II] / [Health, Physical Educ & Leisure Services] / ....
Reza RadConsultant, Trainer

Commented:
my previous post #25664770 show the cause of this problem and solution exactly. Author just need to try it!
Regards,
Reza RadConsultant, Trainer

Commented:
I objected already on this topic. solution is clear on my #25664770 comment.
Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial