Monday, June 19, 2017

How to list files inside a folder with SQL Server

In this section, we are going to list all the files within the folder or directory by using T-SQL. This is very common scenario if you have SSIS environment to load the multiple files from the shared location. In absence of  SSIS or other ETL tools,  you have to write T-SQL codes to list down all the files in the file directory or folder before load their data into the system one by one. 

Database-level configuration options - Before using this code, we should change the global configuration settings for the current server as given below -
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO











T-SQL Code through xp_DirTree command - After doing the needful database level configuration setting, we are able to run the below code to list all the files from the shared folder as given below –
----- Table Variable to Store the file List
declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1))
----- Folder or Directory Path Variable
declare @inpFilePath varchar(200)='C:\Sample Data Pack\';

---- Insert Values into Table variable
INSERT INTO  @TotalFiles (SubDirectory, Depth , Files )
EXEC Master.dbo.xp_DirTree @inpFilePath,1,1







T-SQL Code through XP_cmdshell command - XP_cmdshell is the other option to list all the files from the shared folder but it requires more data filters as given below –
----- Table Variable to Store the file List
declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1))
----- Folder or Directory Path Variable
declare @inpFilePath varchar(200)='E:\Sample Data Pack\';

---- Set Dirctory command before folder path
SET @inpFilePath= 'DIR '+ ' "' + @inpFilePath +'"'
---- Insert Values into Table variable
INSERT INTO  @TotalFiles (SubDirectory)
EXEC Master.dbo.XP_cmdshell @inpFilePath

SELECT FilesName=Substring(SubDirectory,40,100)
FROM @TotalFiles
----- Remove all directories
where SubDirectory NOT Like '%<DIR>%'
----- remove bytes
AND SubDirectory NOT Like '%bytes%'
---- file name will start from 40
AND LEN(SubDirectory)>40
---- file name must have . extenion
AND SubDirectory Like '%.%'


No comments:

Post a Comment

Popular Posts