Text files in multiple subfolders
-
I have a project that will download data in text files to subfolders based on each user's name. The sub folders are all under the same higher folder, and are created automatically by the non SQL processes. I would like to import the text all into the same table. I will then parse the strings from that table and insert into the final datatable.
DTS seems to be the way to go here, but how can I pick up and import text into the table? Do I have to set up a package for each user because they are all in different subfolders?
-
I do not think you need a package for each user. You should be able to set up one package with multiple text file connections pointing to one SQL Server connection. The downside to this is each time a new user is added, you need to modify your DTS package.
You should look into bcp. This would allow you to script the import in DOS scripts. I think that with some crafty MS-DOS programming, you can create a bcp script that is dynamic enough to loop through all user folders and import data from each.
Another option would be to have your application, after placing the individual users files, create a master import file. This would then allow your DTS package to operate off of one source text file. This would also take care of adding new users.
-
I would suggest that you could do it using combinations of xp_cmdshell executing the dir command with the /s and /b switches, selecting the result into a temp table. Then you could use the textcopy.exe command line tool (look in your SQL Server's binn folder) to import each text file - again this could be launched using xp_cmdshell. A cursor could be used to execute the textcopy command once for each file. Execute textcopy.exe /? for information - I also gave a good example of using it in a post a couple of days ago - should be available on the forums here somewhere....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply