Bulk Insert Error : Cannot bulk load. Invalid column number in the format file

SSIS and Import/Export
1

I have a CSV File with 17 columns, where one column [CustName) has various customer names and some of them have commas.
Example "ABC Company, Inc"

I want to Bulk Insert the CSV file into a table.
The command is:
BULK INSERT [dbo].vPTInsert
FROM '\Server\GROUP-Data\AfftonImports\Invoice_Detail_Export_Report 2-28-20.csv'
WITH ( FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' ,
FORMATFILE = '\Server\GROUP-Data\AfftonImports\Format.fmt')

I get the error:
Cannot bulk load. Invalid column number in the format file

The format file:
11.0
21
1 SQLCHAR 0 20 "," 1 InvNum QL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "," 2 InvType QL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "," 3 BolNum QL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 40 "," 4 PONum QL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 11 "\r\n" 5 InvDate QL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 15 "," 6 BatchNum QL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 60 "," 7 CustName QL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 10 "," 8 Company QL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 10 "," 9 Terminal QL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 20 "," 10 InvAmt QL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "\r\n" 11 GLDate QL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 20 "," 12 Header_Detail QL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "," 13 ChargeCode QL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 50 "," 14 GLAccount QL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 20 "," 15 DebitAmt QL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 20 "," 16 CreditAmt QL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 200 "," 17 Memo QL_Latin1_General_CP1_CI_AS

side note: The column headings (Row 1) of the CSV file do not exactly match the column names in the destination table.
The Format File column names are from the the Table, not the CSV file.
So for example Column 7 in the CSV File is labled 'custnumb' but the table is 'CustName'
Not sure that matters given I have FIRSTROW = 2 in the Bulk Insert command.

When I open the CSV file in Textpad 8.0 the data looks like this:
invnum,invtype,bolnum,ponum,invdate,batchnumb,custnumb,company,terminal,invamt,gldate,header_detail,chargecode,glaccount,debitamt,creditamt,memo

918182,Invoice,SGNVH5096400,CHI1874176,2/28/2020,AFT022820,ONE-LINE,1,2201,377,2/4/2020,Header,,1028,377,,TMP# 580327-CONTAINER # TRLU9441196-CHASSIS # FLXZ423713-REF # CHI1874176

918168,Invoice,6226845980,313194183,2/28/2020,AFT022820,"FREIGHT SERVICES, LTD 01",1,2201,314.6,2/21/2020,Header,,1028,314.6,,TMP# 583616-CONTAINER # CSLU6066287-CHASSIS # APMZ400009-REF # 313194183

I notice that the only column enclosed in quotes is the [CustName] (column 7) and then ONLY when the CustName has a Comma in the name.

So I am not sure what I should have for the Delimiters in the Format file, especially for the CustName column given that only some rows have the Double-Quotes.

2

"I have a CSV File with 17 columns" but The format file has 21 columns but you are showing only 17 of the format file's columns. Edit the format file to say 17 instead of 21

The error message clearly states: Invalid column number in the format file

3

Thanks. I fixed that, but I get the same error plus a new one:
I don't know what "Invalid collation name for source column 1 in the format file " means.

BULK INSERT [dbo].vPTInsert FROM '\Server\GROUP-Data\AfftonImports\Invoice_Detail_Export_Report 2-28-20.csv' WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\Server\GROUP-Data\AfftonImports\Format.fmt'
)
Msg 4839, Level 16, State 1, Line 1
Cannot perform the bulk load. Invalid collation name for source column 1 in the format file "\LTCAD01\GROUP-Data\AfftonImports\Format.fmt".
Msg 4822, Level 16, State 1, Line 1
Cannot bulk load. Invalid number of columns in the format file "\LTCAD01\GROUP-Data\AfftonImports\Format.fmt".

4

open the csv file with norepad++ . Under encoding what do you see?

image
image829×393 23.8 KB

Also please post the collation for your

  • server
  • database
  • table
  • Columns of table
5

