SSIS importing a CSV file with double quotes. The problem is having a (comma) in a field.

Please help me to import a CSV file and that has double quotes.
I could resolve that part; by adding " as textqualifier

The problem is that it has comma in the column
example
"Provider24.com, Inc.","M1020"

and many times there is no data within the double quotes
"","express.com","E510"

Please see that attached example.

thank you,
G
example.docx
sqlgauriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
One option is using this third party component.

http://ssisdfs.codeplex.com/
sachiekCommented:
Double click Flat File Connection Manager, input "Text qualifier" as ". This can guarantee anything between DOUBLE QUOTE will stay its original meaning.

Was this helpful -- below link.

https://www.mssqltips.com/sqlservertip/1316/strip-double-quotes-from-an-import-file-in-integration-services-ssis/
sqlgauriAuthor Commented:
Thanks Sachiek,

I have already done text qualifier as ".

But there are issues after that
1) there is a comma within the "Provider24.com, Inc.","M1020"
2) "" is used to show that there is no data in the same column.

If you open the doc attached I have highlighted the example.

At my end, 3rd party tools are not allowed on the production server.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hi sqlgauri,

I  have gone through the issue related to the csv file with double quoted data. I have tired to resolve your issue.

You can use the SSIS package for that. I think you have already used the same.

Configure the Flat File Connection as Below.

1) Select the Source of CSV file data.
2) In General tab select " as Text Qualifier.
3) In General tab is your first row represents the Column name then check the check box "Column in First Data row"
3) In Column tab select "," as Column delimiter.

I think it will resolve your issue.

I have attached sample data file and Screen shot of package in Zip. Check it.

Please put a comment if it does not work for you.
Solution.zip
sqlgauriAuthor Commented:
Thanks Arifhusen Ansari.

I had already done that part of your suggestion from http://mund-consulting.com/Blog/importing-csv-file-with-double-quotes-using-ssis/  before I asked for help here at experts-exchange.

The problem is that there is a comma with in the text of the data column.
And also there are some empty data columns with "".
sqlgauriAuthor Commented:
Thank you every one for sharing the solutions.

I corrected in the following steps
1) in staging sql table I got all the 100 columns ( I actually just needed 4) so earlier in the flat file I was just calling for the 4 columns.
2) and then inserted into the sql table (production) (the table had 4 columns)
3) and it worked ....yeah!

I did not realize that I should have got all the 100 columns earlier and was just trying to get the 4 columns from the flat file. And it failed till the end.

Learnt the importance of having a staging table.

Thank you ALL those who helped .

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlgauriAuthor Commented:
Hi vitor,
Please let me know how to close the issue?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is each comment you have two options:
Accept Multiple Solutions --> if the comment is part of many comments solution
Accept as Solution --> if only this comment was the solution

You can also click in Request Attention button at the end of the main question.
sqlgauriAuthor Commented:
I've requested that this question be deleted for the following reason:

I upgraded to the premium services
Vitor MontalvãoMSSQL Senior EngineerCommented:
Instead of requesting for deletion wouldn't be better to mark your own comment as solution?
sqlgauriAuthor Commented:
That's true, but it has been long time and do not recollect the solutions on the same.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.