tsql find csv file in folder

1    --BULK INSERT MULTIPLE FILES From a Folder 
2
3    --a table to loop thru filenames drop table ALLFILENAMES
4    CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
5
6    --some variables
7    declare @filename varchar(255),
8            @path     varchar(255),
9            @sql      varchar(8000),
10            @cmd      varchar(1000)
11
12
13    --get the list of files to process:
14    SET @path = 'C:\Dump\'
15    SET @cmd = 'dir ' + @path + '*.csv /b'
16    INSERT INTO  ALLFILENAMES(WHICHFILE)
17    EXEC Master..xp_cmdShell @cmd
18    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
19
20
21    --cursor loop
22    declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
23    open c1
24    fetch next from c1 into @path,@filename
25    While @@fetch_status <> -1
26      begin
27      --bulk insert won't take a variable name, so make a sql and execute it instead:
28       set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '
29           + '     WITH ( 
30                   FIELDTERMINATOR = '','', 
31                   ROWTERMINATOR = ''\n'', 
32                   FIRSTROW = 2 
33                ) '
34    print @sql
35    exec (@sql)
36
37      fetch next from c1 into @path,@filename
38      end
39    close c1
40    deallocate c1
41
42
43    --Extras
44
45    --delete from ALLFILENAMES where WHICHFILE is NULL
46    --select * from ALLFILENAMES
47    --drop table ALLFILENAMES
48