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

[PeggyGithinji]

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 

[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.

[binuacs]

@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

[PeggyGithinji]

Hi @binuacs 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 · Accepted solution]

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.

[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.