Get List Of Files From a Windows Directory to SQL Server
Posted by Prashant on June 8, 2010
In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.
So in order to use xp_cmdshell, we need to enable it as it is disabled by default.
Here is the way to enable:
--allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO --Update the currently configured value for advanced options. RECONFIGURE GO --Enable XP_CMDSHELL EXEC sp_configure 'xp_cmdshell', 1 GO --Update the currently configured value for this feature. RECONFIGURE GO
Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:
--Create the table to store file list CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256)) --Insert file list from directory to SQL Server DECLARE @Path varchar(256) = 'dir C:\Import\' DECLARE @Command varchar(1024) = @Path + ' /A-D /B' INSERT INTO myFileList EXEC MASTER.dbo.xp_cmdshell @Command --Check the list SELECT * FROM myFileList GO --Clean up DROP TABLE myFileList GO
Here is the result in the table.
This can be extendable to other file operations too. Leave your valuable comments and suggestions.