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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try \r\n.

\r is for the carriage return, \n is for the new row.
kshumwayAuthor Commented:
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.
kshumwayAuthor Commented:
BTW - thank you for your quick response...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

can you post your bulk statement?  are you using a format file?

my pleasure.  :-)
kshumwayAuthor Commented:
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
BULK INSERT [Hospital Billing Data SQL]
FROM '\\KSQLDB\Projects\HospitalInvoicesData.txt'

Open in new window

well, at this point i'd need to see your table def.  I just ran this, and it loaded everything fine:

drop table test
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

FROM '\\myserver\c$\mine\data.txt'
      ROWTERMINATOR = '\n'

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kshumwayAuthor Commented:
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
kshumwayAuthor Commented:
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!
kshumwayAuthor Commented:
Thank you so much for your help.
Excellent!  I am glad you figured it out.  And I am glad to have helped.
kshumwayAuthor Commented:
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:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.