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

Get worksheet names from excel

NidhinGD
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

 

18 REPLIES 18
WillBowman
9 - Comet

Totally misinterpreted. There are some good solutions here!

LordNeilLord
15 - Aurora

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

 

 

BenMoss
ACE Emeritus
ACE Emeritus
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
ChrisTX
15 - Aurora

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

NidhinGD
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

NidhinGD
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

LordNeilLord
15 - Aurora

@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)

NidhinGD
7 - Meteor

Hi @LordNeilLord,

 

Sorry to add on.

 

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

 

Thanks

Nidhin

NidhinGD
7 - Meteor

@LordNeilLord

 

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

Thanks

Labels