Pulling in Multiple Excel workbooks with different Sheet names

Hi, sorry for what is probably a basic question, but I'm a new user and haven't been able to find a solution in the Community. I have a directory with ~30 Excel files that I want to combine into one, where each file follows the same naming convention but with different date prefixes. Not wanting to have 30 input tools, I am trying to use a single Input tool with a wildcard. I am having 2 resultsing issues.

1 - each workbook usually contains 2-3 sheets; and the one containing the data I want never follows a consistent naming convention, but it usually contains the text "230". Doesn't seem like there is a wildcard option for sheet name, does anyone have any other suggestions?

2 - the relevant fields in the worksheet are always included, but are not always in the same column order. using an Input tool with a wildcard does not seem to have the capability to re-order & marry up the fields the way a Union tool does. is there a better way to do this?

any help greatly appreciated

Guy

Input

Accepted answers

JordanB

Hi @guystanley

Here is the workflow I created as a potential solution to the process you described below:

Pic 1.png

Read all your 30 workbooks in using a directory tool
Using the macro attached below update the input tool to one of your files referenced in the directory tool. Make sure you select the option to output full path as field and you select 'List all sheet names' when it asks you for a sheet.
Insert he macro into the main workflow and feed the directory too results into it. You need to select 'Full path' in the drop down in the macro configuration.
This macro will read all your file paths from the directory tool and update the 'list all sheet names' section in the input tool giving you all the sheets names for all 30 files.
You can then parse out the 'list all sheet names' from the full path and clean the data up using the select tool
Join the directory tool output to the sheet names macro section to have the right sheet names next to the correct full paths from the directory tool
You can ow use a formula tool to add the sheet name to the full path. Make sure you update the 'FullPath' field
Use a filter to select the full paths with '230' in them
Use a batch macro to read in these files dynamically regardless of if they have different field schema.

I have attached the workflow below (built in 10.5). It will error due to being mapped to my C drive however you should be able to see the bones of the workflow which should help to explain the process further.

Best,

Jordan Barker

Solutions Consultant

Read multiple Files & Sheets.yxzp

All comments

JordanB

Hi @guystanley

Here is the workflow I created as a potential solution to the process you described below:

Pic 1.png

Read all your 30 workbooks in using a directory tool
Using the macro attached below update the input tool to one of your files referenced in the directory tool. Make sure you select the option to output full path as field and you select 'List all sheet names' when it asks you for a sheet.
Insert he macro into the main workflow and feed the directory too results into it. You need to select 'Full path' in the drop down in the macro configuration.
This macro will read all your file paths from the directory tool and update the 'list all sheet names' section in the input tool giving you all the sheets names for all 30 files.
You can then parse out the 'list all sheet names' from the full path and clean the data up using the select tool
Join the directory tool output to the sheet names macro section to have the right sheet names next to the correct full paths from the directory tool
You can ow use a formula tool to add the sheet name to the full path. Make sure you update the 'FullPath' field
Use a filter to select the full paths with '230' in them
Use a batch macro to read in these files dynamically regardless of if they have different field schema.

Best,

Jordan Barker

Solutions Consultant

Read multiple Files & Sheets.yxzp

JordanB

The macro to read all sheets is attached here belo.

Readd all sheet names from all files.yxmc

guystanley

Hi @JordanB, this worked perfectly, thanks so much for the help!

parnell

Hi @JordanB

Hey, I'm having an issue with the first macro here. I'm reading the files from the directory (only 2 at the moment) and using one of them inside the macro as the 'input' as per your instructions.

However, the macro is ONLY reading in the data from that file (not the other file in the directory) and in fact is duplicating the data (reading the same sheet 2x)

Macro and workflow attached.

What am doing wrong here?

NEW_CD_autopay_1stofmonthbatch.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703