Bulk insert with text qualifier in SQL Server

Question

I am trying to bulk insert few records in a table test from a CSV file ,

 CREATE TABLE Level2_import 
 (wkt varchar(max),
 area VARCHAR(40),
 ) 


BULK
 INSERT level2_import 
 FROM 'D:\test.csv'
 WITH
 (
 FIRSTROW = 2,
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n'
 )

The bulk insert code should rid of the first row and insert the data into the table . it gets rid of first row alright but gets confused in the delimiter section . The first column is wkt and the column value is double quoted and has comma within the value .

So I guess I question is if there is a way to tell the BULK INSERT that the double quoted part is one column regardless of the comma within it ?

the CSV file looks like this ,

 "MULTIPOLYGON (((60851.286135090661 510590.66974495345,60696.086128673756 510580.56976811233,60614.7860844061 510579.36978015327,60551.486015895614)))", 123123.22

Adulterine

Answer 1

You need to use a 'format file' to implement a text qualifier for bulk insert. Essentially, you will need to teach the bulk insert that there's potentially different delimiters in each field.

Create a text file called "level_2.fmt" and save it.

11.0
2
1   SQLCHAR   0  8000   "\","      1     wkt         SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0  40   "\r\n"      2     area         SQL_Latin1_General_CP1_CI_AS

The first line, "11.0" refers to your version of SQL. The second line shows that your table, [level2_import], has two columns. Each line after that will describe a column, and obeys the following format:

[Source Column Number][DataType][Min Size][Max Size][Delimiter pattern][Destination Column Number][Destination Column Name][Case sensitivity of database]

Once you've created that file, you can read in your data with the following bulk insert statement:

BULK INSERT level2_import
FROM 'D:\test.csv'
WITH 
(
  FIRSTROW = 2,
  FORMATFILE='D:\level_2.fmt'
);

Refer to this blog for a detailed explanation of the format file.

Phenice

A much more concise and relevant answer than my previous attempt. – Phenice

Would you know why I am getting this error ? Cannot bulk load. The file "C:\mig\level_2.fmt" does not exist. I have created the file with the content as suggested but no luck – Adulterine

My guess is that you've got the file somewhere that SQL Server can't access. I have gotten this when the file is on my local machine and I've forgotten for a moment that SQL Server is running remotely and maps to different drives. Try putting the file in the same folder as your csv file, so you can at least test the functionality. If that's working, you can move the file back to where you want and just try to make sure that it's a valid location from SQL Server's point of view. Hope it helps. – Phenice

Will this work if the column SOMETIMES has quotes? – Tweak

Answer 2

SQL Server 2017 finally added support for text qualifiers and the CSV format defined in RFC 4180. It should be enough to write :

BULK INSERT level2_import 
FROM 'D:\test.csv'
WITH ( FORMAT = 'CSV', ROWTERMINATOR = '\n', FIRSTROW = 2 )

Gemini

The docs say that Azure SQL does support CSV : Beginning with SQL Server 2017 (14.x) CTP 1.1, BULK INSERT supports the CSV format, as does Azure SQL Database.. Your comment describes one of the other solutions, necessary for older versions: 0x0a is the hex for \n and char is used for non-Unicode files. – Gemini

Answer 3

Try removing .fmt to the file and use .txt instead, that worked for me

Robb

Could you please elaborate more your answer adding a little more description about the solution you provide? – Antepast

Answer 4

I have this issue working with LDAP data the dn contains commas, as do other fields that contain dns. Try changing your field terminator to another, unused character, like a pipe | or Semicolon ;. Do this in the data and the file definition.

so the code should be:

 CREATE TABLE Level2_import 
 (wkt varchar(max),
 area VARCHAR(40),
 ) 


BULK
 INSERT level2_import 
 FROM 'D:\test.csv'
 WITH
 (
 FIRSTROW = 2,
 FIELDTERMINATOR = ';',
 ROWTERMINATOR = '\n'
 )

and your CSV:

"MULTIPOLYGON (((60851.286135090661 510590.66974495345,60696.086128673756 510580.56976811233,60614.7860844061 510579.36978015327,60551.486015895614)))"; 123123.22

Lazarus