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!
If there are indeed 1124 CR/LF instances in the file, but you want only 437 of those, I can't think of a programmatic way for the bulk insert (or any other utility/program) to discern which ones to keep and which ones to discard.
Is there anything in Approach that would allow you to export the data without the spurious CR/LF pairs?