Multiple Sheet Excel File to Separate Alteryx Database Files

[CandidDataScientist]

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? 

[CandidDataScientist]

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

[JoshKushner]

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]

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.

[CandidDataScientist]

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]

No problem, here's the main workflow

[Carlos_A · Accepted solution]

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]

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.