BULK INSERT ERROR MESSAGE

Question

Post reply

BULK INSERT ERROR MESSAGE

Lester Vincent

Ten Centuries

Points: 1227
More actions
April 19, 2006 at 5:52 am

#117038

Hi folks.
I have SPROC which
1. Takes a string parameter containing the path and name of a text file, constructs a dynamic query containing the parameter, as follows: -
BULK INSERT dbo.tbl_OrdImports_Temp FROM 'C:\MyFolder\MyFile.TXT'
The text file is a simple line of text, which I am trying to import into a 2-column table in SQL-S.     The table's columns are
(a) RowNo int Identity (Primary Key), and
(b) Data varchar(255)
The table is truncated at first, then is to be filled with one text file each time the SPROC is called.    There can be any number of rows of text, between 100 and 300.
The error Message I get in Query Analyser is,
"Server: Msg 4832, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected end-of-file (EOF) encountered in data file." and the SPROC then aborts.
I have tried using the WITH statement, eg,
...WITH (FIELDTERMINATOR = '`', ROWTERMINATOR = '\n', LASTROW = '190'), etc and several variations of these, to no effect.
I am able to re-model the text file, as I use VBA code to create it from a Work Document.    Originally, I was creating the value for the RowNo column in VBA code, instead of having the SPROC create "Identity" values for it.     When I did get it to work, it inserted the odd-numbered rows and left out the even-numbered rows.
But now, it does nothing else but give me this error message.
Can someone please tell me what it might be looking for?
With thanks.

Lester Vincent
Sydney

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

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

David Burrows SSC Guru Points: 65144 More actions · Answer 1

To bulk insert into a table that has an identity column you have to use a format file otherwise you will get the error

Try this

8.0

1

1 SQLCHAR 0 255 "\r\n" 2 columnname ""

Replace columnname with the name of the column and 255 with it's length

BULK INSERT dbo.tbl_OrdImports_Temp

FROM 'C:\MyFolder\MyFile.TXT'

WITH (FORMATFILE = 'C:\MyFolder\MyFile.fmt')

Far away is close at hand in the images of elsewhere.
Anon.

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 2

Thanks David. Sounds logical. It's getting late here, (Sydney time), so I will try your suggestion in the morning.

I have never created a format file, and am not sure how to use the following information you have provided, ie

"8.0

1

1 SQLCHAR 0 255 "\r\n" 2 columnname ""

Replace columnname with the name of the column and 255 with it's length"

I presume these are parameters to put in the format file. ...

Can you advise me how to create this format file please.

Thanks David.

Lester Vincent

Sydney

David Burrows SSC Guru Points: 65144 More actions · Answer 3

Create the file (e.g. C:\MyFolder\MyFile.fmt) using text editor like notepad

therefore if your column name was DataCol and is 2500 chars long then create the file with the following on separate lines (3 in total)

8.0

1

1 SQLCHAR 0 2500 "\r\n" 2 DataCol ""

and run bulk insert

BULK INSERT dbo.tbl_OrdImports_Temp

FROM 'C:\MyFolder\MyFile.TXT'

WITH (FORMATFILE = 'C:\MyFolder\MyFile.fmt')

Far away is close at hand in the images of elsewhere.
Anon.

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 4

Thanks again, David. It just sounds too easy, but I'll run it in the morning now.

Cheers,

Lester Vincent

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 5

Hi David. I did exactly as you suggested, but received the following error message: -

"Server: Msg 4839, Level 16, State 1, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform bulk insert. Invalid collation name for source column 1 in format file 'C:\SQLS_BulkInsert_FormatFiles\OrderImports_BulkInsert.fmt'."

I created the format file exactly as suggested, and read up on Books-on-Line, about "using format files" in Bulk Insert.

What you provided looked right according to that, but BOL is very sparse on the collation value. No explanations.

In your example, I could not establish whether you had used four single quotes, or two double-quotes for collation name, so I tried both of these alternatives without success.

Any further ideas on this, David? It seems to be looking for a collation name ...

In BOL, their example shows "SQL_Latin_General" for the "pub_name" field in their example.

I will try replacing the quotes with that and see how it goes.

Will let you know.

Lester Vincent

Sydney

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 6

Hi David. Me again (sorry).

I tried the format file, with contents exactly as follows: -

8.0

1

1 SQLCHAR 0 2500 "\r\n" 2 Data SQL_Latin1_General

(as per the BOL example for 3 columns in "pubs..Authors2

Got the same error message, ie

"Server: Msg 4839, Level 16, State 1, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform bulk insert. Invalid collation name for source column 1 in format file 'C:\SQLS_BulkInsert_FormatFiles\OrderImports_BulkInsert.fmt'."

The text file is (as previously mentioned), a single row of text to be inserted in a 2-column table, of which the first is an "int Identity" data type.

It doesn't seemto matter whether I have "", or '''', or SQL_Latin1_General as collation name, the same error message regarding collation name appears.

Any ideas?

Lester Vincent

Sydney

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 7

Hi David. AS I had to solve this somehow today, I looked again at BOL, found the format file values had to be tab-separated. I had to correct this, and tried by (a) removing the "collation name" item completely. Didn't work.

(b) tried (again), by inserting "" (double quotes), as per your original example, and this time IT WORKED!!!

Many thanks for your help, David. So simple whe you know how.

I hope this 'trial-and-error dialogue is going to help at least onw other needy person!

CHeers,

Lester Vincent

Sydney

David Burrows SSC Guru Points: 65144 More actions · Answer 8

Happy to help

In BOL, their example shows "SQL_Latin_General" for the "pub_name" field in their example.

The collation column is mandatory and must be either a valid collation name (without quotes) or 2 double-quotes if no collation, any deviation from this and you get the error you stated.

found the format file values had to be tab-separated

Although BOL states this, this is not true, it can be space delimited (see below) as long as the correct number of columns is specified.

If you have trouble creating format files then do what I do

Run bcp to produce a format file for you, e.g.

bcp databasename.owner.tablename out temp.txt -S servername -T

Press enter for each prompt and Y to save file Enter for filename to accept default (bcp.fmt) or type in your own name

This will give you a file start with, you will need to alter it though

Make sure datatype is SQLCHAR for all columns

Change the separator for each column, ie "," "\t" etc (you must include the double quotes)

Make sure the last column has "\r\n" as a terminator

You may have to tweak it further depending on requirements as in your case where your IDENTITY column would be included but would have to be removed.

Far away is close at hand in the images of elsewhere.
Anon.

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 9

Thanks David. I will file your great tips in a safe place. This is a fantastic forum outcome. Hope I can be as helpful to someone else.

Cheers,

Lester Vincent

susanne.rossignol SSC Rookie Points: 29 More actions · Answer 10

yes, thanks for this forum. it was full of teaching and learning and that is unfortunately uncommon. i am under a deadline and looked around for a few hours for someone to lay things out simply and in a way that i coudl learn what was going w bulk insert. so thanks from a reader.