Post reply

using BCP to import data where first record is different

  • December 6, 2015 at 12:17 am

    #306865

    Hi there,

    Basically I have a comma delimited file (with 5 fields) that I need to import into SQL Server 2008 using BCP. The first line however is different from the rest in that it only contains one column, this being a record count of the number of records on the file. I intend to use this record count to ensure that the file contains the correct number of records once the import is complete. I can import it in fine (with the help of a format file) when the record count row isn't there, but when it is it (quite expectedly) falls over, even though I remove the reference to the format file.

    Does anyone know the best way of of importing in both the row with the row count and all the records afterwards please?

    Thanks very much

  • December 6, 2015 at 12:51 pm

    #1844254

    With only 5 "fields", I'll typically import each row as a whole row and split it using DelimitedSplit8K ( see the "Resources" section at the bottom of the article located at http://www.sqlservercentral.com/articles/Tally+Table/72993/ ) and repivot the data to get what I need. Depending on the nature of the beast, I might use dynamic SQL with OPENROWSET and the ACE drivers, which is usually slower but gets the job done.

    What I normally do is not tolerate inconsistent data and work with the folks providing the data to clean things up a bit. Of course, some folks won't budge so we're left with options like the above.

    Another option might be to write something like a PowerShell routine that makes a copy of the file while stripping off the header.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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