Importing Information Into SQL - Errors

Hi there, I would really appreciate it if someone could help me. At the moment part of our customer information is held in Lotus Approach and part in SQL.

I have exported the Approach information to Excel and am trying to import this into SQL. I have also saved the Excel sheet as a .txt file.

My issue is that three of the Approach fields are free text boxes, with lots of carriage returns. I have tried the following query to enter the information into SQL:

BULK INSERT livedev.dbo.CarriageReturns
FROM 'C:\Users\janitor\Desktop\TestData\Test.txt'
WITH ( FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\r\n',
FIRSTROW=2 )

This works fine on the simple fields but the large text fields are carrying over to the next row and creating lots of errors.

I have read lots of forums but just can't seem to find anything that works. I should have 437 records imported into SQL, but looking at a Hex editor - my data is showing as having 1124 lines because of the carriage returns.

It must be something to do with the ROWTERMINATOR and it doesn't know where the row ends - but I can't find a solution for this!

Thanks
Caroline

you posted in the MySQL forum, but i'm pretty sure you're using Microsoft sql server, so I've moved the thread over to that forum for you

Ooops yes I am! Thanks for that