Home Forums SQL Server 2008 T-SQL (SS2K8) Bulk insert with format file - handling quotation mark text qualifiers in header row RE: Bulk insert with format file - handling quotation mark text qualifiers in header row

  • October 10, 2013 at 8:23 am

    #1657581

    caspersql (10/10/2013)

    Thanks Jeff, I like this idea because I'll be generating the format file using BCP and dynamic SQL. I read here http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e996cc4-466a-4569-912a-8782beba6806/bcp-and-dynamic-sql?forum=transactsql that there's a 4000 character limit for that so the less text in my format file the better!

    When I use your suggested version, I still get a single text qualifier left in the last column of the last row of my data. I guess there's no /r/ n at the end of that line because there are no further rows in the file. Do you experience that and if so, do you just use string manipulation afterwards to remove the offending qualifier?

    Ugh!... Yeah... it's probably because there's no CRLF at the end of the file. That's the age old problem of the sender of the data not using a consistant format. I'm actually a bit surprised it didn't cause an error.

    And, yes... some "post import" validation is essential. That's why I always (and I don't say that word very often in anything having to do with computers) load the data into a staging table instead of into final tables. That's where you can check and correct the final column for the common error that you're running across.

    --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)