Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Get worksheet names from excel

Highlighted
7 - Meteor

Hi

 

I couldn't find the solution i was looking for.If this is a repeat question,I am sorry.

 

I have multiple excel sheets which i will be reading by using Directory tool and a Batch macro.

I am facing problems to do this when there are multiple sheet in the excel.

I tried with Dynamic input to fetch the sheet names of the files being passed through the Directory tool,but the limitation is that the Schema should be same.

I am working with Files which will have different schema for files and worksheets.

I only want to get the sheet names of individual files (Full path will be passed via the Directory tool).

I tries creating a macro by passing the Fullpath|<List of file names> to a macro where i use the Text Parse to fetch the sheet names.But this approach also failed due to the schema

Attaching the file i tried out with.

 

Thanks

Nidhin

 

Highlighted
8 - Asteroid

Totally misinterpreted. There are some good solutions here!

Highlighted
Alteryx Certified Partner

Hey @NidhinGD

 

I built you a little workflow to do this.....the first Macro reads all of the sheet names and the second macro imports those sheets into a single file

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
You can avert the schema issue you are having with the list of sheet names by going to the interface designer, and then the settings pane and set this too 'auto config by name rather than error if different.

This is an issue I have faced in the past when trying to create a similar macro.

Ben
Highlighted
12 - Quasar

Can you use the Input Data tool to get the list of sheet names?  See attached screenshot.

Highlighted
7 - Meteor

Hi @LordNeilLord,

 

Thanks a lot for the solution.This is exactly what i wanted.

A quick ques though.

I will only be needing till the First macro output as the intention is to get the filename sheet name combination.

If I remove the formula tool after the first macro Output,I am not getting the list but if i keep it (the formula tool after the first macro Tool:Formula 7)the Macro out and Formula out will both have the same output (List of filenames and sheet names).

Am i missing anything ?

 

Nidhin

Highlighted
7 - Meteor

Hi Chris,

 

As i am reading a set of files,input tool will not help me.I gave it a try by setting a macro out of it (attached in OP) ,but it didn't workout.

 

Thanks

Nidhin

Highlighted
Alteryx Certified Partner

@NidhinGD

 

Keep the formula after the first macro as it strip "|||<List of Sheet Names>" from the filename.

 

Also with a batch macro, you need a downstream tool to make sure it runs through all of the iterations (a known bug in Alteryx)

Highlighted
7 - Meteor

Hi @LordNeilLord,

 

Sorry to add on.

 

Is there a similar approach to read .xls files as well ?

 

Thanks

Nidhin

Highlighted
7 - Meteor

@LordNeilLord

 

Didn't know that (Needing a downstream tool for Macro).It's a useful info.

Thanks

Labels