April 1, 2011 at 10:57 am
Hello all,
I am a newbie trying to import text files into SQL Server 2008 R2. I was given 12 text files that came from another SQL Server instance. Whoever that created those files, I think, exported all the results to text and copy pasted the results into a text editor (EditPad Lite etc.) and saved it. It doesn't have delimiters, second line has -'s and last few lines have spaces and number of rows affected (Ex: (1000 row(s) affected)). The individual files are big 1.5 - 2.0 GB and I was asked to import all these 12 files into a SQL Server 2008 R2. Can someone help share their ideas/thoughts on how I can bulk import these files. There are many files like this that are coming my way!
I have a dumb solution. Open each file with EditPad Lite, remove the second line, delete the last few lines, save it, import the text file into Excel 2007, parse it per the definition (text/number/general/date), save it and import into SQL Server 2008. This method works but it is a pain to do repeat all these steps for every file and more over when the file has more than 1M records this method doesnt work. I will try if I can do the same thing in Access 2007 for files that have more than 1M records.
Any help is greatly appreciated.
Thanks!
April 1, 2011 at 12:18 pm
You could try to import each file into a one column staging table. Then write a select against the table to exclude rows, you will probably need to use LIKE. You may want to delete the junk rows at this point. Then try to parse your data out into columns which will be inserted into a destination table.
For the last step you could post representative sample data here and perhaps get some guidance on how to approach this.
Good luck...!
April 3, 2011 at 7:16 pm
I like your own creativity.
The only additional thought I have is to automate your solution with SSIS which will apply the formatting to each file in a folder, so that you don't have to do this manually.
Hope to see other answers here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply