none
Remove CRLF

    Question

  • How to remove {CR}{LF} and Tab {t} characters from source data?

    - per Script component ?
    - per Derived Column Transformation (Replace)?


    I have built a SSIS package that reads in data from a database source (ODBC) into a flat file destination (csv-file).

    Problem: Some Text fields have undesirable characters  (CR LF and Tab).


    SSIS-Package  | Data Flow:

    - DataReader-Source(ODBC)
    - Flatfile Destination (CSV-File)

     

    My first attempt: use the Derived Column Transformation and the REPLACE function.

    Replace(Text-Column,"\r\n","")

    \r = CR
    \n = CR + LF


    The function doesn't work (with "\r\n") !

    Wednesday, January 19, 2011 1:08 PM

Answers

  • replace((replace((replace(column,"\n","")),"\r","")),"\t","")  -> doesn't work


    Are you sure you have a tab separator or is that you have fixed number of spaces??

    Could you try replace((replace((replace(column,"\t","")),"\r","")),"\n","")


    Sudeep's Domain
    • Marked as answer by Andy07 Thursday, January 20, 2011 7:29 AM
    Wednesday, January 19, 2011 5:26 PM

All replies

  • try this:  replace((replace(column,"\n","")),"\r","")
    Sudeep's Domain
    • Proposed as answer by Jon Kleinhans Wednesday, January 19, 2011 10:26 PM
    Wednesday, January 19, 2011 1:21 PM
  • it works!  :-)

    replace((replace(column,"\n","")),"\r","")

    + remove TAB \t  ?

    replace((replace((replace(column,"\n","")),"\r","")),"\t","")  -> doesn't work


    Wednesday, January 19, 2011 2:09 PM
  • If that "string literal" doesn't work, then you can go the Hex route by specifying the Hex Unicode of the character you're removing using "\xnnnn".  See Literals (SSIS).


    Todd McDermid's Blog Talk to me now on
    Wednesday, January 19, 2011 5:21 PM
  • replace((replace((replace(column,"\n","")),"\r","")),"\t","")  -> doesn't work


    Are you sure you have a tab separator or is that you have fixed number of spaces??

    Could you try replace((replace((replace(column,"\t","")),"\r","")),"\n","")


    Sudeep's Domain
    • Marked as answer by Andy07 Thursday, January 20, 2011 7:29 AM
    Wednesday, January 19, 2011 5:26 PM
  • It works with  replace((replace((replace(column,"\t","")),"\r","")),"\n","") 

    I have  tab separators in the text (no fixed number of spaces).

    Thanx

    Thursday, January 20, 2011 7:29 AM