SSIS How to split a single string into multiple columns using Derived Column
-
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.
Thanks
-
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
-
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.
-
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:
-
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.
-
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"
-
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) -
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