Input Data - xlsx mixed data types issue

Hi,

I have an issue with using Input Data tool for reading xlsx files that contain mixed data types. In such cases Alteryx sets V_String data type for such columns and that is fine, however the result are a bit unexpected and they do not correspond to values contaned in the spreadsheet, e.g.

A	B
a	1
0.001	0.001
18.4	18.4

is read by Input Data as:

A	B
a	1
1E-3	0.001
18.399999999999999	18.4

What might be the reason for such behaviour? We use that data to compare it later with data extracted from another set of files to list differences, so it creates false positives, because from one file we get 18.399999999999999 and from another one 18.4 (as it should be) if it happens to contan numbers only.

I tried it in Alteryx Designer 10.6.8.17850 both x64 and x86 versions and I get the same results.

In the attachment you can find small example that demonstrates this issue.

Kind regards,

Daniel

Test.xlsx

Test.yxmd

Input

Connectors

Interface Tools

Accepted answers

phil_budden

Hi Daniel,

Sorry it's taken a while to get back to you, I had to leave the office yesterday.

Thats very odd about the csv files, I'm sure someone can explain it but its beyond me.

How about this, I've created a unique identifier variable using the RecordID tool and then split out the NULL values created by the coersion and joined them back to the original dataset via the unique identifier variable. I've then unioned all the data back together. It works for the test dataset you've provided so will hopefully do the trick.

fixTest2.yxmd

All comments

phil_budden

The issue is the "a" in variable 'A', Alteryx reads this in as a V_String not a Double like variable 'B'.

You can use a select tool to amend this to a Double, it fixes the issue with your numeric entries but does change your "a" to a NULL. It's not elegant but the only way I could think of to fix this is to drop another Select tool changing it to a VW_String then use a Formula tool to change NULL's to "a" - of course I have no idea if you only have "a" in your full dataset as if not this will require a different solution.

I've attached my workflow - hope this helps.

fixTest.yxmd

DanielR

Hi Phil,

Thanks for your comment. I was already considering using Select tool to enforce specific data type and it seems to convert the values back to their original form (although it throws conversion errors due to lost precision), however as you wrote the downside is that I will loose all non-numeric entries this way. The example that I posted is just a small extract to present the problem. Full dataset has hundreds of columns and thousands of rows, so if possible I wanted to try some other solutions before I go with adjusting the data types for all of them.

The strange thing is that if I try to input exactly the same values from csv or using Text Input tool then the results are correct and the column 'A' is still set by Alteryx to be V_String exactly as when I read the data from xlsx. Unfortunately I am stuck with xlsx files, because that is what I receive from business users. It seems to be a bug in Input Data tool when used with Excel.

Kind regards,

Daniel

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703