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