How to import Multiple csv files from Multiple Folders into single sql server table

  • CRAZY99

    SSC-Addicted

    Points: 435

    Hi,

    I am having one main folder,and that main folder consists of 140 sub folders and each sub folder consists of multiple csv files.....

    so i need to import all these files from all 140 sub folders into single table.

    I know how to import the files fom one folder to the table,but i am not able to import the files from all the folders to the single table.

    please help me if anyone dealt with the same situation.

    Thank u.

  • Lowell

    SSC Guru

    Points: 323356

    here's an example i like to post using BULK insert. you can do this via SSIs as well, but it's a different technique.

    in my example below, i have 4 known folders, and an unknown number of files inside the folders.

    I'm assuming all files are the same structure, and get imported into the same table. If you are doing something different, this solution is not for you.

    note this also uses xp_cmdshell to get the list of files...if you have that disabled, and cannot enable it, you can't use this technique either.

    --BULK INSERT MULTIPLE FILE

    --a table to loop thru filenames drop table ALLFILENAMES

    CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

    --the source table: yours already exists, but needed for this example.

    CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))

    --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:\DB\'

    SET @cmd = 'dir ' + @path + '*.txt /b'

    INSERT INTO ALLFILENAMES(WHICHFILE)

    EXEC Master..xp_cmdShell @cmd

    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB2\'

    SET @cmd = 'dir ' + @path + '*.txt /b'

    INSERT INTO ALLFILENAMES(WHICHFILE)

    EXEC Master..xp_cmdShell @cmd

    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB3\'

    SET @cmd = 'dir ' + @path + '*.txt /b'

    INSERT INTO ALLFILENAMES(WHICHFILE)

    EXEC Master..xp_cmdShell @cmd

    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB4\'

    SET @cmd = 'dir ' + @path + '*.txt /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 '%.txt%'

    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 BULKACT FROM ''' + @path + @filename + ''' '

    + ' WITH (

    DATAFILETYPE = ''char'',

    FIELDTERMINATOR = '','',

    ROWTERMINATOR = ''\n'',

    FIRSTROW = 2

    ) '

    print @sql

    exec (@sql)

    fetch next from c1 into @path,@filename

    end

    close c1

    deallocate c1

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • CRAZY99

    SSC-Addicted

    Points: 435

    Thank you Lowell for your fast reply,

    but the thing is i need to insert the data from 140 folders,here your are inmporting the data from 4 folders,so for 140 folderss i need to do the same thing,

    is there any other procedure like keeping the whole thing in the loop .

  • Lowell

    SSC Guru

    Points: 323356

    I presented an idea and example to get you part of the solution. I thought it would get you started...you really didn't provide anything concrete as far as true requirements.

    it should be pretty obvious that you could create a cursor to find all the sub folders in the master folder, so whether there is 2 sub folders or 200, it' the same way it's getting the data as the list of files;

    something like dir *.* to get the list of folders, then iterate thru each folder for each file beneath the folder.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell

    SSC Guru

    Points: 323356

    another obvious fix would be to simply copy all the files form 140 folders to a single folder, and use the example i gave against that one folder.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply