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...Hope fully 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_Fil eNumber]
DEFAULT (''),
[ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Ite mNumber]
DEFAULT (''),
[Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Des cription]
DEFAULT (''),
[RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNum ber] DEFAULT
(''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Qua ntity] DEFAULT ('0'),
[ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_Imp ortDate] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode]
) ON [PRIMARY]
) ON [PRIMARY]
/*Table*/

Here is the data file: (2 records)
FileNumber,Item Number,Descript ion,RoomNumber, Quantity
"26458CBL-1","01","(4) LEVELERS",,1.00
"26458CBL-1","01","INTERC ONNECT 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_Gene ral_CP1_CI_AS
2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS

and finally here is the Bulk Insert T-SQL command:
BULK INSERT SHIPPING.DBO.It emTest
FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
WITH
(
--DATAFILETYPE = 'native',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = '\\ACCT_NT\SHAR ED\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\SHAR ED\CBL\ITEMTEST .fmt'.

[Erland Sommarskog]

Re: Bulk Insert Problems

me (me@work.com) writes:[color=blue]
> 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...Hope fully there is some little (or big) problem with
> my code that someone can point out that may save me some time.[/color]

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.)
[color=blue]
> Here is the data file: (2 records)
> FileNumber,Item Number,Descript ion,RoomNumber, Quantity
> "26458CBL-1","01","(4) LEVELERS",,1.00
> "26458CBL-1","01","INTERC ONNECT CABLE",,1.00[/color]

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.
[color=blue]
> 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_Gene ral_CP1_CI_AS
> 2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
> 4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS[/color]

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.
[color=blue]
> and finally here is the Bulk Insert T-SQL command:
> BULK INSERT SHIPPING.DBO.It emTest
> FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
> WITH
> (
> --DATAFILETYPE = 'native',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n',
> FORMATFILE = '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .fmt'
> )[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: Bulk Insert Problems

Hi

You could try DTS?

John

"me" <me@work.com> wrote in message
news:10cupochcr n888d@corp.supe rnews.com...[color=blue]
> 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...Hope fully there is some little (or big) problem with my[/color]
code[color=blue]
> 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_Fil eNumber]
> DEFAULT (''),
> [ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Ite mNumber]
> DEFAULT (''),
> [Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Des cription]
> DEFAULT (''),
> [RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNum ber] DEFAULT
> (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Qua ntity] DEFAULT ('0'),
> [ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_Imp ortDate] DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> /*Table*/
>
> Here is the data file: (2 records)
> FileNumber,Item Number,Descript ion,RoomNumber, Quantity
> "26458CBL-1","01","(4) LEVELERS",,1.00
> "26458CBL-1","01","INTERC ONNECT 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_Gene ral_CP1_CI_AS
> 2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
> 4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS
>
> and finally here is the Bulk Insert T-SQL command:
> BULK INSERT SHIPPING.DBO.It emTest
> FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
> WITH
> (
> --DATAFILETYPE = 'native',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n',
> FORMATFILE = '\\ACCT_NT\SHAR ED\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[/color]
file[color=blue]
> '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .fmt'.
>
>[/color]

[me]

Re: Bulk Insert Problems

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:10cupochcr n888d@corp.supe rnews.com...[color=blue]
> 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...Hope fully there is some little (or big) problem with my[/color]
code[color=blue]
> 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_Fil eNumber]
> DEFAULT (''),
> [ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Ite mNumber]
> DEFAULT (''),
> [Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Des cription]
> DEFAULT (''),
> [RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNum ber] DEFAULT
> (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Qua ntity] DEFAULT ('0'),
> [ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_Imp ortDate] DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> /*Table*/
>
> Here is the data file: (2 records)
> FileNumber,Item Number,Descript ion,RoomNumber, Quantity
> "26458CBL-1","01","(4) LEVELERS",,1.00
> "26458CBL-1","01","INTERC ONNECT 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_Gene ral_CP1_CI_AS
> 2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
> 4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
> 5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS
>
> and finally here is the Bulk Insert T-SQL command:
> BULK INSERT SHIPPING.DBO.It emTest
> FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
> WITH
> (
> --DATAFILETYPE = 'native',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n',
> FORMATFILE = '\\ACCT_NT\SHAR ED\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[/color]
file[color=blue]
> '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .fmt'.
>
>[/color]

[John Bell]

Re: Bulk Insert Problems

Not even?

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

John

"me" <me@work.com> wrote in message
news:10d0qofmq7 g7q1e@corp.supe rnews.com...[color=blue]
> 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:10cupochcr n888d@corp.supe rnews.com...[color=green]
> > 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[/color][/color]
to[color=blue][color=green]
> > get anywhere...Hope fully there is some little (or big) problem with my[/color]
> code[color=green]
> > 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_Fil eNumber]
> > DEFAULT (''),
> > [ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Ite mNumber]
> > DEFAULT (''),
> > [Description] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Des cription]
> > DEFAULT (''),
> > [RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNum ber][/color][/color]
DEFAULT[color=blue][color=green]
> > (''),
> > [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Qua ntity] DEFAULT ('0'),
> > [ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_Imp ortDate][/color][/color]
DEFAULT[color=blue][color=green]
> > (getdate()),
> > CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> > (
> > [BarCode]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > /*Table*/
> >
> > Here is the data file: (2 records)
> > FileNumber,Item Number,Descript ion,RoomNumber, Quantity
> > "26458CBL-1","01","(4) LEVELERS",,1.00
> > "26458CBL-1","01","INTERC ONNECT 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_Gene ral_CP1_CI_AS
> > 2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
> > 3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
> > 4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
> > 5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS
> >
> > and finally here is the Bulk Insert T-SQL command:
> > BULK INSERT SHIPPING.DBO.It emTest
> > FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
> > WITH
> > (
> > --DATAFILETYPE = 'native',
> > FIELDTERMINATOR = ',',
> > ROWTERMINATOR = '\n',
> > FORMATFILE = '\\ACCT_NT\SHAR ED\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[/color]
> file[color=green]
> > '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .fmt'.
> >
> >[/color]
>
>[/color]

[me]

Re: Bulk Insert Problems

Hmm looks interesting I'll take a look!

Thanks

CBL


"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:A8_zc.1607 $0H5.13988977@n ews-text.cableinet. net...[color=blue]
> Not even?
>
> http://www.sqldts.com/default.aspx?210
>
> John
>
> "me" <me@work.com> wrote in message
> news:10d0qofmq7 g7q1e@corp.supe rnews.com...[color=green]
> > Thanks much for all the help!
> > DTS is what I'm currently using and I would like to put this into a[/color][/color]
stored[color=blue][color=green]
> > procedure...
> >
> > "me" <me@work.com> wrote in message
> > news:10cupochcr n888d@corp.supe rnews.com...[color=darkred]
> > > I'm also having problems getting the bulk insert to work. I don't[/color][/color][/color]
know[color=blue][color=green][color=darkred]
> > > anything about it except what I've gleened from BOL but I'm not[/color][/color][/color]
seeming[color=blue]
> to[color=green][color=darkred]
> > > get anywhere...Hope fully there is some little (or big) problem with[/color][/color][/color]
my[color=blue][color=green]
> > code[color=darkred]
> > > 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_Fil eNumber]
> > > DEFAULT (''),
> > > [ItemNumber] [nvarchar] (50) NULL CONSTRAINT [DF_ItemTest_Ite mNumber]
> > > DEFAULT (''),
> > > [Description] [nvarchar] (50) NULL CONSTRAINT[/color][/color][/color]
[DF_ItemTest_Des cription][color=blue][color=green][color=darkred]
> > > DEFAULT (''),
> > > [RoomNumber] [nvarchar] (50) NULL CONSTRAINT [DF_Item_RoomNum ber][/color][/color]
> DEFAULT[color=green][color=darkred]
> > > (''),
> > > [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Qua ntity] DEFAULT[/color][/color][/color]
('0'),[color=blue][color=green][color=darkred]
> > > [ImportDate] [datetime] NULL CONSTRAINT [DF_ItemTest_Imp ortDate][/color][/color]
> DEFAULT[color=green][color=darkred]
> > > (getdate()),
> > > CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> > > (
> > > [BarCode]
> > > ) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > /*Table*/
> > >
> > > Here is the data file: (2 records)
> > > FileNumber,Item Number,Descript ion,RoomNumber, Quantity
> > > "26458CBL-1","01","(4) LEVELERS",,1.00
> > > "26458CBL-1","01","INTERC ONNECT CABLE",,1.00
> > >
> > > Here is the format file: (BOL hasn't given great examples so this is[/color][/color][/color]
as[color=blue][color=green][color=darkred]
> > > close as I've gotten)
> > > 8.0
> > > 14
> > > 1 char 0 20 "," 2 filenumber SQL_Latin1_Gene ral_CP1_CI_AS
> > > 2 char 0 50 "," 3 itemnumber SQL_Latin1_Gene ral_CP1_CI_AS
> > > 3 char 0 50 "," 4 description SQL_Latin1_Gene ral_CP1_CI_AS
> > > 4 char 0 50 "," 5 roomnumber SQL_Latin1_Gene ral_CP1_CI_AS
> > > 5 char 0 10 "," 6 quantity SQL_Latin1_Gene ral_CP1_CI_AS
> > >
> > > and finally here is the Bulk Insert T-SQL command:
> > > BULK INSERT SHIPPING.DBO.It emTest
> > > FROM '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .txt'
> > > WITH
> > > (
> > > --DATAFILETYPE = 'native',
> > > FIELDTERMINATOR = ',',
> > > ROWTERMINATOR = '\n',
> > > FORMATFILE = '\\ACCT_NT\SHAR ED\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[/color]
> > file[color=darkred]
> > > '\\ACCT_NT\SHAR ED\CBL\ITEMTEST .fmt'.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]