Post reply

Bulk Insert problem

  • April 1, 2010 at 9:53 am

    #100719

    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

    #1144530

    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

    #1145964

    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

    #1146043

    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