Reading 2 different tabs from Excel with different schemas

[Ykaty369]

I am looking to read 2 different tabs from the same Excel workbook, however each of the tabs have different schema.

I'm then looking to join the information on each of the tabs to other inputs in my workflow.

I can do this simply by just adding 2 different Input Data tools to my workflow, where each tool reads the different tabs, but I'm not looking to do that.

I'm not an expert with macros but am thinking that's the approach needed.  Any help or ideas would be greatly appreciated.

Thank you!

[benakesh]

Hi @Ykaty369 ,

This is one of the most common excel  related  issues .  Check this link .

You can   read sheet names  and  call   batch macro for each sheet  with dynamic input  ,

Use this for schema errors  in macro . 

[Ykaty369]

I followed this example when trying to create mine and was not successful.  In that example 3 different files were used and only 1 sheet from each of those files.

I need to pull 2 different sheets from the same file.  Should I show or walk through my example?

[benakesh]

Hi @Ykaty369 ,

Can you share  wf and   file with small subset of data with  different schema in 2 sheets . 

[Ykaty369]

Essentially I'm looking to accomplish 2 things:

  a.  Pull data from the Vector Table tab and join to another data set (data set 1)

  b.  Pull data from the CF Prefix Table tab and join to a second data set (data set 2)

[benakesh]

Hi @Ykaty369 ,

   a.  Pull data from the Vector Table tab and join to another data set (data set 1)

  b.   Pull data from the CF Prefix Table tab and join to a second data set (data set 2)

I don't see the need of macro as the  2 sheets contain different data . 

Simple solution would be to read  each sheet with different input  and combine data set1 or data set2  . 

[Ykaty369]

That is currently how I do it.  However, this actual process is repeated multiple times throughout my workflow (other spreadsheets that have multiple tabs) and hence I am rereading the same workbook over and over and over and over.  I am wanting to clean up the workflow and eliminate the redundant data tools. 

For example I have one workbook with about 20 tabs.  I then need 20 data input tools for the joins I do with that one particular workbook.

Wouldn't a macro be cleaner?

[benakesh]

Hi @Ykaty369 ,

If  process is repeated multiple times throughout  wf    try  Standard macro   .   

[Ykaty369]

Can you help write a macro that pulls multiple sheets from the same workbook, where the sheets have different schema?