Solved

Losing data with bulk insert

Posted on 2011-09-15
12
560 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:kshumway
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36545316
Try \r\n.

\r is for the carriage return, \n is for the new row.
0
 

Author Comment

by:kshumway
ID: 36545334
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.
0
 

Author Comment

by:kshumway
ID: 36545404
BTW - thank you for your quick response...
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 17

Expert Comment

by:dbaSQL
ID: 36545445
can you post your bulk statement?  are you using a format file?

my pleasure.  :-)
0
 

Author Comment

by:kshumway
ID: 36545514
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

Open in new window

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 36545689
well, at this point i'd need to see your table def.  I just ran this, and it loaded everything fine:


drop table test
go
create table test (A char(5),B char(4),C varchar(25),D decimal(5,2),E char(5),F datetime,G char(6),H varchar(25),I datetime,J char(5),K char(20))
select * from test

begin
BULK INSERT test
FROM '\\myserver\c$\mine\data.txt'
WITH
      (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
      )
END


obvioulsy, i just created a big/generic table to take in the data from the file, but if you're taking into account that hidden column at the end, between the date and the HGB AND HCT stuff, then this should work.  the above loaded all 14 records in your file (with the double quotes).

                                                                                                                               
"CGH","2E","66400931",7.92,"0",08/01/2011,"HH","500120705",08/01/2011,,"HGB AND HCT"
                                                                                                                          ^
                                                                                                                          ^  this field


 
0
 

Author Comment

by:kshumway
ID: 36546209
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
0
 

Author Comment

by:kshumway
ID: 36547188
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!
0
 

Author Closing Comment

by:kshumway
ID: 36547190
Thank you so much for your help.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36548046
Excellent!  I am glad you figured it out.  And I am glad to have helped.
0
 

Author Comment

by:kshumway
ID: 36548516
Thanks again!
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36548634
>>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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question