Problem with Format File And Bulk Insert

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)

Problem with Format File And Bulk Insert

Author

Topic

Davecg2
Starting Member

10 Posts

Posted - 2008-12-05 : 16:23:39

Im using a Bulk Insert Stored Procedure to import a textfile, i can get it into the table without a format file but it imports the " text qualifiers, so frim what i read i need to use a format file. So i tried and keep getting an error i cant seem to get past. The import file is Pipe Delimited with " text qualifiers.

Heres the table (Already exists as a permanent table in SQL DB)


CREATE TABLE [dbo].[LabelFileImport2](
	[SupplierID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SupplierName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PONumber] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[POLine] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PORel] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DueDate] [smalldatetime] NULL,
	[Item] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[VendorItem] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Price] [money] NULL,
	[QtyOrdered] [int] NULL,
	[QtyReceived] [int] NULL,
	[LastReceived] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

And here is the format file:


9.0
12
1	SQLCHAR	0	25	"|"		1	SupplierID	SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	0	100	"|"		2	SupplierName	SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	0	100	"|"		3	PONumber	SQL_Latin1_General_CP1_CI_AS
4	SQLCHAR	0	100	"|"		4	POLine	SQL_Latin1_General_CP1_CI_AS
5	SQLCHAR	0	100	"|"		5	PORel	SQL_Latin1_General_CP1_CI_AS
6	SQLCHAR	0	25	"|"		6	DueDate	SQL_Latin1_General_CP1_CI_AS
7	SQLCHAR	0	100	"|"		7	Item	SQL_Latin1_General_CP1_CI_AS
8	SQLCHAR	0	100	"|"		8	VendorItem	SQL_Latin1_General_CP1_CI_AS
9	SQLCHAR	0	25	"|"		9	Price	SQL_Latin1_General_CP1_CI_AS
10	SQLCHAR	0	25	"|"		10	QtyOrdered	SQL_Latin1_General_CP1_CI_AS
11	SQLCHAR	0	25	"|"		11	QtyReceived	SQL_Latin1_General_CP1_CI_AS
12	SQLCHAR	0	10	"\r\n"	12	LastReceived	SQL_Latin1_General_CP1_CI_AS

And heres the error i keep getting.
Cannot bulk load because the file "C:\Test\LabelFileImportFormatFile.fmt" could not be read. Operating system error code (null).

I know it seems like a permission issue, but before i realized what i was doing i had "/t" for the delimiters and got and error saying invalid column number in the format file

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-05 : 16:44:54

Does the format file exist on the database server? BULK INSERT runs from the database server's perspective and not from your client machine's perspective.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Davecg2
Starting Member

10 Posts

Posted - 2008-12-05 : 16:53:14

quote:
Originally posted by tkizer

Does the format file exist on the database server? BULK INSERT runs from the database server's perspective and not from your client machine's perspective.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Yes i put it on the SQL Server Machine to eliminate the possibility of running into permission and unc naming errors.

Heres the Stored Proc im using


ALTER PROCEDURE [dbo].[Cust_Label_LabelFileImportProcess] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for procedure here

	BULK INSERT LabelFileImport2
	FROM '\\webserv1\Website\images\FTP\Vendor\LabelFile.txt'
	WITH (FORMATFILE = 'C:\Test\LabelFileImportFormatFile.fmt')
		
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 05:25:32

Does the account running BULK INSERT have access to folder C:\Test where format file is stored?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-07 : 13:35:26

Try putting the format file in the same location as the import file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Davecg2
Starting Member

10 Posts

Posted - 2008-12-08 : 12:23:16

I gave the everyone group full rights to the c:\test\ directory so i dont think its actually an issue with the OS permissions like the error message would make you think. is there anything wrong with the way i did the format file?

Another weird note: when i change the path to the text file im importing to an imvalid path it allows me to save the stored proc without an error, however if i have valid path names it wont allow me to save the procedure? im guessing theres a problem with the format file since ive been able to import the text file when i dont use a format file, however it also brings in a bunch of " i cant have so i need the format file to work to get rid of the quoates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources