BULK INSERT from CSV, strings with ""

Question

I try to insert data from a .csv to a MS SQL databse. I do it like this:

CREATE TABLE [dbo].[prescreen_candidateData](
[id] [varchar](50) DEFAULT '',
[email] [varchar](50) DEFAULT '',
[firstname] [varchar](50) DEFAULT '',
[lastname] [varchar](50) DEFAULT '',
[city] [varchar](50) DEFAULT '',
[address] [varchar](50) DEFAULT '',
[phone] [varchar](50) DEFAULT '',
[birthday] [varchar](50) DEFAULT '',
[candidateurl] [varchar](50) DEFAULT '',
[createdAt] [varchar](50) DEFAULT '') ON [PRIMARY]

After creation the table, I try to insert like:

BULK INSERT [dbo].[prescreen_candidateData] FROM '\\server2\prescreen$\candidateData.csv' WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\,',
ROWTERMINATOR = '\n',
KEEPNULLS
);

My .csv is build on like this:

id,email,firstname,lastname,city,address,phone,birthday,candidateurl,createdAt

for Example like:

58282,[email protected],Michael,Meier,,"""Street Nr 12,08159,City""",012345678910,"1996-08-29 00:00:00",https://linktoemployee, 2016-05-12

In my example, city is empty, in my csv it is shown like ,, that is okay, but my problem the adressfield, because adressfield contains , but the , is the terminator... how can I do it, that in the bulk insert the , whicht is between "" will be ignored?

Edit: **I solved it by creating a linked server to the csv... worked as I want **

I don't have an answer for your issue, but I would like to say that ROWTERMINATOR = '\n' is ignored by sql server. Instead ROWTERMINATOR = '\r\n' will be used. Most of the time this will work, but not always. In order to use line feed as rowterminator use ROWTERMINATOR = '0x0a'. — HoneyBadger
– HoneyBadger, Commented Sep 21, 2017 at 12:06
Try ; for fieldterminator. As far as I know, excel uses ; as fieldterminator by default. — Karolina Ochlik
– Karolina Ochlik, Commented Sep 21, 2017 at 12:18
I get this csv from external service on my ftp... I try to talk to they... but, I think comma is default terminator on sql... — Sam Enbank
– Sam Enbank, Commented Sep 21, 2017 at 12:24

Riv · Accepted Answer · 2017-09-21 15:15:25Z

You should use a format file to describe the data that you are uploading. Amongst other things, a format file will allow you to specify the delimiter for individual columns in your data. You will need to reference the format file in your query:

BULK INSERT [dbo].[prescreen_candidateData] FROM '\\server2\prescreen$\candidateData.csv' 
WITH (
FORMATFILE='\\server2\prescreen$\YourFormat.fmt'
);

For your scenario, create a format file like this(note that it needs a new line at the end of the file or it will error):

9.0
10
1       SQLCHAR       0       50       ","      1     id         ""
2       SQLCHAR       0       50     ","      2     email         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50     ","      3     firstname         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       50     ","   4     lastname         SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50     ",\"\"\""   5     city         SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50     "\"\"\","   6     address         SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       50     ",\""   7     phone         SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       50     "\","   8     birthday         SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       50     ","   9     candidateurl         SQL_Latin1_General_CP1_CI_AS
10       SQLCHAR       0       50     "\r\n"   10     createdAt         SQL_Latin1_General_CP1_CI_AS

The image below explains what the various fields do.

You will notice that the format file allows you to specify the terminator for each of your fields. You will also notice that I specified a comma followed by 3 quotes as the delimiter for City (",\"\"\"). Similarly for the Address field I specified a delimiter of 3 quotes followed by a comma (\"\"\",). So everything within the City and Address delimiters will be uploaded together including any ','. By including the quotes in your terminator you avoid having the quotes uploaded with your data and it also allows you to be able to have comma ',' within your data upload.

More info

Collectives™ on Stack Overflow

BULK INSERT from CSV, strings with ""

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related