community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

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

Highlighted
Alteryx Partner

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

 

2019-01-25 13_29_25-Test.xls  [Compatibility Mode] - Excel.pngExcel 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.

 

2019-01-25 13_30_56-Alteryx Designer x64 - Test.yxmd.pngWorkflow

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

 

 

Pulsar

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/

 

number.png

Alteryx Partner

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

Labels