Input Data Error with Excel file

Toshi92

Hi, all.

I have one issue about excel file input.

I often use excel sheets with meaningless headers, so I have to cut these headers before calculation.

So I need to not checked "First Raw Contains Data" on the configuration of Input Data tool.

In this case, I use input data tool as usual, but some values are strangely converted, like below.

Now I select "Microsoft Excel Legacy" input, therefore I have no issue.

But I think it is just compromise, isn't it?

I want to know why this happened, and want to select regular input style(pattern 1 on the picture), if possible.

Please help me

test.yxzp

Input

Accepted answers

jrgo

@Toshi92,

I recall finding a post a while back about that explained why this happens, but I failed to find it. However, (if memory serves me right) it was how the programming language Alteryx is built on handles values. I know this isn't an explanation to WHY it happens and, unfortunately, I have no idea.

That said, the reason why all fields come in as strings is because of the first row that you configured to read as data. Since it contained a non-numeric value, it had to attribute the field as a string so that it does not NULL out the value. I updated your workflow and used a Dynamic Rename tool to use the first row as your column headers (common practice after removing those header rows) and then converted that field back to double in your Select tool. The number does go back to normal.

Regarding your last question if using the legacy driver is a compromise, I would say yes it is. Reason is because Alteryx no longer includes this legacy driver and only shows up if you the Microsoft Access Jet Driver so you may have issues with sharing or publishing (server) if the driver is unavailable.