Fields not read from multiple Excel files via batch macro

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-Files-with-Different-Field/ta-p/13370

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?

Batch Macro

Dynamic Processing

Iterative Macro

Accepted answers

All comments

binu_acs

@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

@binu_acs - 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.

RPT (1).xls

RPT (2).xls

RPT (3).xls

Copy of RPT_Service_Time_Analysis.yxmd

apathetichell

@binu_acs 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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335