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.
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
The tool is instructed to only find and split out the first 3 comma delimiters. All commas after that are not handled and left in the last split column (or dropped all together) depending on setup. By increasing the number of columns to split into, you're telling the tool to only split out the first X comma delimiters. If you know there will always be say 7 commas, then you set it and forget it. But if it will always vary and you don't know what the max would be, set a higher number and then delete all the null columns afterward.
Not sure if this is what you're looking for in terms of how it works.
Can you post the relevant section of your workflow including sample data?
Or at least post the configuration of your Text to Columns tool.
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
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.
Sorry its still happening...
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
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).
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.
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.
@rmartinez4 Have you tried increasing the columns to 99 and then using a Data Cleanse tool as @ChrisTX mentioned?
Yes, That works why is that the case?
Yes, your answer makes sense. Thank you for the explanation.
In the Text to Columns tool, the value in Number of Columns is important, but we may not know the correct number to use.
To get an idea of the number of commas in a field, you could use a Formula tool with code like this:
REGEX_CountMatches([your field], "\,")
