Directory tool

Question

Hi,

I am trying to build a workflow to read all .xlsx files from a folder. Each excel file has multiple sheets so I only need the sheet with name "PRE PROCESSED LIST". This sheet includes lot of records and columns so I just want to count the total records in a column named "PRIME KEY". I am trying to build a workflow as seen below but not getting the exact output and not sure if I'm missing out anything.

Below workflow reads all excel files using dir tool. Then I use a filter tool to get only .xlsx files and use a formula tool to append the sheet name. After that I use dynamic input tool to read the records from all excel files in that folder. I feel the dynamic input tool is where I'm going wrong because in the Input Data Source Template I give the full path where I open a file and select the desired sheet. When I do this what happens is it's only outputting records for that file I connected to and is not reading all .xlsx files. Any help would be much appreciated. Thank you in advance!

Read_files.yxmd

mceleavey · Answer

Hi @aparna0208 ,

I built a couple of tools to do exactly this.

I've attached both tools and an example workflow showing you how it works.

The first tool reads in all sheet names from all workbooks, then creates the full filenames with the sheets attached.

The second tool then loads them all dynamically, replacing the templates with themselves, so the schema can change without the workflow failing.

This should do exactly what you need.

I hope this helps,

M.

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Flat Files.yxmc

Hollingsworth · Answer

@aparna0208 , I noticed in your Dynamic Input tool that in the Input Data Source Template that the text in that box was to a directory and not to an Excel file.

The tool needs an input file to use as a template to obtain the schema. Try configuring with a file instead of a directory and see what happens.

apathetichell · Answer

basically you'll need to send sheet into a batch macro and replace an input data tool file-value in a batch macro - but you know that this is potentially a blood bath of red error messages as every excel file in the directory without a sheet matching that sheet name will create a red error message.  Non-fatal -but in my case it did create a flowing river of red.