Bulk Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-26 : 07:05:49

shree writes "I am trying to load some data into a table

TAB_DELETE
COL1 VARCHAR 10
COL2 NUMERIC 11,2

This the format file i am using BCP.FMT
8.0
2
1 SQLCHAR 0 10 "" 1 COL1 ""
2 SQLNUMERIC 0 9 "\r\n" 2 COL2 ""

the data file is fixed width and looks like this DATA.TXT
SHREE 19.12345
AB 123.0987

SQL STATEMENT THAT I AM USING is

bulk insert tab_delete
from '\\Shree\DATA.TXT'
WITH ( FORMATFILE = '\\SHREE\BCP.FMT')

BUT I all i see is this data in the TAB_DELETE table.
SHREE 0
AB 0

I want to see those 19.12345 and 123.0987 instead of the 0s.
Any suggestions?
Thanks,
Shree"

dpursell
Starting Member

3 Posts

Posted - 2005-01-11 : 12:37:48

I have the same problem using SQL Server 2000. I cannot get anything but zeroes inserted into numeric columns in my table despite the ASCII file having actual data. There must be a way to do this. The only thing I can offer until someone has a solution is to create char columns in your table and use format SQLCHAR to load the data.

dpursell
Starting Member

3 Posts

Posted - 2005-01-12 : 12:31:41

I ran a test yesterday creating a dummy ASCII test file in Notepad:

006TEST01
006TEST02
006TEST03
006TEST04

and the following format file:

8.0
2
1 SQLNUMERIC 0 3 "" 1 COL1NUM SQL_Latin1_General_Cp1_Cl_AS
2 SQLCHAR 0 6 "\r\n" 2 COL2CHAR SQL_Latin1_General_Cp1_Cl_AS

and executed BULK INSERT and it loaded in 006 and shows up as the numeric value: 6 in my table. Worked beautifully.

Now running this same thing on my production files that I received from the client I still get the zeroes in my numeric columns. I'm not sure why it won't load properly. Must have something to do with the way the client created the flat file.

Anyone???

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-12 : 13:31:02

format file should be

8.0
2
1 SQLCHAR 0 10 "" 1 COL1 ""
2 SQLCHAR 0 9 "\r\n" 2 COL2 ""

That SQLCHAR is the format of the data in the file which is always SQLCHAR for an asci file.
have a look at

http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dpursell
Starting Member

3 Posts

Posted - 2005-01-14 : 17:21:05

SQLCHAR makes sense, except I was getting errors bulk inserting into a numeric column in my table so I "assumed" that it was because I needed to use SQLNUMERIC in the format file. Upon closer inspection of the errors, it was a Bulk Insert Overflow Error.

Ok, so the problem was this:

"125" read into a table column defined as Numeric(3,1) is an overflow because SQL needs to preserve one position for the decimal. My data does not contain any decimals so the overflow occured. Fixed the problem by altering the column to Numeric(4,1). SQLCHAR bulk inserts fine now.

Grandpretre
Starting Member

5 Posts

Posted - 2005-04-13 : 08:37:34

I used this kind of format file and I also generate one in order to see where was the mistake. But I always have the same problem :

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 3 in format file 'c:\essai.fmt'.

It doesn't matter what collation I set, it's always false on the last column...Did I have to configure SQLserver 2000 ? cause I never succeeded in starting a bulk insert ...

amurphy12
Starting Member

1 Post

Posted - 2005-04-19 : 13:56:01

I had the same problem on any format file ending in a non-numeric column...to fix it, I added a line feed after the final column's collation. Wierd.

quote:
Originally posted by Grandpretre

I used this kind of format file and I also generate one in order to see where was the mistake. But I always have the same problem :

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 3 in format file 'c:\essai.fmt'.

It doesn't matter what collation I set, it's always false on the last column...Did I have to configure SQLserver 2000 ? cause I never succeeded in starting a bulk insert ...

Grandpretre
Starting Member

5 Posts

Posted - 2005-04-28 : 04:59:32

quote:
I had the same problem on any format file ending in a non-numeric column...to fix it, I added a line feed after the final column's collation. Wierd.

I don't understand what you'r doing whith this line feed ... Can you give me an example ?? If I add any character(like a space for example), I obtain an other error message :

Bulk insert data conversion error (truncation) for row 1, column 1 (Firstname).

etc for each of my text's row

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 13:33:55

