April 1, 2010 at 9:53 am
So, this one has me entirely baffled.
I have a process which is bulk loading data files to a table, very simple code. I have run the code several dozen times, on multiple files, and it works just fine. Today though, the data file for one of these fails to load, with an unexpected EOF error. I have isolated it down to a specific record causing the issue. However, I can find no difference at all between this bad record and a loadable record.
I have..
Used Enterprise Manager to import the file, and this works.
Removed record from data file, file then loads.
Put bad record alone in data file, file fails to load.
Parsed out each character as ascii numbers for each character between a good and bad record. The records are identical except in a series code, which should be as it is.
I am out of tricks and ideas to try to identify the issue. I am hoping someone here will find something I am missing on this.
Attached is a script to create the table, along with the bulk insert statement, file Code_Test.txt
The data file, with the bad record, and one good record is attached as BH_220.txt.
I clearly am missing something here... :crazy:
April 1, 2010 at 4:44 pm
You need to make sure number of columns in the table matches number of columns in the file.
This works perfectly with the file you provided:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[File220]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[File220]
CREATE TABLE [dbo].[File220] (
[A] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[D] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[E] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[F] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[G] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL --,
-- [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [J] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [K] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [L] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [M] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
BULK INSERT Northwind.dbo.[File220]
FROM 'C:\BH_220.txt'
WITH
(
FIELDTERMINATOR = '^',
ROWTERMINATOR = '~')
Or - you can specify subset of table columns to be used in the BULK INSERT in a format file.
_____________
Code for TallyGenerator
April 5, 2010 at 1:06 pm
Thank you! I knew it was something simple I was missing.
The vendor apparently changed the file layout on me.
April 5, 2010 at 3:28 pm
You welcome.
And thank you for providing perfect coding and data samples.
It made it really easy to help you.
🙂
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply