Import Sheet and Select fields using table

Question

Hi, I've been working on this issue for a couple of years and had only done it file-by-file, but I need to scale up the process. How it goes is that I'm given a metadata file to refer to. Then using the schema detailed in the metadata file, I import each input file listed in it, select the sheet I need, rename the fields to follow the schema in the metadata file, use a formula tool to add the fields that are missing manually, then merge the data from all the input files into one table and export that. However, this process is not scalable and too manual for me to continue doing it file-by-file.

I've attached a zipped folder with the example metadata file, and 3 input files with sample data to look through. Hope anyone has any ideas to share with a fresh pair of eyes!

Example Use Case.zip

OTrieger · Accepted Answer

@khooweicyn 
Add a Directory tool connected to the input folder. Now you will have the list of all the files.

In the Metadata file you have all what you need, file and and sheet name.

Match the data based on file name, now you will have all the needed files. Now add the sheet name to the FullPath and get a Batch Macro to open all the files.

Then you can do whatever data transformation in the batch macro, meaning that it will be done for each file separately, or after the batch macro, which means that you will transform the whole data in one go.

lwolfie · Accepted Answer

This is an example of what was suggested.  You could then continue it for the different filtering, etc that you require.  The renaming option could easily be moved into the macro.  You want to make sure you set up the interface designer to know that there are different schemas coming through.