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