4

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
Improve this question

4 Answers 4

Reset to default
9

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.

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

5 Comments

A much more concise and relevant answer than my previous attempt.
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
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.
Will this work if the column SOMETIMES has quotes?
yes the qualifiers are all just candidate markers that the parser will look for. so if only a few of your records contained double quotes (a common scenario), this is totally fine.
8

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 )
Improve this answer

1 Comment

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.
0

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

Improve this answer

1 Comment

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

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.