Text to Column-Delimiter

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?

Developer Tools

Reporting

Output

Custom Tools

Preparation

Accepted answers

cgoodman

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.

All comments

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.

example.yxmd

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.

KK12

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703