How to dynamically pull all text file names from a folder into a SQL Server table

So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.

To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .

-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED

Sayōnara

— 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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s