How to make a record for each comma separated field
Moderators: chulett, rschirm, roy
How to make a record for each comma separated field
Hi, thanks for reading
Need to split a comma delimited record into rows.
If I have
field1,field2,field3
How can I end up with 3 records?
field1
field2
field3
Need to split a comma delimited record into rows.
If I have
field1,field2,field3
How can I end up with 3 records?
field1
field2
field3
Sorry it's a comma delimted field not a comma delimited record.
we won't know the number of comma seperated fields. We just know that we have a comma delimited field and each comma delimted value within the field should be parsed into it's own record.
field1,field,field3 etc...
will need to be
field1
field2
field3
etc...
we won't know the number of comma seperated fields. We just know that we have a comma delimited field and each comma delimted value within the field should be parsed into it's own record.
field1,field,field3 etc...
will need to be
field1
field2
field3
etc...
No worries.
Since there's not a fixed number of columns, some possibilities that come to mind:
- If you're using IS 8.5, a transformer could probably loop through the field and create the multiple records
- 8.1 or earlier: If there's a reasonable max number of columns in that field, you could use a transformer with constrained multiple outputs and funnel those together. Ugly but it works
- A BuildOp could handle this easily
Each of these actually do the pivot for you, so no need for a pivot stage.
Regards,
Since there's not a fixed number of columns, some possibilities that come to mind:
- If you're using IS 8.5, a transformer could probably loop through the field and create the multiple records
- 8.1 or earlier: If there's a reasonable max number of columns in that field, you could use a transformer with constrained multiple outputs and funnel those together. Ugly but it works
- A BuildOp could handle this easily
Each of these actually do the pivot for you, so no need for a pivot stage.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
You could also continue down James' original path as long as you know what the max occurance would be. You then always generate that max number of fields, pivot out to the maximum number of records and then filter out an unneeded / null records post pivot.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I considered that as well...easy enough with the transformer. My reservation with it is the overhead of the extra records to be dropped. Depends on the input volume and the ratio of average population to max population.
If this is a one-time job, the overhead may be worth the easier design.
If this is a one-time job, the overhead may be worth the easier design.
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
The input record conatins one field and it would be a varchar 255 field. The one input field contains value1,value2,value3 etc.. we will never know ahead of time how many comma delimited values the input field contains. So each value in the field would become it's own record.
value1
value2
value3
etc..
value1
value2
value3
etc..