Bulk Insert Multiple Semicolon Delimited Files into SQL Server

Question

I have 400 csv files with semicolon as its delimiter.

Example: the column headers (all in one field in Excel):

CUST_NAME;Transdate;Transnum;UNIT_PRICE;QTY;STYLE_NUMBER;country

Sample data in Excel file (all in one column):

Bar Clayn;2012-01-01 00:00:00.0;45724;-109.98;-1;803702;CA
EAL AN;2012-01-01 00:00:00.0;48772;-69.98;-1;837642;CA

I have referenced this post for importing multiple csv files into SQL:

Stackflow

As it is a semicolon delimited file, I have created a format file for bulk insert.

The file looks like this:

    10.0 
    7
    1   SQLCHAR     2   500 ";" 1   CUST_NAME     Latin1_General_CI_AS
    2   SQLDATETIME 1   8   ";" 11  Transdate     ""
    3   SQLCHAR     2   500 ";" 12  Transnum      Latin1_General_CI_AS
    4   SQLDECIMAL  1   19  ";" 19  UNIT_PRICE    ""
    5   SQLINT      1   4   ";" 20  QTY           ""
    6   SQLBIGINT   1   8   ";" 22  STYLE_NUMBER  ""
    7   SQLCHAR     2   500 ";" 28  country       Latin1_General_CI_AS

The code to loop through all the files in one folder works, but I think there is something wrong with my fmt file as it gives me errors like:

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1.
Verify that the field terminator and row terminator are specified correctly.

The code is :

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--some variables
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)

--get the list of files to process:
SET @path = 'C:\Sales\2012\' 
SET @cmd = 'dir ' + @path + '/b/a-d'
INSERT INTO  ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE is not null
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT Sales2012 FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
                    FORMATFILE =''C:\Sales2012.fmt''      
                    )'

print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1

It works perfectly with SQL Server Import Wizard:

Test Qualifier: "
Header row Delimiter: {CR}{LF}

I could do this using import wizard, but considering 400+ csv files, it would take me days to load those. Did I do something wrong?

Any help is appreciated.

Try changing the last fieldterminator in the format file to `"\r\n"`. — Aaron Bertrand, Aug 13 '13 at 03:59
@AaronBertrand i got the same error.. could it be all the data is in one column in excel file that is delimited by semicolon? — Kric, Aug 13 '13 at 04:06
Are you trying to bulk insert an .xls/.xlsx file? Or is it actually a CSV file? You keep talking about how it looks in Excel; how does it look if you open it in Notepad? — Aaron Bertrand, Aug 13 '13 at 04:39

score 0 · Answer 1 · answered Aug 13 '13 at 09:45

0

This is most probably the case of having comma character somewhere in the data that is not parsed properly.

Try opening your CSV file in notepad and see how it’s internal structure looks like and what characters are used to escape comma and quote chars.

Another thing you can do is modify your cursor to print INSERT statements VS making the actual inserts. This will allow you to track which row specifically is causing issues and to fix that.

answered Aug 13 '13 at 09:45

Terence Eccleston

649
5
3

There is no comma, no quote chars in that file. :/ – Kric Aug 13 '13 at 15:40
Please reference this post [StackFlow](http://stackoverflow.com/questions/7403560/bulk-insert-rowterminator-issue) if there is no rowterminator/. Thanks Terence. – Kric Aug 13 '13 at 16:39

Bulk Insert Multiple Semicolon Delimited Files into SQL Server

1 Answers1