Excel Multiple Input, different layout, different file formats

[AA007]

I want to read in files that are in xls,xlsx, csv and want to make one layout of the file from the various layouts they have. Big problem is trying to read all data together? Is there a way around or should I have to make all of the files in one format and dynamically reel them in?

[echuong1]

You could standardize the formats and then dynamically bring all three in. While this is an option, it might not be the best as you'd have to standardize everytime you wanted to re-run this analysis.

Instead, you can bring in each file individually with a separate input data tool. From there, you can use the various functions under Preparation to get the data into that standard format. Once you have all three cleaned up, use a Union to stack them together. This why you only have to configure the cleanup once!

Hope this helps!

[AA007]

For more than 100 excel files and 100's of sheets it is just not as possible in my thinking. I found a workflow by @CameroonS this workflow is pretty robust but the modifications I wanted to add are not as easily approaching me. I want the workflow to be able to read a csv file as well such that i can use the preparation tools then fall them all under one layout.

[echuong1]

Can you outline what specifically is different about your files exactly?

If the differences are minor (ex: file type, column order, field types, etc.) you can use a batch macro.

[AA007]

File types : xls, xlsx, csv
multiple sheets in xlsx files

I want to combine the excel files and work to get to one unified layout.

[phottovy]

I can't remember if this works with csv files but you might look at the "Wildcard XLSX Input" in the Crew Macro Pack:

http://www.chaosreignswithin.com/p/macros.html