UTF-8 on the CSV File
SQL Server collation is SQL_Latin1_General_CP1_CI_AS
The Database is also SQL_Latin1_General_CP1_CI_AS
The Table is SQL_Latin1_General_CP1_CI_AS
all of the columns are SQL_Latin1_General_CP1_CI_AS

6

did you create this fmt file using bcp cli? or did you somehow manipulate it manually?

7

I created it manually, since it is a small number of columns (17); but it seems to match all of the documentation I have found for the file.

8

I highly discourage you creating fmt file manually. And that is one of the problems. Notice that you are using "\r\n"
as column delimiter. That is problematic.

I would encourage you to use bcp to create the fmt file.

1 SQLCHAR 0 20 "," 1 InvNum QL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "," 2 InvType QL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "," 3 BolNum QL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 40 "," 4 PONum QL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 11 "\r\n" 5 InvDate QL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 15 "," 6 BatchNum QL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 60 "," 7 CustName QL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 10 "," 8 Company QL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 10 "," 9 Terminal QL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 20 "," 10 InvAmt QL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "\r\n" 11 GLDate QL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 20 "," 12 Header_Detail QL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "," 13 ChargeCode QL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 50 "," 14 GLAccount QL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 20 "," 15 DebitAmt QL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 20 "," 16 CreditAmt QL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 200 "," 17 Memo QL_Latin1_General_CP1_CI_AS
9

Can you provide a sample file?

10

I fixed up the format file and bulk insert works. He has the wrong delimiters in there and also a non existing collation name of QL_Latin1_General_CP1_CI_AS

invnum,invtype,bolnum,ponum,invdate,batchnumb,custnumb,company,terminal,invamt,gldate,header_detail,chargecode,glaccount,debitamt,creditamt,memo
918182,Invoice,SGNVH5096400,CHI1874176,2/28/2020,AFT022820,ONE-LINE,1,2201,377,2/4/2020,Header,,1028,377,,TMP# 580327-CONTAINER # TRLU9441196-CHASSIS # FLXZ423713-REF # CHI1874176
918168,Invoice,6226845980,313194183,2/28/2020,AFT022820,"FREIGHT SERVICES, LTD 01",1,2201,314.6,2/21/2020,Header,,1028,314.6,,TMP# 583616-CONTAINER # CSLU6066287-CHASSIS # APMZ400009-REF # 313194183


if OBJECT_ID('_Customer') IS NOT NULL
	drop table _Customer
	go

CREATE TABLE [dbo]._Customer(
	[InvNum] [varchar](max) NULL,
	[InvType] [varchar](max) NULL,
	[BolNum] [varchar](max) NULL,
	[PONum] [varchar](max) NULL,
	[InvDate] [varchar](max) NULL,
	[BatchNum] [varchar](max) NULL,
	[CustName] [varchar](max) NULL,
	[Company] [varchar](max) NULL,
	[Terminal] [varchar](max) NULL,
	[InvAmt] [varchar](max) NULL,
	[GLDate] [varchar](max) NULL,
	[Header_Detail] [varchar](max) NULL,
	[ChargeCode] [varchar](max) NULL,
	[GLAccount] [varchar](max) NULL,
	[DebitAmt] [varchar](max) NULL,
	[CreditAmt] [varchar](max) NULL,
	[Memo] [varchar](max) NULL
) ON [PRIMARY] 


BULK INSERT [dbo]._Customer
FROM 'C:\_personal\sqlteam\Customer.csv'
WITH ( FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' ,
FORMATFILE = 'C:\_personal\sqlteam\Customer.fmt')

select * From _Customer
11

Thanks for your help.
Solved my issue.
This Blog post has a Stored Proc that reads the CSV row into a table as a whole and parses it out, thus dealing with a few possible issues the Bulk Insert does not handle well.
It is from Brad Shulz
bradsruminations-DOT-blogspot-DOT-com/2011/01/so-you-want-to-read-csv-files-huh.html

Chuck