Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Dynamically pull all text file names from a folder into a SQL Server table
-- THIS QUERY USES CMD TO CAPTURE ALL THE FILES IN A FOLDER
-- AND WRITE THE FILE NAMES TO A TABLE
-- THE QUERY REQUIRES THAT xp_cmdShell BE ENABLED TO WORK
-- THE FOLLOWING ENABLES xp_cmdShell:
-- 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
-- THE FOLLOWING CREATES A TABLE AND POPULATES IT WITH THE FILES LOCATED IN THE SPECIFIED DIRECTORY
IF OBJECT_ID('ALLFILENAMES', 'U') IS NOT NULL
DROP TABLE ALLFILENAMES
CREATE TABLE ALLFILENAMES (
WHICHPATH VARCHAR(255)
,WHICHFILE VARCHAR(255)
)
DECLARE @filename VARCHAR(255)
,@path VARCHAR(255)
,@cmd VARCHAR(1000)
-- GET THE LIST OF FILES TO PROCESS
-- NOTES: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED
SET @path = 'C:\Users\Data\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES (WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES
SET WHICHPATH = @path
WHERE WHICHPATH IS NULL
SELECT *
FROM ALLFILENAMES
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment