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')
Vikas J
  • 795
  • 3
  • 14
  • 31
  • 3
    The field terminator in your data is `,` not `","`. `"` is the text qualifier. It's only supported in [SQL Server 2017](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017#input-file-format-options) and later – Panagiotis Kanavos Jan 03 '19 at 13:26
  • 1
    Possible duplicate of [Bulk insert with text qualifier in SQL Server](https://stackoverflow.com/questions/25726385/bulk-insert-with-text-qualifier-in-sql-server) – Panagiotis Kanavos Jan 03 '19 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 – Panagiotis Kanavos Jan 03 '19 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 – Vikas J Jan 03 '19 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. – Panagiotis Kanavos Jan 03 '19 at 14:59
  • Yes that's how the data is in some of the columns which I get contains data which goes in next line so if we open the input file which I provided in notepad++ you will see it in the same line but if opened in Excel and double-click on that cell value will be on next line this is not an issue in excel because of text enclosure (" ") – Vikas J Jan 03 '19 at 15:04
  • If you control how the data is exported, or can contact whoever exports the data, you can change the field and row terminators to something unusual, eg `§` for row terminator and `¦` or `¤` as the field terminator without any text qualifiers. – Panagiotis Kanavos Jan 03 '19 at 15:04
  • Another option is to cheat - SSMS is a separate downloadable product whose latest versions [support CSV files](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard?view=sql-server-2017). If this is a one-off job, or something an administrator is going to do, you can import the file from SSMS – Panagiotis Kanavos Jan 03 '19 at 15:09
  • If this isn't a one off job, then SSIS does support quoted strings. You could consider using that. – Thom A Jan 03 '19 at 15:19
  • Tried changing the field terminator to ^ but still same error. One more thing is it's not failing just at that 3rd line but even the first row is failing to insert with the same error even though it has no data in Name column that goes to the next line – Vikas J Jan 03 '19 at 15:29
  • I am now able to load the first 2 rows successfully using updated format file. I have updated the same in my post. 3rd row where name columns value goes to the next line that 1 row was not inserted. Please note this is how I get value in Input CSV and that cannot be changed. "3","V J","4000.3" So Ideally expected output in the table for the above row is : Id Name Sal 3 VJ 4000.3 – Vikas J Jan 04 '19 at 10:28
  • @PanagiotisKanavos I am now using SQL server 2017 to resolve the issues of - Field separator, Text enclosure etc. But facing another issue can you please check my post **Update** section. – Vikas J Jan 16 '19 at 21:43

1 Answers1

3

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

Martin Widlund
  • 349
  • 2
  • 4