Post reply

BULK INSERT problems

  • March 16, 2009 at 2:35 pm

    #198082

    I am trying to do a Bulk Insert from a .txt file into a SQL Server database table. The .txt file is pipe delimited. When I run the code for the BULK INSERT the first row populates, but when it gets to the last field it puts the rest of the data in the file in that field. I'm not sure what the problem is. When I open the file with UltraEdit all of the records are on sepate lines. Any help would be greatly appreciated.

  • March 16, 2009 at 3:02 pm

    #960612

    Could you post the format file you are using and some (anonymiced) sample data?

    Hint:

    Be careful with UltraEdit configuration. Default configuration is to automatically convert UNIX line-feed to windows carriage-return/line-feed. Have a look to the options.

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • March 16, 2009 at 7:02 pm

    #960720

    I don't use a format file, it just comes to me as a text file. Unfortunately I don't have any test data it is all real data. I don't save the file with Ultra Edit settings, I was just using it to view the data after the bulk insert didn't work. I can say that if I open it in wordpad and do a save as then it works fine, but I can't manually do that with every file.

  • March 16, 2009 at 10:20 pm

    #960781

    It's going to be tough to troubleshoot this without some test data and a listing of the code that does the Bulk Insert. If the data has private information in it, then make some up for a couple of lines of data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 17, 2009 at 7:46 am

    #961016

    Here is the code for the bulk insert

    BULK INSERT Test_Table

    FROM 'sourcefolder\filename.txt'

    WITH

    (

    FIELDTERMINATOR = '|',

    ROWTERMINATOR = '|'

    )

    Here is some test data:

    66101212|Hotel Inn |0064963 |Ted Smith|123-456-7890|Guest hardware|Guest hardware|123|Call reason: Browsing |Guest hardware|Guest issue was resolved|2009-02-10 12:01:09|2009-02-10 12:10:57|

    66101213|Hotel Inn Raleigh |0064963 |John Smith|123-456-7890|Guest hardware|Guest hardware|123|Call reason: Windows XPConnection |Hardware|Issue was resolved|2009-02-10 12:01:09|2009-02-10 12:10:57|

    66101229|Test Hotel San Francisco |0037805 |Scott Smith|999-999-9999|User instructions|User instructions|444|Call reason: Cannot login.|User instructions|Refer to front desk|2009-02-10 12:06:53|2009-02-10 12:18:21|

    In the text file each record shows on one line. Each record starts with the

    I have tried several row terminators and nothing seems to work.

  • March 17, 2009 at 7:49 am

    #961021

    Try to change your ROWTERMINATOR to:

    -- Replace the @ with a backslash. The forum cuts them if I use it...

    ROWTERMINATOR = '|@r@n'

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • March 17, 2009 at 9:55 am

    #961182

    I just tried that and get the same result.

  • March 18, 2009 at 7:36 pm

    #962414

    That's why I suggested attaching the first 10 lines of the file... so I can see what the termination character is in binary.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 19, 2009 at 2:42 am

    #962530

    Hi,

    No need to put the ROWTERMINATOR, when the records in the line ie it row alignment.

    So try to put all row data in one line then second row data in the second line in the text file.

    Then try

    BULK INSERT Test_Table

    FROM 'sourcefolder\filename.txt'

    WITH

    (

    FIELDTERMINATOR = '|'

    )

    Regards,

    ARUN SAS

  • March 20, 2009 at 6:31 am

    #963466

    Have you tried using a format file with your bulk insert. I use something like this :

    BULK INSERT TBL_CLIENT_TEMP FROM ''x:\path\FileToImport.txt''

    WITH (FORMATFILE = 'x:\path\FileToImport_Format.txt'', DATAFILETYPE = ''char'',

    FIRSTROW = 2, KEEPNULLS, FIELDTERMINATOR = ''\t'', ...)

    And my format file looks like :

    -------------------------------------------------

    9.0

    5

    1 SQLCHAR 0 50 "\t" 1 Client_Identifier ""

    2 SQLCHAR 0 50 "\t" 2 Client_Active_First_Date ""

    3 SQLCHAR 0 50 "\t" 3 Client_Charge_Off_Date ""

    4 SQLCHAR 0 50 "\t" 4 Client_Monetary_Last_date ""

    5 SQLCHAR 0 50 "\t" 5 Client_Payment_Last_Date ""

    -------------------------------------------------

    That way you can specify what goes where.

    These links helped me a lot:

    http://msdn.microsoft.com/en-us/library/ms179250.aspx

    http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm

    http://doc.ddart.net/mssql/sql70/impt_bcp_12.htm

  • March 20, 2009 at 11:01 am

    #963693

    arun.sas (3/19/2009)

    Hi,

    No need to put the ROWTERMINATOR, when the records in the line ie it row alignment.

    So try to put all row data in one line then second row data in the second line in the text file.

    Then try

    BULK INSERT Test_Table

    FROM 'sourcefolder\filename.txt'

    WITH

    (

    FIELDTERMINATOR = '|'

    )

    Regards,

    ARUN SAS

    There certainly is if it's not a standard terminator even if it looks ok on screen.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply