Post reply

BCP Utility for Data Copy

  • October 11, 2007 at 2:28 pm

    #119009

    Hi,

    I am trying to use BCP Utility to copy data from flat file to SQL Server 2005 server table. The flat file has "comma" as field and "next line" as row delimiter. In addition to these two delimiters, my flat file has also double quotes to the data). Now I don't know which option should be specified in the BCP utility to ignore douqble quotes in the data file. example of my data

    "City","ST","ZIP","A/C","FIPS","County","Pref?","T/Z","DST?","Lat","Long","MSA","PMSA","Abbreviation","MA","Type"

    "1000 Palms","CA","92274","760","06065","Riverside","N","PST","Y","33.0930",

    "-116.0605",,"6780",,"049",

    "1000 Palms","CA","92276","760","06065","Riverside","N","PST","Y","33.8219",

    "-116.3833",,"6780",,"049",

    one solution is to replace these double quotes in the flat file then import to table but this is a huge file and if I try to replace it then it hung up the file every time. The next solution is import data with double quotes to the database then use replace function in SQL Server. However I am looking for some decent solution to ignore these double quotes while importing it to SQL Server.

    Any idea sharing is appreciated.

  • October 11, 2007 at 7:41 pm

    #741422

    Please see the following for how to resolve this problem...

    http://qa.sqlservercentral.com/Forums/Topic296166-8-1.aspx#BM296961

    --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)
    Intro to Tally Tables and Functions

  • October 12, 2007 at 1:09 pm

    #741715

    Jeff,

    I didn't see any solution in the link provided.

    Bhushan

  • October 12, 2007 at 5:48 pm

    #741757

    You don't? :blink: There's an example of what the input file looks like, what the table looks like, what the BCP format file looks like, and what the necessary command looks like. I've given you the whole shootin' match on a silver platter with paragraphs of explanations for each... Books Online should do so well! All you have to do is some minor mods for your particular problem... :Whistling:

    --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)
    Intro to Tally Tables and Functions

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

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