Bulk Insert Problems

me

I'm also having problems getting the bulk insert to work. I don't know
anything about it except what I've gleened from BOL but I'm not seeming to
get anywhere...Hopefully there is some little (or big) problem with my code
that someone can point out that may save me some time.

TIA

CBL
here is the table i'm trying to insert to.

/*Table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Description]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber] DEFAULT
(''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00

Here is the format file: (BOL hasn't given great examples so this is as
close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

Here is one of the errors I recieve

Server: Msg 4824, Level 16, State 1, Procedure pts_ImportTest, Line 32
Could not bulk insert. Invalid data type for column number 1 in format file
'\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'.

Jul 20 '05 #1

Erland Sommarskog

me (me@work.com) writes:

I'm also having problems getting the bulk insert to work. I don't know
anything about it except what I've gleened from BOL but I'm not seeming
to get anywhere...Hopefully there is some little (or big) problem with
my code that someone can point out that may save me some time.
OK, there are some problems with what you have achieved so far, but
since you provided full information - CREATE TABLE, sample data file,
format file and command - it's easy to help you. (And trying to learn
how to write format files from Books Online is not particularly easy.)
Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00
I was about to say that I hope that column headers are not part of the
data file, but I actually found when testing that it actually works. I
will return to why.
Here is the format file: (BOL hasn't given great examples so this is as
close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS
14 is the number of columns in the format file, so you have the wrong
number. Here is a format file that works with your table and data file:

8.0
7
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 2 col1 ""
3 SQLCHAR 0 0 "\",\"" 3 col2 ""
4 SQLCHAR 0 0 "\"," 4 col3 ""
5 SQLCHAR 0 0 "," 5 col4 ""
6 SQLCHAR 0 0 "." 6 col7 ""
7 SQLCHAR 0 0 "\n" 0 "" ""

Some notes:

o Column lengths does not matter, as long as you have delimiters, so 0 is
fine.

o The names in the next-to-rightmost column are informational only. It's
the column numbers to the left that matters. Still a good idea to put
in the actual column names, but I was too lazy to do it.

o Collation name can be specified to "" which may give undesired
conversions, then again the default collation name may not always
be right either.

o The first column in the data file as defined here, is the empty space
before the first ". This is why the column header does not cause any
problems. It's simply the first field on the first row. Since we say
0 in the server-column column, this means that we are just throwing
this field away.

o The "\",\"" is bulky, but needed when the delimiter is ",". BCP does
not know about quotes as string delimiters - it only knows about field
delimiters, so we have to define the quotes as part of the delimiter.

o The delimiter for field 6 is period (.). This is because I found when
testing that BULK INSERT barfed when I tried to import 1.00 to an
integer column. (The command-line BCP didn't mind, though.) I don't
know about the data in the file, but maybe you should change the
definition of that column to float or decimal. (In which case you
should update the format file.)

o The last field has \n as terminator. Note that there is no necessity
than one line in the file is one row in the table, but data may have
line breaks, and you have more than one rows on the same text line.

o Note that there is no 1 in the server-column column; this is the
identity column which we are not importing.
and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

When you use a format file, there is no need for FIELDTERMINATOR and
ROWTERMINATOR. These are shortcuts for certain appearance of the format
file. (As is DATAFILETYPE = 'native'.)

Here is my BULK INSERT command:

bulk insert ItemTest from 'E:\TEMP\slask.bcp'
with (formatfile = 'E:\temp\slask.fmt')
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

John Bell

Hi

You could try DTS?

John

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

I'm also having problems getting the bulk insert to work. I don't know
anything about it except what I've gleened from BOL but I'm not seeming to
get anywhere...Hopefully there is some little (or big) problem with my code that someone can point out that may save me some time.

TIA

CBL
here is the table i'm trying to insert to.

/*Table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Description]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber] DEFAULT
(''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00

Here is the format file: (BOL hasn't given great examples so this is as
close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

Here is one of the errors I recieve

Server: Msg 4824, Level 16, State 1, Procedure pts_ImportTest, Line 32
Could not bulk insert. Invalid data type for column number 1 in format file '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'.

Jul 20 '05 #3

me

Thanks much for all the help!
DTS is what I'm currently using and I would like to put this into a stored
procedure...

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

I'm also having problems getting the bulk insert to work. I don't know
anything about it except what I've gleened from BOL but I'm not seeming to
get anywhere...Hopefully there is some little (or big) problem with my code that someone can point out that may save me some time.

TIA

CBL
here is the table i'm trying to insert to.

/*Table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Description]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber] DEFAULT
(''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00

Here is the format file: (BOL hasn't given great examples so this is as
close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

Here is one of the errors I recieve

Server: Msg 4824, Level 16, State 1, Procedure pts_ImportTest, Line 32
Could not bulk insert. Invalid data type for column number 1 in format file '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'.

Jul 20 '05 #4

John Bell

Not even?

http://www.sqldts.com/default.aspx?210

John

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

Thanks much for all the help!
DTS is what I'm currently using and I would like to put this into a stored
procedure...

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

I'm also having problems getting the bulk insert to work. I don't know
anything about it except what I've gleened from BOL but I'm not seeming to get anywhere...Hopefully there is some little (or big) problem with my

code

that someone can point out that may save me some time.

TIA

CBL
here is the table i'm trying to insert to.

/*Table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Description]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT (getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00

Here is the format file: (BOL hasn't given great examples so this is as
close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

Here is one of the errors I recieve

Server: Msg 4824, Level 16, State 1, Procedure pts_ImportTest, Line 32
Could not bulk insert. Invalid data type for column number 1 in format

file

'\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'.

Jul 20 '05 #5

me

Hmm looks interesting I'll take a look!

Thanks

CBL
"John Bell" <jb************@hotmail.com> wrote in message
news:A8*********************@news-text.cableinet.net...

Not even?

http://www.sqldts.com/default.aspx?210

John

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

Thanks much for all the help!
DTS is what I'm currently using and I would like to put this into a stored

procedure...

"me" <me@work.com> wrote in message
news:10*************@corp.supernews.com...

I'm also having problems getting the bulk insert to work. I don't know anything about it except what I've gleened from BOL but I'm not seeming

to get anywhere...Hopefully there is some little (or big) problem with

my

code

that someone can point out that may save me some time.

TIA

CBL
here is the table i'm trying to insert to.

/*Table*/
CREATE TABLE [ItemTest] (
[BarCode] [int] IDENTITY(1,1) NOT NULL ,
[FileNumber] [nvarchar] (20) NULL CONSTRAINT [DF_ItemTest_FileNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_ItemNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT

[DF_ItemTest_Description] DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNumber]

DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT ('0'), [ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_ImportDate] DEFAULT (getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,ItemNumber,Description,RoomNumber,Quant ity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERCONNECT CABLE",,1.00

Here is the format file: (BOL hasn't given great examples so this is as close as I've gotten)
8.0
14
1 char 0 20 "," 2 filenumber SQL_Latin1_General_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_General_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_General_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_General_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_General_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.ItemTest
FROM '\\ACCT_NT\SHARED\CBL\ITEMTEST.txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'
)

Here is one of the errors I recieve

Server: Msg 4824, Level 16, State 1, Procedure pts_ImportTest, Line 32
Could not bulk insert. Invalid data type for column number 1 in format

file

'\\ACCT_NT\SHARED\CBL\ITEMTEST.fmt'.

Jul 20 '05 #6