Working on a Macro with Excel files

Hello There ,

Im a newbie in Alteryx Designer and i have to build a macro ( batch) to read my Excel file.

The idea is from a workflow :

Excel File -> Full Path -> (Macro w control parameter to get the FullPath) -> Output : FileName
SheetName
RowNumber
ColumnNumber
Name (Column name)
Value ( inside my row)
IsColumnEmpty
IsRowEmpty
Over10M (lines)

On my macro i used a dummy excel file as example only to get the fullpath and to connect with my prep.(One dummy file and Sheet1)

The problem is actually from my principal workflow.

I have an Excel file with differents sheets but same columns and structure and its working.

My goal is to get from an excel with differents sheets structure:

Sheet 1( columns 1 to 105columns and X rows)

and to have the info Sheet 2( 2 columns and 2rows) for example.

Do u have any solutions to aim my objective ?

Batch Macro

Accepted answers

OllieClarke

@Antoine-Vauthier I think this is what you're after:

The workflow uses the directory tool and dynamic input tool to get all the sheets within every xlsx in a specified directory. It then constructs the fullpath and feeds them into a batch macro

The batch macro reads in each sheet and creates the columns as I have understood them:

This is the ouptut:

As I say, I've had to make some assumptions, so if they're wrong a clearer expected output would help a lot.

Ollie

inputting excels.yxzp

All comments

OllieClarke

HI @Antoine-Vauthier, could you share dummy input(s) and the output that you're looking for please, it'll be quicker and easier to get to your desired solution

Dark_Star9

Hi @OllieClarke

First pic my dummy example excel on my macro workflow

dummy example.png

Snd pic my dataset with differents sheets and not the same columns numbers and rows

input macro.png

Third

The output alteryx is giving for my first sheet but not the others one cauz not the same structure. As you can see below only the first sheet is here

output macro.png

jrlindem

I wonder if @abacon's macro (Dynamic XLSX Input Tool - Alteryx Community) would be useful to this thread. I know he's spent a great deal of time developing solutions to solve for more flexible excel-based ingestion.

-Jay

abacon

I haven’t dug too deep into the problem but that macro does allow for different schemas in your xlsx files to be put together. It should solve the problem at a high level glance.

Bacon

Dark_Star9

Unfortunately , your macro isn't what im looking for. Like there is no connection between the sheets they are differents

OllieClarke

inputting excels.yxzp

Dark_Star9

Hi @OllieClarke Tsym for making that macro. Thats excactly what i wanted ! as output.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703