Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple Sheet Excel File to Separate Alteryx Database Files

CandidDataScientist
7 - Meteor

Hello,

 

I have an excel file with 113 different sheets all with a different schema. Is there a way to read this file in, and output each sheet to a separate .yxdb file? 

7 REPLIES 7
CandidDataScientist
7 - Meteor

Adding a workflow with the data source so everyone can see an example.

JoshKushner
12 - Quasar
You can do this with a batch macro and adding a suffix to your files in the ouput tool.

Batch Macro: if you Fred in a single column with each if your sheet names into the co trol input of your batch macro and the file path into the regular input of your batch macro you will dynamically read in each sheet.

Output with Suffix: instead of using the macro output tool in the macro, use a regular our put tool. Add a suffix to each output file to match the sheet name. (Bottom of the output tool config.

Hope this helps!
Carlos_A
8 - Asteroid

Hi @CandidDataScientist,

 

You can do it using a batch macro in your main workflow. A batch macro runs once for each record in the dataset and performs some operation on it.

 

The idea is to input a list of all the table names in your excel file and pass these to the macro. The macro then reads one sheet at a time and outputs it as a yxdb. I have attached a solution to the example you provided.

 

Note that since they have different schemas, you will have to change the batch macro configuration to reflect that. To do so, open the interface designer and select the "Output fields change..." option on the properties tab.image.png

CandidDataScientist
7 - Meteor

Hi @Carlos_A.  My employer is still on 2018.2. If you have time, would you mind saving the example to 2018.2 and resending?

Carlos_A
8 - Asteroid

No problem, here's the main workflow

Carlos_A
8 - Asteroid

And here's the macro. It seems that the community doesn't let us share macros through here. You'll have to change the extension of this file to .yxmc, but it should work. Let me know if you have any issues.

 

The only thing you'll have to do is to repoint the workflow and the macro to the 'example.xlsx' input file you provided.

CandidDataScientist
7 - Meteor

Thanks @Carlos_A.  I apologize but I don't think the macro is packaged with this though.  Maybe a screen shot of the macro?  This is what i see. 

Labels