Batch Macros for Multiple excel files with different schemas - Help!

Hi folks,

I have been working on coming up with a batch macro to import multiple excel files that have different schemas from a folder with sub directories and I keep getting an error when I insert the macro to a standard workflow to import the files from the mapped directory.

The error I get is Error: Multiple Excel different schema macro (2): Record #245: Tool #1: No sheet specified, you must specify a sheet

Just to add the excel files I would like to import have one sheet each but the sheet names are different.

I have looked at a couple of tutorials and most are using csv files rather than .xslx files hence haven't managed finding a solution and its doing my head in as I have been at it for a while now

Subset of files to be imported.PNG

Error Message

Batch Macro

Accepted answers

jdminton

Hello @PeggyGithinji, I've fixed the macros and workflow to work with the data included. I anonymized your data as it appears to not have been anonymized in your package. Please remove your data. I removed the relative references as this was causing some issue. You can try adding them again on your end once you've confirmed these macros are working for you. I also checked the box for the first row contains data in the second macro and added some blank fields in the template as you'll have different headers as well as a different number of columns. The excessive number of headers will capture any extra columns of data in subsequent sheets/workbooks. I made one of your workbooks 3 worksheets to include multiple sheets in the test. Please let mw know how this works for you.

Multiple excels with different schema and different sheet names.yxzp

All comments

jdminton

For Excel files you'll need to update the entire path. The part at the end like "|||Sheet1" or similar, identifies the sheet name. You can import the list of sheet names in the input to identify the names.

binu_acs

@PeggyGithinji Attaching a sample workflow with two batch macros, the first macro reads all the sheet names and the second macro combine all the sheets into one

CombineFile_030623.yxzp

PeggyGithinji

Hi @binu_acs and @jdminton

Thank you for your inputs. I have tried out the suggestions but keep getting an error, not sure what's causing it. Attaching my workflow and sample anonymised data of the excels I am trying to import to help with the troubleshooting. Thanks!

jdminton

Multiple excels with different schema and different sheet names.yxzp

PeggyGithinji

Hi @jdminton thank you for your help in fixing the macros and also your professionalism in handling the data after my mishap. It now works on my end without any errors.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703