Post reply

Bulk Insert Grief

  • November 20, 2008 at 6:09 am

    #900994

    Jim,

    A good hex editor to use is Ultra-edit. I have used it extensively when dealing with flatfiles. Avoid Notepad, it will handle CR, CR\LF, and LF all the same way. It tends to mask some of the things that cause problems. You can also see the Unicode chars I mentioned in the previous post.

    Good Luck.

  • November 20, 2008 at 12:02 pm

    #901225

    I am making no progress...

    I inspected the source file carefully, and found it to have a CR/NL every 2016 characters.

    Trying to step back, I created a dummy input file consisting of 6 lines of 2016 "x" characters (with CR/NL line terminators).

    I can load that file (as I can load the real data file) if I do not specify a format file reference:

    -- import dummy with no format file

    bulk insert [HR_Joined].[dbo].[T1021COLTEST]

    from '\\K021ch-0008\ohc\Tech Team\Jim Russell\New J\FTP Downloads NFC\New Input Files\Dummy.txt'

    --(6 row(s) affected)

    But if I try to load the file with my 558-col format definition file, it fails.

    Worse, if I simply try to give a format file that defines only one column, into a table with a single VARCHAR(MAX) column that fails also.

    bulk insert [HR_Joined].[dbo].[T1021COLTEST]

    from '\\K021ch-0008\ohc\Tech Team\Jim Russell\New J\FTP Downloads NFC\New Input Files\Dummy.txt'

    with (FORMATFILE = 'I:\ohc\Tech Team\Jim Russell\New J\FTP Downloads NFC\New Input Files\T102RAWLINEcol.FMT')

    (Note, on the server (k021ch-0008), the I: drive contains the OHC share. Here the references are interchangeable.)

    Here is my one-column format file:

    9.0

    1

    1 SQLCHAR 0 2016 "\r" 1 [LINE] SQL_Latin1_General_CP1_CI_AS

    ...with the same meaningless error message I have been haunted with since I started:

    Msg 9422, Level 16, State 48, Line 1

    XML parsing: line 2, character 0, incorrect document syntax

    Jeff: did your "awesome idea" involve something other than BULK INSERT?

    Do you want my dummy file of "x"'s?

    Todd: Thanks for the Ultra-edit reference; our desktops are so locked down that I can't even install an evaluation copy, but I'll start the paperwork to see if I can add it to help me out the next time.

  • November 20, 2008 at 5:46 pm

    #901358

    Jim Russell (11/20/2008)

    Jeff: did your "awesome idea" involve something other than BULK INSERT?

    Combination of that and "something else". Dunno if it'll be worth it, but wanted to give it a try for you.

    Do you want my dummy file of "x"'s?

    Only if the dummy file of X's is giving you the same headache. I wanted the "real" file so I could troubleshoot it and give my "something else" a go.

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

  • November 21, 2008 at 7:14 am

    #901599

    Boy if you can see something in this Jeff (or Todd) I will be happy to accept the title of Dummy.

    I've attached a test data file (Dummy.txt), a format file (OneColFmt.txt) and a test script

    (BULK INSERT TEST.SQL.txt -- the .txt extention just to let me attach it). See if you can figure out a way to get results that are different than mine.

    Thanks very much!!!

  • November 21, 2008 at 7:38 am

    #901618

    Jim,

    Problem solved... 🙂

    The format file is in Unicode format. It needs to be saved a plain ASCII Text file.

    I was able to duplicate the issue with your original file. When I viewed the file with Ultra edit, it was obvious it was a Unicode file. I saved your file as an ASCII format, your file worked fine.

    Todd

  • November 21, 2008 at 8:05 am

    #901637

    AT LAST, A RAY OF HOPE -- WONDERFUL!!

    I created the format file with Notepad (I thought, maybe I pasted something in from a web page).

    So I need to strip out the upper bits from the format file?

  • November 21, 2008 at 8:10 am

    #901642

    Gee, just discovered that Notepad can save as ANSI, which did the trick for me.

    (Guess I never was good with these high-end complex applications, like Notepad!)

  • November 21, 2008 at 8:37 am

    #901667

    I just converted my 558 column format file to ANSI, and imported it to a 558 column temp table

    and it WORKED!

    Can't thank you guys enough!!

  • November 21, 2008 at 9:06 am

    #901702

    Glad to help.

  • November 22, 2008 at 3:15 pm

    #902089

    Jim Russell (11/21/2008)

    I just converted my 558 column format file to ANSI, and imported it to a 558 column temp table

    and it WORKED!

    Can't thank you guys enough!!

    They beat me to it (gettin' old), but now you know why I wanted you to post what you had.

    Real glad everything worked out well for you, Jim.

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

Viewing 10 posts - 16 through 24 (of 24 total)

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