title: Bulk Insert Multiple Semicolon Delimited Files into SQL Server
body: <p>I have 400 csv files with semicolon as its delimiter.</p>
  
  <p>Example: the column headers (all in one field in Excel):</p>
  
  <pre><code>CUST_NAME;Transdate;Transnum;UNIT_PRICE;QTY;STYLE_NUMBER;country
  </code></pre>
  
  <p>Sample data in Excel file (all in one column):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have referenced this post for importing multiple csv files into SQL:</p>
  
  <p><a href="https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder">Stackflow</a></p>
  
  <p>As it is a semicolon delimited file, I have created a format file for bulk insert.</p>
  
  <p>The file looks like this:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p>Msg 4866, Level 16, State 7, Line 1<br>
    The bulk load failed. The column is too long in the data file for row 1, column 1.<br>
    Verify that the field terminator and row terminator are specified correctly.</p>
  </blockquote>
  
  <p>The code is :</p>
  
  <pre><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 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 &lt;&gt; -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
  </code></pre>
  
  <p>It works perfectly with SQL Server Import Wizard: </p>
  
  <pre><code>Test Qualifier: "
  Header row Delimiter: {CR}{LF}
  </code></pre>
  
  <p>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?</p>
  
  <p>Any help is appreciated.</p>