Bulk insert multiple .csv files into a table for each file

Question

I need a stored procedure that will do a bulk insert for multiple .csv files into tables in SQL Server. The .csv files all sit in a folder. The .csv files are tab delimited. I am able to iterate through the folder and create a record in a table named FileNames with each file in it. I get an error when it gets to the BULK INSERT code.

It's an incorrect syntax error near '-'. BULK INSERT FILE01-K FROM C:\Temp\CSV_FILES\FILE01-K.csv')

My procedure:

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

--get the list of files to process
SET @path = 'C:\Temp\CSV_FILES\'
SET @cmd = 'dir ' + @path + '*.csv /b'
--clear the FileNames table
DELETE FROM FileNames

INSERT INTO  FileName(FileName)

EXEC Master..xp_cmdShell @cmd

UPDATE FileName SET FilePath = @path where FilePath is null

DELETE FROM FileNames WHERE FileName is  null

--cursor loop
declare c1 cursor for SELECT FilePath,FileName FROM FileNames where FileName like '%.csv%' order by FileName desc
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
   set @sql = 'BULK INSERT '+ Replace(@filename, '.csv','')+' FROM '+@Path+@filename+''')' 
   print @sql
   exec (@sql)

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

I've searched and found some examples, including up here that I've used to try and build mine.

What tables are you inserting into? I don;t see them. Presumably you don't have a table called `FILE01-K`, and if you did you need to escape it like this `[FILE01-K]` — Charlieface, Sep 30 '21 at 19:08
I'm inserting each file name and path into a table named FileNames. From there I want to create a table for each file. — trav1514, Sep 30 '21 at 19:37
BULK INSERT doesn't create tables. You can use `SELECT ... INTO FROM OPENROWSET(BULK,...` if you want to create the tables dynamically. — David Browne - Microsoft, Sep 30 '21 at 19:51
The example I am working from uses openrowset. https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder — trav1514, Sep 30 '21 at 19:58
The one I used as reference uses ms access. What replaces that since I'm using SQL Server?set @sql = 'select * into '+ Replace(@filename, '.csv','')+' from openrowset(''MSDASQL'' ,''Driver={Microsoft Access Text Driver (*.txt, *.csv)}'' ,''select * from '+@Path+@filename+''')' — trav1514, Sep 30 '21 at 20:00

Bulk insert multiple .csv files into a table for each file

0 Answers0