Fields not read from multiple Excel files via batch macro

[mmaziere]

Hello!

I have an issue where I am importing multiple MS Excel files, via the batch macro method described in the following link: 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

The import process works well for nearly everything with the exception of any fields that are calculated within Excel (the cell contains a formula - E.G. "=IF(ISBLANK(H6),0,IF(J6/H6<=1,1,ROUND(J6/H6,0)))") . The batch macro will not import these values at all, unless oddly when I have the files open in Excel. Even then, if I have 10 files to be imported and only 5 are open...only those 5 will read in the contents from those fields.

Any suggestions?

[binuacs]

@mmaziere Alteryx should read the excel data even though there is a formula in it. Can you provide a sample input file with the formula? 

[mmaziere]

@binuacs  - I attempted to mock up some file data for you to look at, using 3 simple downsized files. However, when I run them through the workflow I get the expected results. The problem though is that the full 85+ files do not work for me. Unfortunately I cannot share the original Excel files, as they contain private details. Attached are the mocked up files though.

[apathetichell]

@binuacs  key piece of information seemingly omitted (.xls files...).... ... ...

question @mmaziere - did you create these .xls files or did you download them? If I was troubleshooting this - My first avenue would be exploring if you downloaded them and if there's something related to .xls and auto calculate in your security settings for excel.

My second would relate to Alteryx behavior around .xls files.

[mmaziere]

@apathetichell - These files are downloaded in that format already. I know that versioning of Excel is very old though. Do you suspect it is because the files are XLS rather than XLSX, to be the possible cause of the issue?

[apathetichell]

my expectation is that it's a combination between .xls and trust and security settings in your excel. - or a general auto-calculate setting...

[apathetichell]

but I should clarify - are you sure you are reading in for fileformat  8 - not fileformat 25 in your input data tool?

[mmaziere]

Yes, I believe so. Is that evident within the batch macro?

[mmaziere]

The issue is definitely related to the XLS file formatting. I converted all of the files that I have to XLSX and now the batch process works as expected. That being said, is there anything that can be done with the XLS formatting? I would like to avoid having to re-save 80-100 files each time to a new format?