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
LordNeilLord
15 - Aurora

The same process will work with XLS...you just need to make sure you have the Access 2010 driver installed to read XLS files 

 

https://www.microsoft.com/en-us/download/details.aspx?id=13255&751be11f-ede8-5a0c-058c-2ee190a24fa6=...

LordNeilLord
15 - Aurora

Yeah it's a bit rubbish, I don't know why it's still not fixed (unless its intentional)

kellyreburn
7 - Meteor

After the macro, it is only showing the sheet names. How do I connect it to the other data that is pulled using the directory tool?

NidhinGD
7 - Meteor

The final result have file name (File path) and sheet name.Replace the <List of Sheet Names> in the filename field and send it to a macro for reading the excel.

 

Replace([FileName], "<List of Sheet Names>", [Sheet Names])

 

Thanks 

Nidhin

jenv20032000x
7 - Meteor

Hello and thank you in advance for helping me resolve the issue I'm having with the Directory flow you provided.  When I use the second "SheetNames.yxmc" macro I get the error "Sheet Names" is not contained in the record.  I mirrored your flow and macro.  I've attached the look of both the flow and the SheetNames macro.

petause14
5 - Atom

@LordNeilLord

I was searching for a macro like this and it's very helpful!

Is there also a way to only import the first n columns of each sheet?

Cidney
5 - Atom

Hello, 

 

In your example, the directory is pointing to yours, what do i need to change that too? In the beginning on my WF I am going into a directory and grabbing 10 spreadsheets. I need to now read all the tabs and data within those sheets. This WF works for me as is with your macro (not sure why as it is pointing to a nonexistent directory of yours).

 

Capture.PNG

usmanbashir
8 - Asteroid

Exactly what I needed - worked like a charm! Thank you!

AmitojSingh
7 - Meteor

i tried using this macro but for some reason i just get the sheen names - i am referring to the first part of macro, it wont bring the file names just the sheetnames for odd reason - any suggestions, why would it act differently for me

Labels