Import Multiple CSV Files to SQL Server from a Fol

Question

I have a folder called "Dump." This folder consists of various .CSV Files. The folder Location is 'C:\Dump'

I want to Import the contents of these files into SQL Server. I want the rough code along with proper comments so that I understand it.

I have tried a few codes that I found on the Net. But they haven't quite worked out for me for some strange reason.

---

The steps I would like to have are

Step 1: Copy all the File Names in the folder to a Table

Step 2: Iterate through the table and copy the data from the files using Bulk Insert.

---

Someone do please help me out on this one. Thanks a lot in advance :)

Answer 1

    --BULK INSERT MULTIPLE FILES From a Folder 

    --a table to loop thru filenames drop table ALLFILENAMES
    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:\Dump\'
    SET @cmd = 'dir ' + @path + '*.csv /b'
    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 like '%.csv%'
    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 Temp FROM ''' + @path + @filename + ''' '
           + '     WITH ( 
                   FIELDTERMINATOR = '','', 
                   ROWTERMINATOR = ''\n'', 
                   FIRSTROW = 2 
                ) '
    print @sql
    exec (@sql)

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


    --Extras

    --delete from ALLFILENAMES where WHICHFILE is NULL
    --select * from ALLFILENAMES
    --drop table ALLFILENAMES

Answer 2

In python you can use d6tstack which makes this simple

import d6tstack
import glob

c = d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv'))
c.to_mssql_combine('mssql+pymssql://usr:pwd@localhost/db', 'tablename')

See SQL examples. It also deals with data schema changes, creates table and allows you to preprocess data. It leverages BULK INSERT so should be just as fast.

Answer 3

To solve step 1, xp_dirtree can also be used to list all files and folders.

Keep in mind that it is an undocumented function. Security precautions must be considered. Intentionally crafted filenames could be an intrusion vector.

Answer 4

For Step 1 Maybe you can look at:

http://www.sql-server-performance.com/forum/threads/copying-filenames-to-sql-table.11546/

or

How to list files inside a folder with SQL Server

and then Step 2

How to cast variables in T-SQL for bulk insert?

HTH