从文件夹导入多个CSV文件到SQL Server

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

我有一个名为“转储”的文件夹。此文件夹包含各种.CSV文件。文件夹位置是'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.

我想将这些文件的内容导入SQL Server。我想要粗略的代码以及适当的评论，以便我理解它。

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

步骤1：将文件夹中的所有文件名复制到表中

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

第2步：遍历表并使用批量插入从文件中复制数据。

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

有人请帮我解决这个问题。非常感谢提前:)

2 个解决方案

#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

#2

For Step 1 Maybe you can look at:

对于第1步，也许你可以看一下：

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

要么

How to list files inside a folder with SQL Server

如何使用SQL Server列出文件夹中的文件

and then Step 2

然后是第2步

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

如何在T-SQL中为变量插入转换变量？

HTH

#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

#2