Problem parsing values from an XLS (Microsoft Excel 97-2003) Workbok

[Verakso]

Hi

So far by reading what I have could find on the Community, then besides the "known" issue in regards to read data from a XLS or and XLSX using the legacy driver, I have not found anything that directly relates to this issue-

I have an xls file, that have some blank lines and metadata there shouldn't be used, and data starts in line 10

Excel file

As you can see on the screenshot, then how the data is shown, is not the same as the actual values

When I import the data in Alteryx, it seems that it only takes the "formatted" value, and not the actual value.

Workflow

This give me some problems on how to validate the data, because it we sum the data in Excel, the data uses the decimals, and therefore it doesn't add up with my summarize in Alteryx

I can't see that many settings in the *.xls connnection, but is there anything I have missed?

How can I get Alteryx to read the actual values from the xls file

/Thomas

[DavidP · Accepted solution]

The problem is that in the Excel file, the cells type is set to "number".

If you change it to "General" and then load the file in Alteryx, it loads correctly/

[Verakso]

Thanks for the input @DavidP 

I did noticed that, and the change in the behavior if I change it from »General« to »Number«

The challenge with that is

• This was only an example, there is many more excel spreadsheets like this
• They come each month with new data, from somewhere else

Changing the type would be a project by it self, and kind of defeat the purpose of using Alteryx instead of Excel to do the data preparation, since we then would have to revisit every spreadsheet we have.

Br
/Thomas