Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CRafferty
Starting Member
4 Posts |
 Posted - 2013-03-25 : 11:16:11
|
| 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.CarriageReturnsFROM '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!ThanksCaroline |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-25 : 11:22:13
|
| 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? |
 |
|
|
CRafferty
Starting Member
4 Posts |
Posted - 2013-03-25 : 11:31:15
|
| Thanks for your reply James! Will have a look into this - I think with Approach the options are not that advanced. If it comes to it I will just have to remove the carriage returns manually in Excel.Thanks for your help! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 12:02:47
|
| so whats the delimiter in case of large text fields? why not use a format file to specify exact delimiter in that case?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
CRafferty
Starting Member
4 Posts |
Posted - 2013-03-25 : 12:18:54
|
| Hi visakh16I am sorry I am not really sure what you mean? The delimiter is a tab.I have just downloaded a tool which has removed all of the carriage returns and line breaks. This has worked and I have managed to get the data into SQL now without any errors.It would have been nice to keep the carriage returns, as I will be designing a programme in Visual Basic with the text boxes taken from Approach. But just couldn't find a way of doing this.ThanksCaroline |
|
|
|
|
|
|
Importing .txt file with carriage returns