kshumway
asked on
Losing data with bulk insert
I am using Bulk Insert to pull a large text file into my SQL table. I noticed that the number of rows in my table was less than the number of rows in the text file. When I looked more closely at the data, it appears that it's not detecting the end of the row and the start of the next row is attached to the last field of the previous row. When the stored procedure finishes, it shows (343520 row(s) affected). My table contains 171760 records which is half the number of rows affected. I assume my row terminator is incorrect??? I have it set to '\n'. I've attached a screen shot of the last column of a few records in my SQL table. The "CGH" after the two boxes is the first field of the next record in the text file. Does anyone have any ideas? Doc2.doc
ASKER
I tried that and I got the following error when I tried to execute the stored procedure:
Running [dbo].[InsertHospitalData] .
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Bulk Insert fails. Column is too long in the data file for row 1, column 12. Make sure the field terminator and row terminator are specified correctly.
Running [dbo].[InsertHospitalData]
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Bulk Insert fails. Column is too long in the data file for row 1, column 12. Make sure the field terminator and row terminator are specified correctly.
ASKER
BTW - thank you for your quick response...
can you post your bulk statement? are you using a format file?
my pleasure. :-)
my pleasure. :-)
ASKER
See attached. I've also attached a few rows of the .txt file that I"m importing and the table definition. I'll be offline for about an hour. Back online after that. Again THANK YOU! Data.txt Doc3.doc
ALTER PROCEDURE dbo.InsertHospitalData
as
begin
BULK INSERT [Hospital Billing Data SQL]
FROM '\\KSQLDB\Projects\HospitalInvoicesData.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n'
)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've attached my table definition. I made a few changes based on your definition, but I'm only getting six rows in my table. tabledef.doc
ASKER
I figured it out. I had an id field on the table. Took that off and I am getting all the data. Thank you so much for your help!
ASKER
Thank you so much for your help.
Excellent! I am glad you figured it out. And I am glad to have helped.
ASKER
Thanks again!
>>I had an id field on the table. Took that off and I am getting all the data.
by they way, in situations like this, it is always nice to use a format file. you can get around identity columns very easily.
something like this, where the sixth value is server column order. see, i am starting at server column #2, not #1, and we skip the ID:
9.0
4
1 SQLCHAR 0 7 "," 2 Col1 ""
2 SQLCHAR 0 100 "," 3 Col2 SQL_Latin1_General_CP1_CI_ AS
3 SQLCHAR 0 100 "," 4 Col3 SQL_Latin1_General_CP1_CI_ AS
4 SQLCHAR 0 100 "\r\n" 5 Col4 SQL_Latin1_General_CP1_CI_ AS
just an example, for the next time.
by they way, in situations like this, it is always nice to use a format file. you can get around identity columns very easily.
something like this, where the sixth value is server column order. see, i am starting at server column #2, not #1, and we skip the ID:
9.0
4
1 SQLCHAR 0 7 "," 2 Col1 ""
2 SQLCHAR 0 100 "," 3 Col2 SQL_Latin1_General_CP1_CI_
3 SQLCHAR 0 100 "," 4 Col3 SQL_Latin1_General_CP1_CI_
4 SQLCHAR 0 100 "\r\n" 5 Col4 SQL_Latin1_General_CP1_CI_
just an example, for the next time.
\r is for the carriage return, \n is for the new row.