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

[binuacs]

@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

[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.

[binuacs]

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.

[binuacs]

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?

[binuacs]

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

[binuacs]

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