Read in Excel files with different schemas and different sheet names with Crew Macro

[Peter4]

I have a several different Excel files which have different names, different schemas and different sheets. I've also got a mapping table which renames the columns of each Excel file.

I'd like to import all Excel files at once so I've used the Wildcard xlsx Crew Macro. I managed to rename all files that come in through the N output, but I don't know how to rename the ones coming in through the other output. To complicate matters, my column headers aren't always in the first row.

One idea would be to push down the column names from the other output and rename them as F1, F2, F3 etc and union them on the N output. However, I don't know how many columns I might have so I'm looking for a way to account for files with only 3 columns and for files with 50 or more columns. Is that possible without hardcoding a text input file?

Please let me know if anything is unclear.

[Peter4 · Accepted solution]

I found a solution.

[praveenpantula]

Hello Peter.

I do not have access to download the design.  Is it possible for you to append the screen shot along with explanation.

Thank you for your help in advance.

Regards,

Praveen

[praveenpantula]

Hello Team

Request you to help me