Importing data Question

[Rene1024]

Hello,

I'm trying to import a very large tab delimited text file into SQL server.

The .txt file is about 2.6 gb and it contains almost 16 million records.

The import process starts fine and then I get an error that says:

"error at source for row number 666749.
Column delimeter not found"

At first I thought the txt file was damaged, so I asked the provider to replace it and they did, but I continue to get the same message.

Has anyone experinced something like this?

Thanks for any help you can provide.

RGDS,

Rene

 

[pweegar]

Would certainly be interesting to see the data at the row where the error message says the data is bad. What does your error log say?

 

[JayKusch]

have had that issue bringing in Web log files. The work around I used was to bring it in Access first. Access is able to parse out the bad records and retain the good ones in the target table. from there I imported them into SQL Server. As an added bonus, the erroneous records are put into and error table and in most cases it will descibe the error that occured.

Thanks

J. Kusch

 

[Rene1024]

Thanks to both of you for the replies,

Pweegar: I very new to SQL and don't know how to read the error log.

JayKusch: I tried to open import the text file in to access, but I think because the text file is 2.6 gb access can't handle it. I get a message that the text file contains no data, even though I know there's data in there.

Is there a tsql command that I can use to import the data and skip any bad records on the text file?

Thanks again for your help.

rgds,

Rene

 

[JayKusch]

Is there any way to break that large 2.6GB txt file into a few smaller ones. I use an app called TextPad that is very helpful in this matter.

Thanks

J. Kusch

 

[Rene1024]

Thanks JayKusch,

I'll look for information on that Application.

I'll let you know what happens.

Rene

 

[Rene1024]

It didn't work JayKusch, I downloaded the application, but when I try to open the file it tells me that the disk is full while accessing my file, I think that it could be because the file is really large.

Thanks again,

rgds,

Rene

 

[evaleah]

I found an article that exactly relates to your problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;301060&Product=sql2k

Unfortunately, it is far from encouraging but at least it will let you know that you are not crazy.....

Good luck.

 

[Rene1024]

Thanks for all the help, I finaly was able to solve my problem. it turned out to be that some of the fileds contained " marks, so I used a text editor (Ultra Edit) and was able to clean the file. After that I was able to import the file.

Thanks again for all the help.

Rgds.

Rene