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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — 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 | |
Tidbytez 