Alteryx incorrectly pads zeros to a single digit numeric value of a .csv file

Having trouble with Alteryx when it reads data from a .csv file. The file is being read using an Input Data tool and set to read a .csv file type. However it incorrectly pads zeros to a numeric value even though it doesn’t have zeros when opening the file from Excel. As a result I don’t get the correct number of matched rows when using a Join tool.

Alteryx CSV Error.PNG

Data Investigation

Accepted answers

philipmannering

Hi @ricoo

This is very strange. What do the value appear like in a text editor? Is it possible that Excel is changing the 00# to #? And not Alteryx changing the # to 00#? Can you share an excerpt (say, first 10 rows) of the data? If it's always zero padded to three characters, it is possible to only trim if three characters long.

Let us know if any of this would work for you.

All comments

binu_acs

@ricoo Seems to be the ProductID field is a V_String, if you want to remove the leading zeros you can use the formula TrimLeft([Product Id],'0')

ricoo

Hi @binuacs thanks for the suggestion. But my problem if I apply that formula is that, there are other data that originally starts with zero. And I don't want to trim those. The main problem here I think is how Alteryx reads the data using the Input Tool for .csv files.

Based on the screenshot I provided, this is the actual data from Excel (this is a .csv file): Note the ones boxed in RED.

This is how Alteryx reads it using Input Tool. The values boxed in Red above, are now appended with Zeros. (This time, NOTE the ones boxed in GREEN)

If I apply the formula you suggested, the ones boxed in GREEN above would also be truncated

philipmannering

Hi @ricoo

Let us know if any of this would work for you.

ricoo

Let me investigate this further. I'm currently experiencing a very weird situation with my file. When I opened the .csv file in notepad (as you suggested) it does have "000" at the beginning.

BUT, when I deleted the other rows (to give you an example of 10 rows only) the leading "000" disappears.

ricoo

Hi @binuacs @philipmannering . Seems like this is an issue when opening files using Excel and not with Alteryx. Thanks for helping.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703