Pulling in Multiple Excel workbooks with different Sheet names

[guystanley]

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

[JordanB · Accepted solution]

Hi @guystanley

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

• 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

[JordanB]

The macro to read all sheets is attached here belo. 

[guystanley]

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

[JordanB]

@guystanley

No problem at all! 

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

[parnell]

macro here

[gucher]

I am getting this same issue as well. Output is working for only one file, the one I used in the macro. 

[JordanB]

Hi @parnell & @gucher

Looking at your attached workflow the action tool inside the macro is not configured correctly. 

The screenshot below shows you need to delete the '|||<List of sheet Names>" part. This means just the full path which omes from your directory tool will be passed into the input tool and the "|||<List of sheet Names>" will remain the same. 

This will allow all the sheet names to pass through and should work as designed.

Please let me know how you get on.

Best,

Jordan Barker

Solutions Consultant

[gucher]

This worked. Thanks Jordan!