January 16, 2019 at 1:55 pm
I am 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. This was working in SQL 2014 but now when I do same in SQL 2017 using Format = CSV parameter it throws an error.
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](3) 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')
January 16, 2019 at 2:20 pm
I got this to work by removing Format from the insert and the format file looks like this
BULK INSERT dbo.test
from 'C:\work\data\test\test.csv'
WITH ( FIRSTROW = 2, FormatFile = 'C:\work\data\test\test.fmt')
12.0
3
1 SQLCHAR 0 1 "\"" 0 FIRST_QUOTE ""
2 SQLCHAR 0 20 "\",\"" 1 Id ""
3 SQLCHAR 0 20 "\"\r\n" 2 Name ""
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 16, 2019 at 2:31 pm
Mike01 - Wednesday, January 16, 2019 2:20 PMI got this to work by removing Format from the insert and the format file looks like this
BULK INSERT dbo.test
from 'C:\work\data\test\test.csv'
WITH ( FIRSTROW = 2, FormatFile = 'C:\work\data\test\test.fmt')
12.0
3
1 SQLCHAR 0 1 "\"" 0 FIRST_QUOTE ""
2 SQLCHAR 0 20 "\",\"" 1 Id ""
3 SQLCHAR 0 20 "\"\r\n" 2 Name ""
Hello Mike, I was using parameter FORMAT = 'CSV' because it handles all special characters within csv data like double quotes, line break, back slash etc. That's the benefit of 2017 over 2014 while using Bulk Insert so I cannot skip that parameter. This was just a sample data so it worked without that parameter. Had the data contained any of the special characters which I mentioned it would have failed.
Example,
"Id","Description","DateModified","Quantity"
"1","hello-this,is test data"",""testing","2018-10-01",""
"2","","\","18.0"
"3","""text in double quotes""","13.2"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply