#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    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

IMN logo majestic logo threadwatch logo seochat tools logo