Bug: Reading numbers from Excel can produce floats

[seven]

When Alteryx 2018.4.3.54046 reads an xlsx, it can "introduce" a float where there was none before. This only happens in a specific situation and it's not clear WHY it is doing this. The actual numbers are exact to 2 and 3 decimal places in Excel. It isn't a matter of formatting or display. That's what they are. An example number: 1.43.

If there is a string value in the same column, then Alteryx will read the entire column as a string. So far, so good. When it does this, some of the numbers now look like floats. 1.43 now looks like 1.42999999998 in Alteryx. Not all of the numbers get this weird float treatment.

I cannot control the source files and they may have strings in various cells of the "numeric" fields. I have to read everything as strings. I want to know why this happens and what to do about it. Thank you.

[ThizViz]

I had this situation early on and it drove me nuts. Nothing I did could change it, until I had a video chat with support.

In my case, there were NULLs in my number fields of which I was not aware. Once I added a NULL to zero formula, I had no more problems. Not sure if it will help you or not. 

if isnull([field]) then 0 else [field] endif

[TonyaS]

@KylieF  can we move this one to B&I and have someone try to reproduce? One of our Devs is not able to reproduce this one so it might be fixed or there might be something very specific about the input file that we need to know to reproduce it. 

[KylieF]

@TonyaS 

I've moved this post to Designer Discussions, since that's Community's best location for bugs and have escalated this post to get Support to attempt to reproduce.

[ntobon]

@seven   Could you please provide sample data if possible, so we can easily test on our end?

[ntobon]

@kksieski   We haven't been able to reproduce the issue. I have not heard from you.  Could you please provide sample data (excel file) , so we can easily test on our end? 

[aklaum]

I am currently having the same issue.  Attached are the files I used to reproduce it.  The first float is introduced in record 171 column F53.  Let me know if any other information is needed.

[seven · Accepted solution]

I found a solution:

change the .xlsx to .zip, unzip the file, and examine the XML docs inside.

This post helped me to understand the process.

ttps://professor-excel.com/xml-zip-excel-file-structure/

Turns out Microsoft Excel lies to us sometimes when it presents the data. Alteryx sees through to what actually is in the doc, not what is presented to us.