CODEWITHSUNDEEP
sql-serverbulkinsert
Vikas J
Vikas J

Reputation: 887

How to Bulk Insert data from csv which contains double quotes to sql server 2014

Input CSV file:

"Id","Name","Sal"
"1","vikas,j","10000.5"
"2","James","5000.2"
"3","V
J","4000.3"

table:

create table dbo.demo
(
 Id char(1),
 Name varchar(50),
 Sal float
)

bcp.fmt file:

12.0
4
1 SQLCHAR 0 1 "" 0 FIRST_QUOTE "" 
2 SQLCHAR 0 9999 "\",\"" 1 Id ""
3 SQLCHAR 0 9999 "\",\"" 2 Name ""
4 SQLCHAR 0 9999 "\"\r\n" 3 Sal ""

Bulk Insert command:

BULK INSERT dbo.demo from 'D:\Input.csv' with(DATAFILETYPE='char',fieldterminator= '","' ,Firstrow = 2, Rows_per_batch = 100000 , rowterminator='\n',FORMATFILE = 'D:\bcp.fmt')

Error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (Sal)

Can anyone tell me what am I missing here, from what I understand I am doing something wrong in format file while passing Terminator value?

UPDATE

I am now using SQL Server 2017 Developer edition. I have a table of 5 columns out of which 1st one is identity and 4th is having default constraint and the 5th one is computed column. So in CSV, I am not providing values for these three columns. I have created a format file to skip the first column.

Cannot bulk load CSV file. Invalid field parameters are specified for source column number 1 in the format file "C:\MyData\Archives\Demo.fmt". All data fields must be either character or Unicode character with terminator when CSV format is specified. Cannot bulk load. Invalid number of columns in the format file "C:\MyData\Archives\Demo.fmt".

Am I missing anything here? Any help would be really appreciated.

CREATE table dbo.test
(
[UniqueID] [bigint] IDENTITY(1,1) NOT NULL,
[Id] char NOT NULL,
[Name] [varchar] (50) NOT NULL,
[IsDelete] [tinyint] NULL DEFAULT 0,
[HashValue] AS (checksum('MD5',[Id],[Name])) PERSISTED
);



CSV
"UniqueId","Id","Name","IsDelete"
"A101","John"
"B102","Scott"


Demo.fmt
14.0
3
1 SQLCHAR 0 1 "" 0 FIRST_QUOTE ""
2 SQLCHAR 0 9999 ","" 2 Id ""
3 SQLCHAR 0 9999 ""\r\n" 3 Name ""

BULK INSERT dbo.test from 'C:\MyData\CONTENT\Input.csv'
WITH ( FORMAT = 'CSV', FIRSTROW = 2, FormatFile = 'C:\MyData\Archives\Demo.fmt')

Upvotes: 2

Views: 9967

Answers (1)

Martin Widlund
Martin Widlund

Reputation: 399

What worked for me was adding ROWTERMINATOR = '0x0a' in the WITH-statement.

Upvotes: 5

Related Questions