Post reply

SSIS How to split a single string into multiple columns using Derived Column

  • June 3, 2020 at 12:09 am

    Go to Answer

    #3759649

    Hello,

    In my source Column having comma separated with text qualifier(“), I would need derived column from source column like below. Please suggest the best SSIS Expression to archive this .

    Note : I am trying to derived 50+ Columns and using the string TOKEN(REPLACE(SourceRecord,"\"",""),",",1), it wont work if the Column value has comma.

    Annotation1

    Annotation2

    Thanks

  • June 3, 2020 at 12:40 am

    #3759678

    if your source is a file then just split it on the file definition by setting up the file with comma delimited as column separator, quoted

  • June 3, 2020 at 1:09 am

    Answer

    #3759691

    This can be done, if you think laterally: make " your delimiter and your formulas become:

    TOKEN( SourceRecord,"\"",1)
    TOKEN( SourceRecord,"\"",3)
    TOKEN( SourceRecord,"\"",5)
    TOKEN( SourceRecord,"\"",7)
    TOKEN( SourceRecord,"\"",9)

    Having said that, Frederico's suggestion is better, if applicable.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2020 at 1:01 pm

    #3760092

    Thanks you Phill !!

    Your solution is working as expected, however any of the column value has empty, I did not get the proper value in corresponding output columns.

    Source Column/ OutPut Coulumn:

    Annotation4

     

     

    • This reply was modified 4 years, 4 months ago by  sabarishbabu.

  • June 3, 2020 at 1:15 pm

    #3760101

    My solution depends on there being the same number of quotation marks (") in every string which is split.

    If an 'empty' column appears as "", the solution should still work. But if the quotation marks are not there, it will not.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2020 at 1:32 pm

    #3760102

    Thanks for your quick reply !! Yes the  quotation marks are there in the empty field too, even its not working for if any empty column value.

    Source Data

    "A","","","","A,B,C,D"

    "X","X1,X1","20","+","X,B,C,D"

    "Y","Y1,B1","100","+","Y,B,C,D"

    "Z","A1","","","Z"

    • This reply was modified 4 years, 4 months ago by  sabarishbabu.

  • June 3, 2020 at 1:58 pm

    #3760104

    Well, that's unexpected ... but I have a solution for you. If you first REPLACE all instances of "" with " " (a single space) before using TOKEN, things seem to work (though you will have to decide what to do with the space ... set it back to NULL, perhaps).

    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",1) 
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",3) 
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",5) 
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",7) 
    TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",9)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2020 at 9:36 pm

    #3760257

    Thank you Phill !! Its working as expected I just added trim function 🙂

    TRIM(TOKEN(REPLACE(SourceRecord,"\"\"","\" \""),"\"",1))}

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

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