2

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')
Improve this question
12
  • 3
    The field terminator in your data is , not ",". " is the text qualifier. It's only supported in SQL Server 2017 and later Commented Jan 3, 2019 at 13:26
  • 1
    Possible duplicate of Bulk insert with text qualifier in SQL Server Commented Jan 3, 2019 at 13:38
  • In previous versions you'd have to use a format file, not the fieldterminator parameter. If you target 2017, use FORMAT='CSV' without a format file. For earlier versions, use the format file and remove both the fieldterminator and rowterminator parameters Commented Jan 3, 2019 at 13:42
  • Thanks, @PanagiotisKanavos I tried to modify fmt file based on the link which you shared but I am still getting same Error. Is it something to do with float datatype? 12.0 3 1 SQLCHAR 0 3 "\"," 1 Id SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 55 "\"," 2 Name SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 15 "\r\n" 3 Sal SQL_Latin1_General_CP1_CI_AS Commented Jan 3, 2019 at 14:58
  • The 3rd line contains a newline inside the Nam field. That's why text qualifiers are needed. Only SQL Server 2017 supports this. Earlier versions will see the newline and treat it as a row terminator. They'll see the last line as a separate record. Commented Jan 3, 2019 at 14:59

1 Answer 1

Reset to default
5

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

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

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.