question

Sarath-2823 avatar image
0 Votes"
Sarath-2823 asked ErlandSommarskog commented

Bulk Insert from csv having issues with double quotes

Hi Team,


58406-image.png


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. 58504-image.png


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

58370-image.png



Please suggest a way to fix.

Thanks - Sarath

sql-server-transact-sqlsql-server-integration-services
image.png (16.3 KiB)
image.png (8.4 KiB)
image.png (5.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
1 Vote"
Monalv-msft answered ErlandSommarskog commented

Hi @Sarath-2823 ,

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:
58990-csv-file.png
59004-df.png
58965-ffcm-general.png
58967-ffcm-columns.png
58968-output.png

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.



csv-file.png (10.7 KiB)
df.png (97.5 KiB)
ffcm-general.png (19.6 KiB)
ffcm-columns.png (18.7 KiB)
output.png (22.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Sarath-2823 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·

84900-error.jpg


Mona, I have a similar issue with double quotes as shown in picture of the data source (csv file). The issue I have is that I cannot import the data from csv to SQL table without the double quotes.

0 Votes 0 ·
error.jpg (22.6 KiB)

Don't piggyback on old threads. If you have not done so already, start a new thread, describing your problem from start to end, so that we understand what you are doing. Be sure to use the corret tags. That is, if you are using SSIS, use the tag for Integration Services, else not.

0 Votes 0 ·