SQL Server 2012 Bulk Insert CSV File where a column (Cust Name) May have commas 1

[appelq]

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.

Thanks,
Chuck

 

[fredericofonseca]

Assuming that your CSV file is a valid one (it may not be even if it looks like it) you will need to change your delimiters.
but if only values that need the double quotes have them you will be in trouble and you may need to resort to powershell/c# to process the file.

in SQL 2017 and higher you can also use a CSV format option on the command - that will work well even if not all columns have the double quotes


see

https://www.sqlservercentral.com/forums/topic/bulk-insert-csv-with-quotes

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[appelq]

So 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.

 

[fredericofonseca]

that is why I mentioned whether all fields had the double quotes or not. If they had you could use the trick I posted. and yes it is a trick.

BCP, unless it is 2017 or higher, does not deal correctly with CSV files - was never made for that.

so although there are one or 2 options with SQL the split will not be possible to be done by BCP on this case.

I'll see if I can dig out a SP that did manage to parse most CSV cases and will post back - but on your particular case I'm not sure it will work as you have 2 different delimiters. From what I can see on your format file you are dealing with an input file that has 3 different record types and you are, smartly using "\r\n", making it look like it is just 1 record type

it would be a lot easier if you did this with powershell (SQL job step) as it would could probably deal with that file fine (or maybe not if what I said above is correct)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[appelq]

OK. I'll research the powershell option.
My main goal is to allow the user to drop files into a folder and have a Stored Proc import the data with minimal effort by the end user.

Thanks,
Chuck

 

[MakeItSo]

You could try a quick workaround using OpenOffice / LibreOffice. open the csv there, save as csv with "adjust filter settings" option checked. Enclose in quotes.


"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[fredericofonseca]

you can try the following proc - I know for sure that in some cases it won't work but maybe you can make it work for your needs.

It is from Brad Shulz

http://bradsruminations.blogspot.com/2011/01/so-you-want-to-read-csv-files-huh.html

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[appelq]

That Proc was perfect!
It addresses all of the issues I was having with Bulk Insert.
Thanks for your help.
Chuck