Trimming Additional , at the end in csv

  • Hi ,

    Some user csv's have an additional , at the end of each row. How can I trim it before the csv gets processed through the other process.

    I read something about a script component but I'm not well versed with the code to be written for that.

    Appreciate any help here.

    Thanks,

    V

  • venkyzrocks (2/13/2015)


    Hi ,

    Some user csv's have an additional , at the end of each row. How can I trim it before the csv gets processed through the other process.

    I read something about a script component but I'm not well versed with the code to be written for that.

    Appreciate any help here.

    Thanks,

    V

    SELECT SUBSTRING(CsvColumn,1,LEN(CsvColumn)-1)

    WHERE RIGHT(CsvColumn,1 = ','

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

  • Hi Jeff,

    Looks like a sql statement. What data flow transformation in SSIS would I use for this ?

    Thanks,

    V

  • I'm in the data flow task.. I'll write a sql task in the main script and try it out.

  • You don't need to remove the comma, you need to remove the column. This is done in the flat file source editor, assuming that you have an additional column that is not needed.

    Is this a fixed length, delimited or ragged right?

    Another option would be to use a derived column to use a derived column with something like this:

    SUBSTRING( ColumnName, 1, LEN(ColumnName) - 1 )

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • venkyzrocks (2/13/2015)


    Hi Jeff,

    Looks like a sql statement. What data flow transformation in SSIS would I use for this ?

    Thanks,

    V

    My apologies. I read the title of the post and paid no attention to the forum it was in.

    I can't help here because I don't use SSIS. I do all of my ETL in T-SQL, so we'll both have to rely on someone that actually knows how to use SSIS.

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

  • If you have an extra , (comma) at the end of rows in a 'csv' file. This means you have an extra column.

    Just update your file connection manager and remove that extra column.

    Or other way - use Derived column and remove extra , (comma)

    ____________________________________________________________

    AP
  • Yes sorry i didn't reply back to this thread earlier.

    The solution is to create a new connection but not uncheck the extra column in the flat file source.

    Thanks,

    V

Viewing 8 posts - 1 through 7 (of 7 total)

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