TSQL Find csv file in folder

    --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

Source: stackoverflow.com

Similar codes

0 vote311 views1 answer
Sql menampilkan data tabel
SQL
1 vote279 views1 answer
Postgresql update auto_increment value
SQL
0 vote404 views2 answers
Https://cloud.google.com/shell/docs/using-web-preview#previewing_the_application
SQL
0 vote299 views2 answers
Drop check constraint in ms sql
SQL
4 votes298 views1 answer
Restart for pg_hba.conf file
SQL
8 votes393 views3 answers
Find table for column name in sql
SQL
4 votes299 views1 answer
Create table if not exist in sqlite
SQL
0 vote305 views1 answer
Client does not support authentication protocol requested by server sqlyog
SQL
3 votes314 views4 answers
Order by desc postgres
SQL
1 vote307 views1 answer
Moodle query first user access
SQL