Bulk Insert Multiple Semicolon Delimited Files into SQL Server -

i have 400 csv files semicolon delimiter.

example: column headers (all in 1 field in excel):

cust_name;transdate;transnum;unit_price;qty;style_number;country 

sample data in excel file (all in 1 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 post importing multiple csv files sql:

stackflow

as semicolon delimited file, have created format file bulk insert.

the file looks 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 loop through files in 1 folder works, think there wrong fmt file gives me errors like:

msg 4866, level 16, state 7, line 1
bulk load failed. column long in data file row 1, column 1.
verify field terminator , row terminator specified correctly.

the code :

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 list of files process: set @path = 'c:\sales\2012\'  set @cmd = 'dir ' + @path + '/b/a-d' insert  allfilenames(whichfile) exec master..xp_cmdshell @cmd update allfilenames set whichpath = @path whichpath null  --cursor loop declare c1 cursor select whichpath,whichfile allfilenames whichfile not null open c1 fetch next c1 @path,@filename while @@fetch_status <> -1   begin   --bulk insert won't take variable name, make sql , execute instead:    set @sql = 'bulk insert sales2012 ''' + @path + @filename + ''' '        + '     (                      formatfile =''c:\sales2012.fmt''                           )'  print @sql exec (@sql)    fetch next c1 @path,@filename   end close c1 deallocate c1 

it works sql server import wizard:

test qualifier: " header row delimiter: {cr}{lf} 

i using import wizard, considering 400+ csv files, take me days load those. did wrong?

any appreciated.

this case of having comma character somewhere in data not parsed properly.

try opening csv file in notepad , see how it’s internal structure looks , characters used escape comma , quote chars.

another thing can modify cursor print insert statements vs making actual inserts. allow track row causing issues , fix that.