Text to Column-Delimiter

[cstafford]

I have data that has multiple records in a field separated by a delimiter. I want to single out each record, vertically. Therefore, I am using the Text to Column tool. The data has 20 records, however, when it passes through the Text to column tool, it is only returning 16.

Any insight as to why this happening. Is their a limitation within this tool?

[AngelosPachis]

Hey @cstafford 

There is no limitation to the tool as far as I am aware. Maybe the delimiter changes after the 16th record?

Maybe you can share a sample of the data or mock-up some to have a look?

Please note the spitting into columns is not a very dynamic way of using the text to column tool, because if for some reason you get some extra columns in the future the number of selected columns won't dynamically update. A safer solution is to split into rows and then cross-tab, similarly to what I did in the attached workflow.

[Qiu]

@cstafford 

As commented by @AngelosPachis , better to use split to rows after assigning each row a Record ID.

The record id then can be used when using Cross Tab.

If using spit to columns, check the number for the columns.

[cstafford]

My VM is down at the moment so I cannot get screen shots of my workflow. I am using the split to rows feature as I wanted the data to be vertical. However, I am not using a cross-tab as the next tool. I am not sure if this will make a difference since only 16 records are being returned as the data passes through the text to columns tool but I will try it.

[Qiu]

@cstafford 
So maybe its the delimiter issue.
We would love to take a look if you could provide a sample data.

Guess the VM is taking the weekend as well huh.😀

[KK12]

Hi

Or we can directly apply Text to column instead of text to rows and configure the tool to  increase the number of columns to 20.

Thanks,

Kirti

[cgoodman3 · Accepted solution]

Before doing the text to rows, add a formula tool with the expression, regexcountmatches([YourFieldName],”,”)

replace the comma with what your delimiter is and then you can check you are getting the right number of delimiters or if it’s an error in your data.

[cstafford]

So I tried this test and it returned a total of 16. I then dumped this field into a text file and parsed by the delimiter through excel and it returned 20 records. Looks like something may be wrong with the data, but if excel parsed it correctly, I would certainly think Alteryx would as well.

[cstafford]

I did the same exercise in Alteryx with a .txt file and it returned 20. Must be something with my data source.