0

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

I want to Import the contents of all files recursively into SQL Server in one table.

Append additional column in the table which consists for each file.

Have tried some solutions found it here , but it didn't work.

--get the list of files to process:
    SET @path = 'C:\Data\'
    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

TWo things which aren't working as expected:

1) It doesn't work for recursive files in the directory.

2) Not able to do the bulk insert to Temp file created.

Praveen
  • 35
  • 1
  • 11
  • That's a good start but in what did it 'not work'. Please explain - error? didn't function as expected? – Nick.Mc May 07 '18 at 11:42
  • 1
    Did you find that here? https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder please refer in future. – Nick.Mc May 07 '18 at 11:44
  • _doesn't work_ and _not able_ are completely useless descriptions and it indicates you haven't made any effort to analyse it! – Nick.Mc May 07 '18 at 11:54
  • here's some hints: 1. Are there any files in the ALLFILENAMES table? 2. If you add a `PRINT @SQL` inside the cursor then run the result, what happens? – Nick.Mc May 07 '18 at 11:57
  • Yes ALLFILENAMES tables returns the file name and path. But, the second part doesn't execute. – Praveen May 07 '18 at 12:29
  • Are you saying the line `exec(@sql)` is never executed, or it is and there is an error? If it isn't executed then the next logical thing is to see if your while loop is seeded properly. i.e. does this return anything: `SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'`. If it does then you need to put some `print` statements inside your loop and see what's going on. I am just leading you through obvious debugging steps here – Nick.Mc May 07 '18 at 12:38

0 Answers0