mcmcom


Hi all,

Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.

My problem is two fold.

Scenario:

I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)

I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.

Running the files through my DTS produced errors on all files. The error was :
Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.

The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.

Basically i need to look at these text files and find Rows that are "incomplete"

the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.

any ideas

thanks,

mcm





Re: Importing text files into SQL Server - some problems

Sue Hoegemeier


Sometimes you have to use file editors so that you can view the ASCII codes in the file and make sure any file clean ups don't remove any non-printable codes that would throw off the file format. There are a lot of file editors available. I've used VEdit for these types of tasks before:

http://www.vedit.com/

-Sue






Re: Importing text files into SQL Server - some problems

Tim

I've gotten the same error before and resolved it by doing the following:

Import the text files into MS Access first. MS Access pinpointed anywhere I had and extra tab in my data. I then would either fix that line in my text file or pull it out completely from my file. I reloaded the text file in the database and the "Too many columns found..." error went away.

Tim