Reading Multiple Files and Tabs

slckkrky

Hello all

I am trying to read multiple files and tabs in the same folder.

- There are two files for each with 2 tabs.

- File names are slightly different but body of the name is same.

- Sheet names are same.

- Data does not start from same row in every sheet.

- Data and structure between sheet A and sheet B are different.

File1 - sheet A and File1 - sheet B (This is the file with actual data)

File1Template - sheet A and File1Template - sheet B (This is the file with dummy empty data which i exclude later on)

I tried Directory and Dynamic input tools. However Dynamic input tool is giving an error as " No sheet is specified....". However i specified the template sheet and as a result it is reading from there.

Why is this error happening? Is there a way to solve this without macro?

If macro is needed because of different sheet names, Could anyone share step by step screenshot please?

I read all the articles about macros however they are not helping how to set up and configure each macro steps.

Thank you in advance

Datasets

Accepted answers

All comments

binu_acs

@slckkrky dynamic input tool wont work if your files are having different schema, you need to use batch macro for this. attaching a sample workflow which reads tabs from different sheets and consolidated into one

Multiple_Excel_Output_11122024.yxzp

slckkrky

Thank you for your answer.

1- I deleted extra sheets and left only 1 sheet. It is now reading all the data from Template and actual file however Dynamic Input file is still throwing same error. Why is that?

2- It is nightmare for me to configure macros for example data starts in row three in the file. How/where can i edit that in your macro?

3- Is there anyway to separate output after everything has been consolidated. Because later on i will add more and more files in same folder and use each file for different part of the workflow.

binu_acs

1.what is the error you are getting from the dynamic input tool?

2. if the data starts from row number 3 you can update the second macro input tool to start import from 3

3. yes, you can add the file name as a new field, then later stage you can separate the records based on the file name, seems to be the given macro will output file name as separate field as the last column

slckkrky

Hello

Now i got rid of the dynamic input error.

question 2- not all sheets are starting from row3. How can we solve that?

question 3- would that work to separate multiple different sheets in same file? so multiple file names and multiple sheet names.

questions 2-3 how do you open that configuration canvas? macros are just blue circle nodes in your sample workflow and i cannot find any configuration on them.

binu_acs

to open the macro right click on the macro icon then you will see the open macro option, make changes and save

2. if all the sheets are not starting from row 3 then you need to select the option first row contains data, in this case alteryx will assing the field names as F1 f2 etc for the all the sheets then later you can filter out unwanted records based on some conditions

slckkrky

Thank you very much. I am slowly progressing.

What do I need to chose for the input file within the macro? Is it ok to select any file in the folder? There is still your dummy file in the macro but it is still working.

Your macro works even in my folder to read all the files. But i have no clue how and why it works therefore I am raising these questions for configuring it.

Also could you explain again how to add file and sheet name as an output? Should i do the configuration on first or second macro?

binu_acs

@slckkrky you dont need to change the input file inside the macro, if you want to change , yes you can put any other excel file as input file, since you are dealing with .xlsx file make sure you are replacing with the an .xlsx file,

slckkrky

It doesnot work with multiple fields with data starting from different rows.

I can group files that starts data on same row and try to use multiple directory input for multiple macro.

How can i save macro separately? if i make any change in your macro, change is applied where ever i paste it.

Also can you explain how to create extra fields for file and sheet name please? I couldnot make it work.

binu_acs

@slckkrky can you upload sample input and expected output, so that i can update the macro accordingly and send to you,

slckkrky

I simply pasted your workflow and started with a Directory tool on myside.

This is also helping me to understand how to do in future.

1- I donot understand how to create new macro as in save as xyz ( your macro name is "sheet names") and use it in different part of the workflow.

in the workflow your macro is used two times and they appear to be identical. Right?

2- how to spit the source file and sheet names as an additional data.

as a summary i need amendment that will be able to read multiple files and sheets which starts from different rows and identifiers of the sheets so I can separate the data on later stages.

slckkrky

Thank you for your help buut this is also helping me to understand how to do in future. Because even most basic details are extremely unknown.

I simply replaced Directory tool with my Directory tool and thats it.

As a summary I need macro to be able to read multiple files with multiple sheets where data donot start from same row. As an output I need an extra field showing file and sheet name so i can separate data on the later stage usage.

1- How can i save as a macro and use it in different part of the workflow?

If i make a change in your macro, i.e. start data from row 3, then those changes are used everywhere same macro is used. In your sample macro is used twice and are they identical?

binu_acs

@slckkrky open the workflow and save as with a different name in different location, if you want to use the new macro right click on the workflow canvas and you will see an option to insert macro in the pop-up window, from there you can select the macro you saved

slckkrky

Thank you mate but i dont see any option you mention this is why if you can share some screenshot it will be very helpfull.

Macro has an image as a node. After I make changes to your makro Where can i save it? all changes are saved in same macro.

Do i need to save every macro as a separate workflow?

Your macro is saved somewhere in my temp folders. How do i save a brand new macro without any dependeny?

also do you have an answer how to get the sheet name as an output? i still dont find it.

slckkrky

I found most of the answers however I still cannot get the sheet names at the end of the workflow. Can you help me with that?

binu_acs

for sheet name you need to add another formula tool and extract it from the FileName field which will be in the last column of the second macro output

you can find the workflow macros in the folder where you downloaded the zip file where i sent to you initially, check for the folder which will have the same name of the workflow inside that you will find the macro, you can copy those macro and paste some where

binu_acs

REGEX_Replace([FileName], '.*\|\|\|(.*)', '$1')

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335