Bulk Insert Grief

Question

Bulk Insert Grief

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

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

ToddE SSC-Addicted Points: 413 More actions · Answer 1

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.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

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

ToddE SSC-Addicted Points: 413 More actions · Answer 5

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

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?

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 7

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

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

ToddE SSC-Addicted Points: 413 More actions · Answer 9

ToddE

SSC-Addicted

Points: 413

November 21, 2008 at 9:06 am

#901702

Glad to help.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

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)
Intro to Tally Tables and Functions