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:
(Image-1)
--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.
SQL Journey 
shankar said
Gr8. How can i retrieve date modified, type, size columns as well
jfbevilaqua said
Try this instead. No xp_cmdshell calls needed:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Dir](@Wildcard VARCHAR(8000))
/* returns a table representing all the items in a folder. It takes as parameter the path to the folder. It does not take wildcards in the same way as a DIR command. Instead, you would be expected to filter the results of the function using SQL commands
Notice that the size of the item (e.g. file) is not returned by this function.
This function uses the Windows Shell COM object via OLE automation. It opens a folder and iterates though the items listing their relevant properties. You can use the SHELL object to do all manner of things such as printing, copying, and moving filesystem objects, accessing the registry and so on. Powerful medicine.
–e.g.
–list all subdirectories directories beginning with M from “c:\program files”
SELECT [path] FROM dbo.dir(‘c:\program files’)
WHERE name LIKE ‘m%’ AND IsFolder =1
SELECT * FROM dbo.dir(‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG’)
*/
RETURNS @MyDir TABLE
(
— columns returned by the function
[name] VARCHAR(2000), –the name of the filesystem object
[path] VARCHAR(2000), –Contains the item’s full path and name.
[ModifyDate] DATETIME, –the time it was last modified
[IsFileSystem] INT, –1 if it is part of the file system
[IsFolder] INT, –1 if it is a folsdder otherwise 0
[error] VARCHAR(2000) –if an error occured, gives the error otherwise null
)
AS
— body of the function
BEGIN
DECLARE
–all the objects used
@objShellApplication INT,
@objFolder INT,
@objItem INT,
@objErrorObject INT,
@objFolderItems INT,
–potential error message shows where error occurred.
@strErrorMessage VARCHAR(1000),
–command sent to OLE automation
@Command VARCHAR(1000),
@hr INT, –OLE result (0 if OK)
@count INT,@ii INT,
@name VARCHAR(2000),–the name of the current item
@path VARCHAR(2000),–the path of the current item
@ModifyDate DATETIME,–the date the current item last modified
@IsFileSystem INT, –1 if the current item is part of the file system
@IsFolder INT –1 if the current item is a file
IF LEN(COALESCE(@Wildcard,”))<2
RETURN
SELECT @strErrorMessage = 'opening the Shell Application Object'
EXECUTE @hr = sp_OACreate 'Shell.Application',
@objShellApplication OUT
–now we get the folder.
IF @HR = 0
SELECT @objErrorObject = @objShellApplication,
@strErrorMessage = 'Getting Folder"' + @wildcard + '"',
@command = 'NameSpace("'+@wildcard+'")'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objShellApplication, @command,
@objFolder OUT
–PRINT 'objFolder: ' + @objFolder
IF @objFolder IS NULL RETURN –nothing there. Sod the error message
–and then the number of objects in the folder
SELECT @objErrorObject = @objFolder,
@strErrorMessage = 'Getting count of Folder items in "' + @wildcard + '"',
@command = 'Items.Count'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objfolder, @command,
@count OUT
IF @HR = 0 –now get the FolderItems collection
SELECT @objErrorObject = @objFolder,
@strErrorMessage = ' getting folderitems',
@command='items()'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objFolder,
@command, @objFolderItems OUTPUT
SELECT @ii = 0
WHILE @hr = 0 AND @ii< @count –iterate through the FolderItems collection
BEGIN
IF @HR = 0
SELECT @objErrorObject = @objFolderItems,
@strErrorMessage = ' getting folder item '
+ CAST(@ii AS VARCHAR(5)),
@command='item(' + CAST(@ii AS VARCHAR(5))+')'
–@Command='GetDetailsOf('+ cast(@ii as varchar(5))+',1)'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objFolderItems,
@command, @objItem OUTPUT
IF @HR = 0
SELECT @objErrorObject = @objItem,
@strErrorMessage = ' getting folder item properties'
+ CAST(@ii AS VARCHAR(5))
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'path', @path OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'name', @name OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'ModifyDate', @ModifyDate OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'IsFileSystem', @IsFileSystem OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'IsFolder', @IsFolder OUTPUT
–and insert the properties into a table
INSERT INTO @MyDir ([NAME], [path], ModifyDate, IsFileSystem, IsFolder)
SELECT @NAME, @path, @ModifyDate, @IsFileSystem, @IsFolder
IF @HR = 0 EXECUTE sp_OADestroy @objItem
SELECT @ii=@ii+1
END
IF @hr 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @strErrorMessage = ‘Error whilst ‘
+ COALESCE(@strErrorMessage, ‘doing something’) + ‘, ‘
+ COALESCE(@Description, ”)
INSERT INTO @MyDir(error) SELECT LEFT(@strErrorMessage,2000)
END
EXECUTE sp_OADestroy @objFolder
EXECUTE sp_OADestroy @objShellApplication
RETURN
END
Peter Pirker said
This works very well.
Alas – Unfortunately not on shares or mapped network drives.
I imagine it has the same issues as xp_cmdshell.
Do you have any ideas for a workaround?
רוני ורד אדר said
Clean and simple.
Thanks very much for the tip.
Regards,
Roni
Store file names and modified dates in a table without a Foreach Loop Task – SQL Server – SSIS | hernandezpaul said
[…] In this link an example of the command usage to retrieve and insert the file names in a table could be found: Get List Of Files From a Windows Directory to SQL Server […]
alex hatcher said
do it all in powershell, no need involve the gui or security hole of xp_cmdshell
Ian Wallace said
Hi,
The following works for shares and mapped drives and is quite simple
CREATE TABLE #IW (FileName VARCHAR(8000),Depth INTEGER, Files INTEGER)
INSERT INTO #IW
EXEC xp_dirtree ‘\\ServerName\c$\’, 10, 1
SELECT * FROM #IW
DROP TABLE #IW
AF said
Thanks Ian! this works absolutely fine!
vijayendra said
@Jfbevilaqua works fine, but does not provide the details of the subfolders and files within the subfolder
vijayendra said
can someone please help me in a similar requested that i have posted in the below link:
http://stackoverflow.com/questions/27611808/need-sql-code-for-all-file-folder-properties-including-all-subfolders-size-in-m?noredirect=1#comment43646135_27611808
sushmitha said
Hey Guys,
Please can anyone help me on how to fetch a file in a path and then delete those file without using ‘XP_*’
Expecting a quick reply..
Thanks