Is this a bug: I've broken Alteryx!

I've been inputting some CSV files saved from Cognos Query Studio (QS) into Designer 2019.1.6.58192.

QS has saved the files as tab separated rather than comma separated which has been fine, I've opened in Excel and done a Save As to fix this. However I've one file with over 5 million rows that can't be opened in Excel..

I've loaded it into Alteryx and used the text to columns tool to split out the 12 columns with \t delimiter. That worked just fine. But adding on a Select tool next to remove the original column doesn't. The column remains. I managed to get rid of it by using a Summarise tool with Group By on every column apart from the original one I want to get rid of.

After running with no errors, the Text to Columns tool later starts to report a "field '[original column name]' is not contained in the record" error, even though it is, and running the workflow again gets rid of this error for a few moments.

Here's the workflow with the problem column highlighted after the Select event has run to remove it.

Input

Bug

Accepted answers

JohnJPS

Have you tried opening as tab-delimited right in Alteryx?

Here's one approach:

Or, treating as a csv but specifying \t for the delimiter:

Hope that helps!

John

All comments

JohnJPS

Have you tried opening as tab-delimited right in Alteryx?

Here's one approach:

Or, treating as a csv but specifying \t for the delimiter:

Hope that helps!

John

CharlieS

Hi @tonypreece

I've got a few thoughts and suggestions that might help with your errors.

- TextToColumns: When this tool is set to "Split to Columns" The original field will be left along and new fields will be created and appended based on the split criteria set. So it's normal that the original field will be there.

- Normally, you are correct that deselecting the field will persist between runs, but I think Alteryx is struggling with your field name. What I mean by that is it looks like you have all the field names (tab delimited) in the first position. I imagine this creates a very long string that might be hitting internal length limits in some points.

My suggestion is to avoid this massive field name with a couple steps.

- When you input your .csv file, deselect the option on the Input tool that says "First Row Contains Field Name". This will assign a generic "Field_!" to the first field and your field names will be the first row of data.

- Use the TextToColumns tool like before, but this time it should split the field names in addition to the data below.

- If this all organizes correctly, you can use a Select tool to deselect "Field_1". This should be more reliable as this generic, position-based name should not change.

- Now all you need to do is use a Dynamic Rename tool set to "Take Field Name from First Row of Data" to apply the field names.

Check out the attached workflow for an example using the FIFA player data from a previous Weekly Challenge.

20200214-CSVImport.yxzp

CharlieS

I think @JohnJPS nailed it. If you set the tab delimiter in the Input tool, it will apply to field names as well as data and the rest will fall into place.

tonypreece

Both good solutions @JohnJPS and @CharlieS

I went with \t on the input configuration (but only after running my original workflow with the summarise tool then writing it back to a CSV file.

One to remember for next time. Thanks both.

#StillABugThough!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703