How can I loop through all the files in a folder using TSQL?

Question

We have a folder of excel files that we want to import into our database using TSQL. We have the code to import the individual file using OpenRowSet, but need to find a way to loop through the files in the folder and run this code on each file. How can this be accomplished using TSQL?

Answer 1

Did some research, and found a way to loop over the files using something like this:

CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B c:\my\folder\path\';

declare @fileName varchar(100)

While (Select Count(*) From #tmp where excelFileName is not null) > 0
Begin

    Select Top 1 @fileName = excelFileName From #tmp

    -- OPENROWSET processing goes here, using @fileName to identify which file to use

    Delete from #tmp Where excelFileName = @FileName

End

DROP TABLE #tmp

Answer 2

Adding More to What Froadie said, you likely need to first enable use of the command shell (Enable 'xp_cmdshell' SQL Server) also your cmd shell path may need to have double quotes around it, This is an example that I got to work:

--Allow for SQL to use cmd shell
EXEC sp_configure 'show advanced options', 1    -- To allow advanced options to be changed.
RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.

--Loop through all of the files
CREATE TABLE #tmp(excelFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B "C:\_GENERAL RESOURCES\CANWET\ANUSPLINE DATA CONVERTER\AnusplineStationSelector\CCDP\Files\"';

declare @fileName varchar(100)

While (Select Count(*) From #tmp where excelFileName is not null) > 0
Begin

    Select Top 1 @fileName = excelFileName From #tmp

    PRINT(@filename)
    -- OPENROWSET processing goes here, using @fileName to identify which file to use

    Delete from #tmp Where excelFileName = @FileName

End