Post reply

Can BCP generate ASCII Fixed Length file?

  • September 18, 2007 at 5:20 pm

    #118618

    Hi,

    Can BCP generate ASCII Fixed Length file?

    I need to produce ASCII,fixed length file

    with header and trailer from SQL Server 2000.

    I tried this:

    d:\>bcp CSTrust..vw_gh_2files_address out c:\bcp\gh_BENE_NUM.txt -c -t"," -Urobert -P197189807 -SCSSAN\TNV00PCDSQL

    and it worked. It did produce ASCII file.

    Now I'm wondering if it can generate Fixed Length file

    with column names.

    Or maybe I'm using the wrong tool for this?

    Thanks,

    Robert

  • September 18, 2007 at 5:35 pm

    #734436

    I recommend you don't post your password or server name anymore... just a thought

    Yes... there's lot's of ways to do this... you can use BCP with a format file, OSQL.exe in conjunction with a couple of SELECTs and casts, DTS... I personnally like the idea of OSQL... take a peek at BOL for info on OSQL and post back if you can't figure out how to do it...

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

  • September 18, 2007 at 8:32 pm

    #734460

    Don't worry Jeff.

    I changed my real username and password.

    So OSQL. I thought it's only a utility to execute SQL from a command line.

    I never thought about it as a transformation/loading tool.

    Will have a look at BOL tomorrow.

    Thanks,

    Robert

  • September 20, 2007 at 8:47 pm

    #735110

    Just don't forget the "-s" (column separator) flag... might want to set it to -s"" so there is no separator...

    And, yes, you could use BCP for a fixed file format... but you will need a BCP Format File to pull it off.  Once done, though, runs nasty fast...

    --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 4 posts - 1 through 3 (of 3 total)

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