Post reply

Bulk Insert: end of file marker and end of line

  • February 28, 2008 at 9:30 am

    #179438

    I have a file that needs to be bulk inserted into a table. I have two issues. The file may or may not contain an end of file, and may or may not contain an end of line. Each line represents a record that is read into a table row.

    How would I have to write the code to resolve these issues?

    This is what I have so far (just relevant code snippet):

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[atable]') AND type in (N'U'))

    DROP TABLE [dbo].[atable]

    GO

    CREATE TABLE atable

    (

    RECORD_FILE VARCHAR(200)

    )

    GO

    Truncate Table atable

    if @file_name is null

    begin

    set @file_name = 'c:\reports\afile.txt'

    end

    DECLARE @bulk_cmd varchar(1000)

    SET @bulk_cmd = 'BULK INSERT dbo.atable

    FROM ''' + @file_name + '''

    WITH (ROWTERMINATOR = '''+CHAR(10)+''')'

    EXEC(@bulk_cmd)

  • February 28, 2008 at 9:45 am

    #783331

    Try using the new line terminater. It may not have carriage returns but it should have a new line.

    = new line

    \r = carriage return\line feed

    Additional documentation on terminators

    http://msdn2.microsoft.com/en-us/library/ms191485.aspx


    My blog: http://jahaines.blogspot.com

  • February 28, 2008 at 10:00 am

    #783338

    which one should I use newline or \r? Or can I test for both?

    Also, how would I have to change my code to include this?

  • February 28, 2008 at 10:07 am

    #783339

    For some reason the \ n was stripped from my post. This is the new line character.

    Well typically you use \r for carriage return/line feed. I would start with this. If you open the file in notepad and the rows are very distiguishable and aligned, then chances are you need \r. If not then you might want to use \ n.


    My blog: http://jahaines.blogspot.com

  • February 28, 2008 at 10:16 am

    #783347

    Thanks, what about checking for end of file?

  • February 28, 2008 at 10:28 am

    #783354

    There is an end of file marker embedded in there somewhere. You should not have to check for it, unless you have something outside the norm, like a delimiter at the end.


    My blog: http://jahaines.blogspot.com

  • February 28, 2008 at 11:14 am

    #783372

    I appreciate your help. It worked!

  • February 28, 2008 at 11:43 am

    #783390

    NP 😉 Thanks for the feedback.


    My blog: http://jahaines.blogspot.com

  • March 5, 2008 at 11:29 am

    #785672

    I hope you can help me again. What if the sp reads in the end-of-file marker, how can I prevent that from happening?

  • March 5, 2008 at 11:46 am

    #785682

    The end of file marker is typically an ASCII 26 and SQL Server will not read it in. You don't have to prevent it from happening.

    --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)

  • March 5, 2008 at 11:49 am

    #785686

    It is reading it in though.

  • March 5, 2008 at 12:02 pm

    #785697

    Ok... since you can't actually see Ascii 26 except maybe as a supurious square, how can you tell the end of file marker is being loaded?

    --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)

  • March 5, 2008 at 2:17 pm

    #785763

    I see a square in the lost row inserted. It doesn't have any record information but for the square in the last row.

  • March 5, 2008 at 2:23 pm

    #785768

    The bulk insert, inserts records from a txt file. The txt file has the square bracket at the very end of the page/ or last record. Should that bracket be there in the txt file?

  • March 5, 2008 at 2:24 pm

    #785769

    Sounds like you have an extra line your text file. Delete the last row of the file. It should not contain any data just a hard return like in MS Word.

    This is a guess, a blank line may or may not exist in your file.


    My blog: http://jahaines.blogspot.com

Viewing 15 posts - 1 through 15 (of 25 total)

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