Text to Columns issue
- 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
Hi All,
I tried using text to columns to separate a text data in a cell by a comma delimiter but its not catching all the comas to delimit. Is there away around it? Why is Alteryx missing the commas?
Thank you for the help in advanced.
Solved! Go to Solution.
- Labels:
- Parse
- Tips and Tricks
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you post the relevant section of your workflow including sample data?
Or at least post the configuration of your Text to Columns tool.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have about 73K rows the varies from similar to this address this data
IP
12545:4edT,000.16.99.000,000.001.000,12545:4edT,000.16.99.000
12545:4edT,000.16.99.000,03.001.000.12,03.001.000.12
12545:4edT,000.16.99.000,000.001.000.1,000.16.99.000
The result should be to split all the data into separate columns but it seems to leave some of the data how looks it below
ip1
12545:4edT,0
12545:4edT,
12545:4edT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I figured it out I was using the all columns but the last column contained the left over since text to column was only splitting into 3 columns. I guess i need to ensure to increase the numbers of columns to avoid the last column containing the left overs.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry its still happening...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
We can't help without data examples. When you write "its still happening", please provide examples where it is still happening.
When I use Text to Columns, I set the number of columns to 99, then use the Data Cleansing tool to drop the null columns.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I wish to share examples but my data is sensitive to share. I did find some random IP online and I put them together similar how it looks to my data
.
Hostname IP Date OS
Test1 192.0. 2.146,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.107,2005:0db8:85a3:0000:0000:8a2e:0370:7334 5/5/2022 Windows 98
Test2 2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105 5/4/2022 Windows 2005
Test3 172.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105,182.168.108.105 5/3/2022 Windows 95
Test4 172.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105,182.168.108.108,182.158.108.105 5/4/2022 Windows 96
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In addition to what @ChrisTX said above about increasing to 99 columns, you could do a split to rows like this when there could be different number of outputs (commas).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry but for some reason I can't post table due html error in this site but if you see the picture of my table i posted that's how my data is setup. splitting into rows scares me because how will I get them get them back in alignment with the other corresponding columns.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It looks like this seem to do the trick. Can you explain more why this work? I want to take make some notes to avoid in the future.