Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Request to not drop last empty in Text to Columns - Split to Rows

It would be great if there was a way for the Text to Columns tool did not drop the last empty when using Split to Rows.

 

For example, if I had the data:

RecordIDString
11,2,3
21,2,
31,,

 

Notice that each value has two commas (representing three values per cell), and If I configure to split into rows on the comma character, what would you expect the result to be:

 

Result A:

RecordIDString
11
12
13
21
22
31
3 


OR

 

Result B:

RecordIDString
11
12
13
21
22
2 
31
3 
3 


OR

 

Result C:

RecordIDString
11
12
13
21
22
31



I would expect Result C if I selected "Skip Empty Fileds", and that is what happens if I select that option.

 

But If I do not want to skip empty fields, I would expect Result B, but what I get is Result A where the last value/field is dropped/skipped.

 

What would it take to Result B as the output from the Text to Columns tool?

1 Comment
Meteor

2019.2

Confirmed - same behavior still seen in version 2019.2 - drops last column if empty.

 

My work-around was as follows.  Case: reading in several files with varying numbers of columns (but at least within each file, the first row is headers, and the data have consistent # columns with header).  Desired output - vertical version of each file, with name/value pair per "cell" of data, regardless if the "cell" was empty (a la your "Result B").

What I did:

  1. Text to Columns (rows)
  2. Multi-row formula to create a FieldID (sequential)
  3. Split header row from rest of data
  4. Join header stream with data stream on file and FieldID
  5. Union the L and J outputs.

If the tool worked like you'd expect (your "Result B"), you would only need the J output.  But including the L ensured that I got cases where the last column is always empty.