Parse Comma separated values in multiple columns to multiple columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello all,
I need your help and suggestions with the following scenario. I have the following file
ID | Fruit | # | Day |
1 | Apple, Banana, Mango | 1,2,3,4 | Mon, Tue |
2 | Apple, Banana, Mango, Kiwi | 1,2,3,4,5,6 | Tue, Wed, Thu, Fri |
3 | Apple, Banana | 1,2,3 | Tue, Wed, Mon, Sun |
4 | Apple, Banana, Mango, Plums | 1,2,3,4,5,6,7 | Tue, Wed, Mon, Sun |
5 | Apple, Banana, Mango, Strawberry | 2,3,4,5,6 | Mon, Tue, Wed, Thu, Sat |
My requirement is to split these values to different columns like below.
ID | Fruit_1 | Fruit_2 | Fruit_3 | Fruit_4 | #_1 | #_2 | #_3 | #_4 | #_5 | #_6 | Day_1 | Day_2 | Day_3 | Day_4 | Day_5 |
1 | Apple | Banana | Mango | 1 | 2 | 3 | 4 | Mon | Tue | ||||||
2 | Apple | Banana | Mango | Kiwi | 1 | 2 | 3 | 4 | 5 | 6 | Tue | Wed | Thu | Fri | |
3 | Apple | Banana | 1 | 2 | 3 | Tue | Wed | Mon | Sun | ||||||
4 | Apple | Banana | Mango | Plums | 1 | 2 | 3 | 4 | 5 | 6 | Tue | Wed | Mon | Sun | |
5 | Apple | Banana | Mango | Strawberry | 2 | 3 | 4 | 5 | 6 | Mon | Tue | Wed | Thu | Sat |
I understand we can use text-to-columns to get this output, but let's say I have 40 columns and I have to de-limit those values? Can't think of a solution. Can anyone help?
Solved! Go to Solution.
- Labels:
- Parse
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Praneeth
As is usually the case when dealing with multiple columns, the best strategy is to transpose all the columns you want to work on, perform the operation on the single pair of columns and Cross tab to get the final grid.
After transposing, split the value field to rows, and then number them sequentially within each group with the Multi row formula tool. Use a formula tool to generate the final header names. I replaced # with aaaaaa a since the Cross tab tool replaces non alpha and digit characters with underscore. After the Cross Tab, rename the aaaaaa_ columns to #_
Another fun thing the Cross tab tool does is order the columns alphabetically. You can produce the output in the original column order, but since you have varying numbers of items in the concatenated string, the logic will be quite complex.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks so much! works perfectly fine!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Pure Genius! That was exactly what I was looking for. Thanks.