June 18, 2010 at 4:33 pm
Hi Experts,
can anyone help me with a script to load 10 or more .txt files from one folder on the server to the SQL Server database. All the new tables to be created same as the text file name. All the text files may have different columns within each. These tables do not exist in the database currently, they should be created. I am using SQL 2008..
Thanks much in advance...
June 18, 2010 at 5:03 pm
How will you know what the datatype and sizes of the columns are? How will you name the columns? Do you know the number of columns in each ahead of time?
Other than custom coding an application, I'm not sure there is any facility that will take an arbitrary text file and load it into a table that is created on the fly.
It might be easier to load all the files one by one into an already existing work table and deal with them once they're loaded.
Hard to say without more information. What's the business requirement?
June 18, 2010 at 5:30 pm
I'm with David. There's no direct way to do this and create tables. To a large extent, this is why SSIS exists. Why can't you use that?
If the tables are there, you can bcp data in, but the tables have to follow the structure of your files.
Is this something that happens regularly? Or is it a one time option?
June 18, 2010 at 6:56 pm
Each file has column names in the header and they are separated by tab. 3 of the 10 columns will be varchar(500) and the others will be float. This is just a one time requirement. there could be 100 such files and it would be difficult to load them all one by one.
June 19, 2010 at 2:21 pm
If they are delimited, you can load them with BCP, but likely you just want to use a loop to do this.
SSIS is not that hard to use, and you can build a basic package with the Import Wizard, then use this article with a little logic to easily import the rest.
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
June 20, 2010 at 9:03 am
If I understand the requirements correctly, each file can have a different set of columns.
In that case the main challenge is to create a new table for each file with the correct columns.
As far as I know there is no direct way in SSIS to do this.
To solve this problem you need some custom programming.
Find a developer who knows a general-purpose language like C#, VB, Java, Python, Perl or something like that.
A competent developer can write a custom program for this problem in a few hours.
Even if you use SSIS you will still need to write some script code to dynamically create the tables.
/SG
June 21, 2010 at 9:07 am
I missed the different columns for each. Apologies.
If this is a one time thing, I wonder if you can get this done, and tested, in 2-3 hours. In 2-3 hours, you could probably run the import wizard 100 times.
If this is something that will happen more than once, I'd definitely hire someone.
However, what's the purpose here? What kinds of text files were generated that need to be separate tables somewhere? I'm curious if someone has put forth a job without understanding a business requirement about how this will be used.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply