Excel Star Read in

[kpmg_lc_analyst]

I need to to do a star read in of 18 excel files, all with different sheet names.  I am running Alteryx 9.5.  How do I accomplish this?

Thanks in advance.

[JordanB · Accepted solution]

Hi kpmg_lc_analyst,

The easiest way to do this in Alteryx 9.5 would be:

- Use a directory tool and wildcard function to bring in those 18 files from the one folder. The directory tool will then generate a full path field (Below)

- Once you have the full path field you can use a formula tool to append the sheet names to each full path record. If you are unaware of what the sheet names are, unfortunately there is no other way in 9.5 than manually entering these sheet names in at once. That feature was added in Alteryx 10.1. 

- If you manually type the sheet names into the text input you can then use the join tool (by record position) to dynamically place the sheet names next to the full paths from the directory tool. 

- You can then use a formula tool to update the full path field ([full path] + [Sheet Names]). 

You can then use a dynamic input (same field schema in each file) or batch macro (different schema in each file) to read through the files. This article is a great resource as reference to achieve these two inputs. 

Best,

Jordan Barker

Solutions Consultant

[MarqueeCrew]

if the files are in the same format, setup the process with one file and then place * wildcard(s) in the name to get all files in the input tool. This is the simplest approach.

[kpmg_lc_analyst]

that won't work.  the sheets are all different names, as mentioned.

[kpmg_lc_analyst]

thank you. I think now is a good time to upgrade to 10.5.

[jgo]

Attached is an example of how to read in multi sheets across multiple files. I know your question was more around just multi sheets in 1 file, but I think it will probably still relate.

... but yes, upgrade to 10.5+ will be required in order to utilize this option.