Data Preparation

[bsk1504]

Hi all,

I have a .xlsx file which has multiple sheets with the same schema. I want to combine all the data from Income Statement Part from different sheets into one file. Basically, I want all the Income statements from all the sheets into one file. I have to know which income statement belongs to which sheet in my single file. 

I am new to Alteryx and I want guidance to do this. 

Thanks

Sravan.

[BenMoss]

Hi!

There are two things I would suggest trying, the first would be to use the wildcard xlsx input which was created as part of the crew macro pack, this can be found here: https://community.alteryx.com/t5/Engine-Works-Blog/Crew-Macro-Pack-2015-Q3-Release/ba-p/5423

However, the structure of your datasource looks complex so I'm not entirely sure how it will handle.

Another option would be to look at batch macro's to input one sheet at a time performing the same transformation process to bring it into a more standard schema. Creating a batch macro would then allow you to loop through all your sheets without having to build out the workflow n number of times.

Here's a post that may help you get started: https://www.thedataschool.co.uk/rachel-phang/batch-macros-alteryx/

You could also try this batch macro created by one of the alteryx engineers, which allows you to feed in a list of paths and it unions all the files together (very similar to the wildcard xlsx but I think there is a bit more flexibility in there). 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

Ben

[bsk1504]

Hi Ben,

I tried Batch Macros. I am getting a result like this. It is dumping all the sheets' data one by one into rows. So I tried to extract specific rows related to the income statement, but I couldn't.

Thanks

Sravan.

[BenMoss · Accepted solution]

What you need to do is take just one sheet, and built an entire transformation of this one sheet into what you want it to look like, and the variety of sub-tables that you have within it.

Once you have achieved this for one sheet, convert the entire transformation process into a batch macro, rather than just the input of the file.

Could you give it a go with just a single sheet, build your transformation and then we can help you apply a batch macro once this is complete.

Ben