Bulk Import CSV file into SQL Server - remove double quotes

Question

I am running SQL 2008, bulk insert command, while inserting the data, I am trying to remove (") double quotes from the CSV file, which works partially, but doesnt work for all the records, please check my code and the screenshot of the result.

Bulk Insert tblUsersXTemp from 'C:\FR0250Members161212_030818.csv' WITH (FIELDTERMINATOR = '","', ROWTERMINATOR = '"\n"', --FormatFile ='' ERRORFILE = 'C:\bulk_insert_BadData.txt')

Are the rows that have a problem the first and last of the CSV? — SQLChao
– SQLChao, Commented Dec 14, 2016 at 17:50

SQLChao · Accepted Answer · 2016-12-14 17:53:42Z

6

After you do the bulk insert, you could replace the double quotes.

UPDATE tblUsersXTemp SET usxMembershipID = REPLACE(usxMembershipID, CHAR(34), '')

answered Dec 14, 2016 at 17:53

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam P Over a year ago

What does CHAR(34) signify here?

SQLChao Over a year ago

@psam the quotes

Shaulinator · Accepted Answer · 2016-12-14 17:52:48Z

You need a format file I believe, that's what I think is going on.

If you use the following Bulk Insert command to import the data without using a format file, then you will land up with a quotation mark prefix to the first column value and a quotation mark suffix for the last column values and a quotation mark prefix for the first column values.

Reference

Example from reference:

BULK INSERT tblPeople FROM ‘bcp.txt’ WITH ( DATAFILETYPE=‘char’, FIELDTERMINATOR=‘","’, ROWTERMINATOR = ‘\n’, FORMATFILE = ‘bcp.fmt’);

You could also potentially have dirty data that uses quotes for more than just delimiters.

Collectives™ on Stack Overflow

Bulk Import CSV file into SQL Server - remove double quotes

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related