Bulk Insert problem

[umararif]

Hi DB Gurus,

I am using SQL server 2000 and VB6 for my project. In its one module I have to import a raw data from a text file having row terminator = \n and field terminator = TAB. I am using following SQL to import data.

BULK INSERT billing_master.dbo.test 
FROM 'c:\abc.dat'                         --Raw data source file
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n', FIRE_TRIGGERS)

The problem is in raw data source file. There is an extra "ENTER/Line Break" at the end of last row of data. Means last row contains 2 x \n.
Due to this my above SQL enable to import data into DB. But when I manually delete last one "ENTER/Line Break" (by oppeing file in notepad and removing it), my query works fine.

Can anyone give me a solution either in SQL or in VB6, also the attached image explain the my questions graphically.

Thanks in advance.

Umar

[Krzemo]

There is no way to import malformed file by BULK INSERT. However in case of only one extra "\n" it should work (I've checked that - if last row ends with \n or \n\n it works, but if there is \n\n\n or more it fails). Is SP4 installed?

And if U use VB6 and UR shure that it ends with exactly 1 extra \n than U can trim file in VB6 code before BULK INSERT.

Best regards,
Krzemo.

[umararif]

Can't use "\n\n" because row terminator = "\n" only. also I am confirm (and you will too if you see the attached image) that last row contains extra \n.

If it is not possible in SQL, kindly give me solution of triming last row from VB6.

Thanks

Umar

[Krzemo]

Can't use "\n\n" because row terminator = "\n" only.

As I said before - it shoud work (I've tested that case) - it works for me for sure! I have MsSQL2000 SP4 installed. Maybe U should Install SP4 for MsSQL2000 to fix your problem.

also I am confirm (and you will too if you see the attached image) that last row contains extra \n.

I've already saw your attached image before, and ... believe me, I know the difference between "\n" and "\n\n".

Please read carefully what I wrote before .

kindly give me solution of triming last row from VB6.

Here is the simplest (but not optimal for sure) and working solution. If U are writting in VB6 than U should be familiar with it and I'm sure U manage to improve that code. But if U have problems with VB than U should post in VB forum instead.

 
	Dim FSO
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Dim TextStream
	Dim s
	' There are several ways to open a text file, and several ways to read the
	' data out of a file. Here's one way to do it:
	Set TextStream = FSO.OpenTextFile("C:\abc.txt", 1)
	s = TextStream.ReadAll
	TextStream.Close
	s = Replace(s, vbCr & vbCr, vbCr)
	s = Replace(s, vbLf & vbLf, vbLf)
	s = Replace(s, vbCrLf & vbCrLf, vbCrLf)
	'And several ways to write file - here one:
	Set TextStream = FSO.CreateTextFile("C:\abc.txt", 2)
 
	TextStream.Write (s)
	TextStream.Close

Best regards,
Krzemo.

[umararif]

Dear Krzemo,

I have installed MSSQL Server 2000 with SP4, but still I am receving following error.

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 38. Make sure the field terminator and
row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give
any information about the error.].
The statement has been terminated.

I have attached 2 raw data files (filename = cdr.date.dat). and a structure of destination table "Test" (filename = test table create.txt). Kindly test at your end.

VB solution will work, but not applicable because I have to import a huge data on daily bases and it will take so long time to replace \n\n to \n (nearly 1/2 seconds for each row, tested in notepad). My raw data contains approx 0.4 to 0.5 million rows. To save time it is easy to manually edit the file in notepad. But I want a automated solution.

Please consider

Thanks again for your time.

Umar

[Krzemo]

There is only 1 \n in that file ("cdr.20061124.dat" - see attached image) as hex viewer shows. So your problem is NOT extra terminator related at all!! (It has only one \n and it fails). So do not use MsWord to check structure of the file since it tricks U.


The message is:

Bulk Insert fails. Column is too long in the data file for row 1, column 38

So it fails at first line (not the last).

It seams that BULK INSERT accepts files with vbcrlf row separator when U specify ROWTERMINATOR = '\n'. So your problem IS NOT an extra \n, but windows/unix text file format related error (In your file is \n as separator, U specified \n as a row separator but BULK INSERT expects \r\n as a row separator in the file - strange ).

Saving file with Word changes \n to \r\n in that file.

Best regards,
Krzemo.

[umararif]

not wroking with combination of \r \n

[Krzemo]

not wroking with combination of \r \n

Wat is not working?

If U specify ROWTERMINATOR = '\n' U have to change row separator in files to '\r\n' - otherwise it won't work (I don't know why - it is a Microsoft bug I suppose).

ROWTERMINATOR = '\r\n' won't work because it somehow expects '\r\r\n' in the file.

So if U can U should change your export utility that way, that it will use '\r\n' as a row separator and specify ROWTERMINATOR = '\n' for bulk insert. Or write in VB convert routine. U can also download some sort of command line file converter from Internet which converts files to Windows standards (It will be many times faster than VB code) or write your own.

Best regards,
Krzemo.