Macro to read all tabs at once and Data for each tabs starts from Different rows.

[DataPirate26]

Hi team, 

Is there a possible way to create a macro in which It will read data from all tabs of a single file having different schemas ? But the challenge is in each of the tabs the data starts from different Lines. Can anyone help me with a solution. 

[Qiu]

@DataPirate26 
This is the macro you should be looking for.

As for the challenge, I think we have to do the cleansing after bringing all the data in.

https://community.alteryx.com/t5/Public-Community-Gallery/Import-Multiple-Schema-Excel-Files/ta-p/91...

[binu_acs]

@DataPirate26 you can also try the batch macro attached in the below post

The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten

[DataPirate26]

@binu_acs @Qiu  I have only one Single File which has 20 different tabs and the number of fields in each of the tabs are different like if Tab 1 has 8 fields , Tab 2 has 12 fields and so on. I want to read the entire data from all the tabs at once, but as the fields are different so it's little difficult.. Can you help me out with a simple Macro rather. The above post does not give the desired result. 

[Sebastiaandb]

@DataPirate26 ,

Try this one out :-).

I made a batch macro that will read the header row as a data row and then filters out all empty rows on the first column. If the data starts at row 10 it will just leave out the first records :-). Afterwards it will take the first row with data (the original headers) as the headers to use. 

Greetings,

Seb

[DataPirate26]

@Sebastiaandb Sorry this won't work as I told the no of Fields and the Field headers are separate for each tabs. I want all the headers for all the tabs to be there and not any one of it shall missout. Let us assume data starts from Row 1 in this case. 

[binu_acs]

@DataPirate26 Can you try the attached workflow?

[DataPirate26]

@binu_acs No This doesn't work. The name of the File is Example.xlsx and the sheet name is A for the Template you have taken. Why are you replacing Test 1 and from where are you getting that value. 

[DataPirate26]

So there are total of 20 tabs and the no of Field and the Field headers are different in each of the tabs. So when we are taking any one of the Tab as the template in the Macro it is taking that as the standard one and the Output for all other tabs are not coming out properly. So its not just the Schema which is different the structure of the tabs are also different. 

[binu_acs]

@DataPirate26 You need to replace the example.xlsx with your input file , 

The steps are

1. Extracting all the Tabs name from the input file

2. The macro combining all the data from these tabs into one output file

If it is not still working please provide a sample file with your different schema and expected result