SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

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.

7 Responses to “Get List Of Files From a Windows Directory to SQL Server”

  1. shankar said

    Gr8. How can i retrieve date modified, type, size columns as well

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

  3. 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?

  4. Clean and simple.
    Thanks very much for the tip.
    Regards,
    Roni

  5. […] 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 […]

  6. do it all in powershell, no need involve the gui or security hole of xp_cmdshell

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: