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

  • sabarishbabu

    Old Hand

    Points: 346

    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

  • frederico_fonseca

    SSChampion

    Points: 14686

    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

  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • sabarishbabu

    Old Hand

    Points: 346

    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 1 month, 1 week ago by  sabarishbabu.
  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • sabarishbabu

    Old Hand

    Points: 346

    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 1 month, 1 week ago by  sabarishbabu.
  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • sabarishbabu

    Old Hand

    Points: 346

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

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

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

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