This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 |
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?
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!