Post reply

Remove Pipe delimeters in Header and footer rows

  • July 13, 2012 at 9:30 am

    #272557

    Hi

    I am generating a flat file (.txt) using stored procedure with header and footer rows. Because the data rows in the middle has 18 columns, my header and footer also coming with 18 pipe delimeters. Is there a way to remove using script component. Please advise.

    example:

    My flat file generates likes below. I am looking for the way to remove pipe delimeters in the first row and last row after 2.

    20120711|APP.REPLINK|PROD||||||||||||||||

    1111|sam||||||I|apps|||N||N||Client|||

    1212|matt||||||I|apps|||N||N||Client|||

    2||||||||||||||||||

    Thanks

  • July 13, 2012 at 9:53 am

    #1512634

    There is no simple way of doing this.

    I can think of three possibilities:

    1) Output each row as a single column - you'll have to concatenate all the columns and add in the delimiters manually when building the column - do it with a derived column, or script component for more flexibility..

    2) Create three separate file: header, data, footer and then join them together using a DOS append.

    3) Add a script task after the dataflow which reads the file and edits the first and last rows.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 13, 2012 at 11:22 am

    #1512664

    Data9 (7/13/2012)

    Hi

    I am generating a flat file (.txt) using stored procedure with header and footer rows. Because the data rows in the middle has 18 columns, my header and footer also coming with 18 pipe delimeters. Is there a way to remove using script component. Please advise.

    example:

    My flat file generates likes below. I am looking for the way to remove pipe delimeters in the first row and last row after 2.

    20120711|APP.REPLINK|PROD||||||||||||||||

    1111|sam||||||I|apps|||N||N||Client|||

    1212|matt||||||I|apps|||N||N||Client|||

    2||||||||||||||||||

    Thanks

    You're combining mixed rows which will cause people who have to parse the data all sorts of headaches. If you want to include header and footer information, my recommendation would be to do it as a separate file.

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

  • July 13, 2012 at 11:53 am

    #1512672

    Thanks All, Your inputs helped me. Yes I am in the mode of developing three files and club. I am not aware of DOS method. The idea "DOS append " helped me. Once again Thanks.

  • July 13, 2012 at 5:03 pm

    #1512745

    Data9 (7/13/2012)

    Thanks All, Your inputs helped me. Yes I am in the mode of developing three files and club. I am not aware of DOS method. The idea "DOS append " helped me. Once again Thanks.

    Again, the problem is going to happen on the other end because there is no DOS "unappend". I don't know if they'll buy it but I recomend NOT combining header, data, and footer into a single file.

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

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