Bulk Insert from csv having issues with double quotes

Question

Hi Team,

I am using Bulk Insert to load the data from CSV into SQL Table.

I have used , (comma) as a field terminator and it was working fine.

But in my csv file I have the Project Manager field which has in the format as (Lastname, Firstname)
eg: Ramasamy, Sarath.

The project manager field is separated using double quotes, while doing bulk insert the fields are seperated and filled into table in tow columns

Please suggest a way to fix.

Thanks - Sarath

Answer 1

Hi @Sarath ,

We can use Flat File Source and OLEDB Destination to load data from csv file to sql table in ssis package.

Please set Text qualifier as " in General page of Flat File Connection Manager Editor.

Please refer to the following pictures:

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

BCP cannot handle double quotes sometimes and not other times. The string fields must be ALL double quoted all the time.

The only way to import the data as shown, would be to use SSIS. In SSIS, you can specify a "text qualifier" and it is technically optional, and will import the data as expected.

Answer 3

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2016 - use the FIELDQUOTE option.