How to handle/strip out Quotes around string columns that can be null when using bcp to import csv files?

From: David E Herbst (dherbstemail-ng_at_yahoo.com)
Date: 08/25/04

  • Next message: SqlJunkies User: "Re: SQL Client Tools on 2003 Web Edition" 
    Date: 25 Aug 2004 12:24:46 -0700

    I am trying to import data from third party CSV files that contain
    quotes around string ([varchar] and [char]) columns. To allow BCP
    (SQL 2000 version) to handle/strip out the quotes I am using format
    files that specify quotes as part of the column delimiters. I can't
    just preprocess the text to remove the quotes because the string
    columns may include commas.

    Example format file:
    1 SQLCHAR 0 0 ",\"" 1 c1 ""
    2 SQLCHAR 0 0 "\",\"" 2 c2 ""
    3 SQLCHAR 0 0 "\"\n" 3 c3 ""

    This works pretty well for the following text:
    111,"Winken,Blinken,Nod","aaa"

    The problem is that if one of the string columns values is null\empty
    [,,] bcp doesn't find the delimiter since it's expecting a quote-comma
    [",] delimiter. E.g.:
    111,,"aaa"

    This results in a bcp error due to not finding the quote-comma
    delimiter! Is there something I can specify in the format file to
    make the quote an optional part of the delimiter? Can I specify an
    alternate delimiter for a column? Is there a better way to do this?
    This is part of a larger automated process located on a different
    server than the DB so if possible I would like to continue to use bcp…

    Thanks,
    David

  • Next message: SqlJunkies User: "Re: SQL Client Tools on 2003 Web Edition"

    Relevant Pages
    Loading