Post reply

URGENT - How to skip header records in bulk insert command using format file

  • March 11, 2005 at 8:12 am

    #60241

    Hi Gurus,

    Platform : Sql server 2000 sp3 on Win 2000 server.

    I am using bulk insert command with a format file.In format file i didnt find option to skip top 2 records, at the same time if i use FIRSTROW = 3 , it wont skip first 2 records. I am using the following command -

    BULK INSERT pubs..bitmap FROM 'c:\test.doc'

    WITH (

    FIRSTROW = 3,

    FORMATFILE = 'c:\Bcp.fmt'

    )

    My questions are -

    1. Can we specify in format file itself FIRSTROW information ? If yes , How ?

    2. Any other alternate way to achieve the same.

    TIA,

    Sheilesh

     

  • March 11, 2005 at 8:40 am

    #546648

    I would expect that specifying a format file overrides any other parameter you give to BULK INSERT, since you're essentially telling it to get all its parameters from the format file.

    An alternative is to use a DTS package with a text file data source. The DTS UI for the transformation into SQL allows you to select header rows to skip.

     

     

  • March 11, 2005 at 1:50 pm

    #546732

    I have done something similar, only using a format file and lastrow:

    BULK INSERT FROM 'FileIn'

    WITH (FORMATFILE = 'FormatFile', LASTROW = '99')

    Format files don't know about first or last rows, so that's probably not a factor.  What you have oughta work.  But here's a thought:  in your example, you specify FIRSTROW before you specify the formatfile parameter.  What if you swap them so that format file is first, then FIRSTROW?  It shouldn't make a difference, but...

    There is no "i" in team, but idiot has two.

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

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