Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel Star Read in

kpmg_lc_analyst
7 - Meteor

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.

5 REPLIES 5
JordanB
Alteryx
Alteryx

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)

 

Directory tool.png

 

Full path'.png

 

 

- 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]). 

 

formula.png

 

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
20 - Arcturus
20 - Arcturus
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.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
kpmg_lc_analyst
7 - Meteor

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

kpmg_lc_analyst
7 - Meteor

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

jgo
Alteryx Alumni (Retired)

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.

Labels