Post reply

Unable to load text data in using BULK INsert with XML Format file

  • July 8, 2019 at 4:45 pm

    #3660834

    I have the following table

     

    IF OBJECT_ID('[TempDB]..[#LoadData]') IS NOT NULL DROP TABLE [dbo].[#LoadData];

    CREATE TABLE [dbo].[#LoadData]

    (

    [RTOPartyID] INT NULL

    , [Prefix] NVARCHAR(255) NULL

    , [FirstNames] NVARCHAR(255) NULL

    , [LastName] NVARCHAR(255) NULL

    , [Suffix] NVARCHAR(255) NULL

    , [PartyType] NVARCHAR(255) NULL

    , [PlaceOfBirth] NVARCHAR(255) NULL

    , [TaxIdentificationNumber] NVARCHAR(50) NULL

    , [CrownServant] BIT NULL

    , [DateOfBirth] DATETIME NULL

    , [NINO] NVARCHAR(50) NULL

    , [Crest] BIT NULL

    , [stsPartyID] INT NULL

    , [AddressID] INT NULL

    , [Ignore] INT NULL

    , [OverrideNameCheck] BIT NULL -- v1.02 Added

    , [RowID] INT IDENTITY(1, 1)

    )

    ;

    Im using the following command to load into ths table

     

    BULK INSERT [dbo].[#LoadData]

    FROM '..\LoadData3.txt'

    WITH (

    FORMATFILE = '..RTO_FormatFile_Party.xml'

    , FIRSTROW = 2

    , MAXERRORS = 0

    )

     

    But i get the following error:

     

    Msg 4832, Level 16, State 1, Line 49

    Bulk load: An unexpected end of file was encountered in the data file.

    Msg 7399, Level 16, State 1, Line 49

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 49

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

     

    Does anyone know what im getting this?

     

    The data file  that Im trying to inset and XML format file are attached

     

     

    Attachments:
    You must be logged in to view attached files.

  • July 8, 2019 at 5:09 pm

    #3660871

    Your format specifies 16 fields, but the data fields only has 15 fields. The format file has a field OverrideSIRIUSPartyNameCheck, which appears to be missing in the data file. When I remove that field from the format file I am able to load the file.

    Also, the last line in the data file does not have a terminating \r\n. I don't remember what BULK INSERT thinks about this. When I tested, I first added the missing line ending, as this was the first thing I observed.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply