SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Importing .txt file with carriage returns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CRafferty
Starting Member

United Kingdom
4 Posts

Posted - 03/25/2013 :  11:16:11  Show Profile  Reply with Quote
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

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 03/25/2013 :  11:22:13  Show Profile  Reply with Quote
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?
Go to Top of Page

CRafferty
Starting Member

United Kingdom
4 Posts

Posted - 03/25/2013 :  11:31:15  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/25/2013 :  12:02:47  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

CRafferty
Starting Member

United Kingdom
4 Posts

Posted - 03/25/2013 :  12:18:54  Show Profile  Reply with Quote
Hi visakh16

I 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.

Thanks
Caroline
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000