472,796 Members | 2,206 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

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
5 4756
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: php newbie | last post by:
Hello, I am trying to load a simple tab-delimited data file to SQL Server. I created a format file to go with it, since the data file differs from the destination table in number of columns. ...
7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
1
by: gchavez | last post by:
I'm running MSSQL 2K on Win 2k and just upgraded SQL Server from SP3 to SP4 (that's when my problem started). I have a procedure that bulk inserts from a text file that is located on a Netware 4.11...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
0
by: ozkhillscovington | last post by:
We have sp's in place that do BULK INSERTS from txt files into the tables. This works fine, however they have asked us to add a field that identifies accounting ctr. The only thing that identifies...
0
by: rshivaraman | last post by:
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) --------------------------------- This is the query used to populate bill_tbl....
0
OuTCasT
by: OuTCasT | last post by:
I have a student management program, now i need to update an SQL database with a CSV file, now ive tested the BULK INSERT function and it works fine. i have a table names students, and a column...
0
by: bob laughland | last post by:
Hi All, I am using a combination of LINQ to SQL and bulk insert. In the process of performing 'one unit of work' I will be doing things like reading, and deleting records using LINQ to SQL and...
3
by: bob laughland | last post by:
Hi All, I am using a combination of LINQ to SQL and bulk insert. In the process of performing 'one unit of work' I will be doing things like reading, and deleting records using LINQ to SQL and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.