Amurphy, I experienced that same issue. You need to hit enter on the last line(at the end of the line), like you said.

I believe its because the terminating character in the format file is a "/r/n" in most cases.

Mike Petanovitch

Grandpretre
Starting Member

5 Posts

Posted - 2005-04-29 : 05:02:44

If I hit enter, I have the same message : Bulk insert data conversion error (truncation) for row 1, column 1

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-29 : 15:29:41

Your table has the wrong datatype.
123.0987 should be represented as decimal(7, 4) not NUMERIC 11,2 (also numeric is old notation although equalivant to decimal)

Info:
decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Mike Petanovitch

Grandpretre
Starting Member

5 Posts

Posted - 2005-05-02 : 04:17:55

I think you don't understand what exactly is my problem or I don't understand what's your solution

So I will explain :

The file that I want to transform into table is "essai.txt"

Firstname  Lastname  	Adress                        
1234567890 1234567890	123456789012345678901234567890
1234567890 1234567890	123456789012345678901234567890

The format file that I use is "essai.fmt"

8.0
3
1 SQLCHAR 0 10 "" 1 FirstName SQL_Latin1_General_Cp1_CI_AS
2 SQLCHAR 0 10 "" 2 LastName SQL_Latin1_General_Cp1_CI_AS
3 SQLCHAR 0 30 "\n\r" 3 Address SQL_Latin1_General_Cp1_CI_AS

The Bulk Insert command looks like this :

bulk insert T_ESSAI from 'c:\essai.txt' with(FORMATFILE = 'c:\essai.fmt', CODEPAGE=1250 )

When I add a line feed at the end of my format file, I obtain the error I told U before and when I don't, I obtain this one :
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 3 in format file 'c:\essai.fmt'.

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-02 : 20:15:11

I was going based of your orginal post and your last error.

Your table looks like this:
COL1 VARCHAR 10
COL2 NUMERIC 11,2

And your data looks like this:
SHREE 19.12345
AB 123.0987

And your last error was this:
Bulk insert data conversion error (truncation) for row 1, column 1

I said, make sure your table which you are bulk inserting into has the correct datatypes. Thats all.

Mike Petanovitch

Grandpretre
Starting Member

5 Posts

Posted - 2005-05-04 : 03:51:57

That's sure, tables that I'm creating have the correct datatypes.
The conversion error appends when i add a line feed at the end of my format file, so that's not a prblem of datatype ...

dwhite
Starting Member

2 Posts

Posted - 2007-01-05 : 13:47:12

This error:
Bulk insert data conversion error (truncation) for row 1, column 1

is sometimes caused by using
ROWTERMINATOR = '/r/n'

Try it with
ROWTERMINATOR = '/n'

worked for me.

dwhite
Starting Member

2 Posts

Posted - 2007-01-05 : 13:49:17

OK, I'm an idiot - reverse the slashes

ROWTERMINATOR = '\r\n'

Try it with
ROWTERMINATOR = '\n'

suns
Starting Member

1 Post

Posted - 2007-09-16 : 09:40:14

I am also getting same error...My data file is
CustomerID CustomerName ContactName ContactDetails
---------- ------------- ----------- ------------------
ALfki bsjfsjfasbjf dfgfgh Sales representative
alfki edtytjgnjmk, gcggf marketing person
alfki dbgfhsgfjmgkjk ghfhfhng accounting personnel
Alfki adhgysuiklolmngf fdbfhg marketing person
bondi dbgfhsgfjmgkjk fghfhfh marketing person
bolndi adhgysuiklolmngf fhfhdgfb marketing person
ALfki dbgfhsgfjmgkjk dhgghgh accounting personnel
Alfki dbgfhsgfjmgkjk tfhdthty marketing person
bolndi adhgysuiklolmngf fdhfhppany Sales representative

and format file is..
8.0
4
1 SQLCHAR 0 5 "" 1 CustomerID SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 40 "" 2 CompanyName SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 0 30 "" 3 ContactName SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 0 30 "\r\n" 4 ContactTitle SQL_Latin1_General_Cp437_BIN

getting an error saying please help..how to solve this.

Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:\format.fmt'.

inon
Starting Member

1 Post

Posted - 2010-05-03 : 03:15:31

All what you need to do it:
ADD A NEW LINE AFTER THE LAST LINE IN THE FORMAT(.FMT) FILE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources