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:10cupochcrn888d@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'.
>
>