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

Count of Records - All Sheets

suby
11 - Bolide

All,

 

I have an excel with multiple Tabs say (40 Tabs)  and the schemas are different in each sheet and i want to produce a report just counting the record in each sheets. 

 

Attached the sample Data with expected Output.

 

Is there way to achieve this using a Macro ?

 

Thanks

5 REPLIES 5
IraWatt
17 - Castor
17 - Castor

Hey @suby,

Here is a batch macro which can do this:

IraWatt_0-1656410442253.png

IraWatt_1-1656410454499.png

]Any questions or issues please ask :)
HTH!
Ira

 

 

messi007
15 - Aurora
15 - Aurora

@suby,

 

Please see below :

 

messi007_0-1656410827085.png

However you have to update the path inside the macro :

 

messi007_2-1656411041548.png

 

Attached the workflow,

Regards,

mceleavey
17 - Castor
17 - Castor

Hi @suby ,

 

I've attached a workflow which does this using repeatable tools.

 

The first step is to use the Directory tool in conjunction with the first bespoke tool that reads in all sheet names from the file. I then removed the Output sheet as this is not needed:

mceleavey_0-1656410714157.png

 

This provides the following:

 

mceleavey_1-1656410736520.png

 

At this point you join it back to the Directory tool output and create the new fullpath field including the sheet names:

 

mceleavey_3-1656410787770.png

 

mceleavey_4-1656410807088.png

 

At this point, given your use case, you don't need to use a batch macro to loop through each one, but you may do if you want to proceed with separate loads for each sheet. However, given your use case is to simply count the records on each sheet, you can then proceed directly to the next tool which loads the data in:

 

mceleavey_5-1656410883543.png

 

A sum tool can the group the records by sheet and provide a simple count:

mceleavey_0-1656411022000.pngmceleavey_1-1656411035156.png

 

I hope this helps,

 

M.

 

 

 



Bulien

suby
11 - Bolide

Thanks All.

IraWatt
17 - Castor
17 - Castor

No worries @suby 😄

Labels