community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Alteryx removing tab characters from data without prompting

Highlighted
Meteor

Something interesting I've noticed with Alteryx which impacts on its ability for data cleansing where Alteryx is removing data without prompting. Unless I am missing something, this would appear to be a bug in Alteryx (I am using version 11.7). Has anyone else come across this scenario?

A common scenario I use Alteryx for relates to delimited data and where the delimiter appears in a text field. Normally it's pretty easy to perform a count of the delimiter character in the data and check for rows where this is not consistent.

 

When you have a file with tab delimiters, and you read in data as one row Alteryx appears to remove the last delimiter. If you see the data in the screenshot, I have the same data with the same number of delimiters but three different delimiters:
1. Tab
2. ASCII character 176 ░3. Pipe character | 

 

image.png


When I read this into Alteryx as one column of data (which is needed for cleansing extra delimiters in cases where it exists in the source data), Alteryx is removing the last character(s) where the delimiter is a tab. This impacts both the length of the field as well as if you are counting delimiters for the purpose of identifying if there are extra delimiters where the data includes a tab delimiter.

Fortunately if you are cleansing data and you want to output to tab delimited data where the delimiter was something other than a tab, it seems to be fine. 

However, this issue will impact on cleansing data with tab delimiters if the last field in the data happens to be blank as the extra delimiter(s) you will lose delimiter(s). 

Given cleansing is looking for column shifts based on extra delimiters, it will create an issue when using Alteryx based on counting the delimiters. For example, if you have 20 tabs in a set of data but have two extra tabs in a given row, but the last two fields in the row are blank, the count in Alteryx will be 20 for all rows.

I've attached both my test file and the Alteryx workflow to this message  which will show the issue.


In summary this impacts on the ability to use Alteryx for data cleansing for extra delimiters in the following scenario:
1. Tab delimiters are used AND
2. Any rows of data include the last column(s) of the row that are blank

 

I would be interested to know:

1. Am I missing anything?

2. As to if this is a known bug and is on the radar screen for fixing?

ACE Emeritus
ACE Emeritus

Hi @

Agreed that seems like a bug: not sure why a tab would be removed without say-so.

 

For a work-around, you can read it as n ascii flat file and make sure to set the "Trim white space" option to "Do not trim white space."  Making that change allowed me to read in your files.

 

Hope that helps!

John

Meteor
John

Thanks for the feedback. Unfortunately that won’t work as the file is encoded UTF-8 with a byte order marker and I believe alteryx still won’t read that in properly (I raised this in the forum a couple of years back, and a latter version of alteryx allows for this with CSV only).

A colleague of mine highlighted if you read in with a delimiter of \0 rather than \n it reads in properly. I am not sure why this is not documented in alteryx but at least it seems to work!
Labels