community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Combine and Output multiple Excel Workbooks with same schema, Append to Output

I need to combine multiple workbooks with the same sheets (schema) into one output file (.xlsx). I then want to be able to add another workbook to the directory and run the workflow without overwriting any data which has been added to the previously output data.

 

For example:

- Sheet 1 and Sheet 2 are in a directory and need to be combined (2 sheets: Name & Cost)

s1 name.pngs2 name.png

s1 cost.pngs2 cost.png

 

- The Output should look like this

 

Output Name.png

Output Cost.png

 

- The output may will to edited as shown below (Approved Field)

 

Edit.png

 

- Sheet 3 then becomes available

s3 Cost.pngs3 Name.png

 

- I want to add Sheet 3 to the previously output data, without overwriting the edited information (Approved Field). Appended Data shown in red

 

Combined.png

 

Any help greatly appreciated.

 

I have been looking at the following articles, but haven't been able to create a workflow/macro which completes the process as expected:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-T...

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

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/using-dynamic-input-on-excel-files-wit...

 

Many thanks

Highlighted
Alteryx
Alteryx

Hi @StephenRodgers,

 

not sure if I got what you want to achieve right, but have you tried to set up the Output Tool -> 3. Output Options to "Append To Existing Sheet"?

 

Attached a sample workflow. It will give you an error the second time you run it, as the first 2 Output tools are set to "Create New Sheet", but at least you won't lose the output you created the first time you run it.

 

Capture.JPG

Asteroid

Hello,

 

For the input, have you tried the crew macro?

 

https://community.alteryx.com/t5/Engine-Works-Blog/Crew-Macro-Pack-2016-Q2-Release/ba-p/26482

 

CaptureCrew.PNG

 

 

For the output, load in the old file and just union the new stuff to it (and rewrite data with drop sheet). Or like the post above, append the data (this one makes me more (unjustifiably) uncomfortable because xlsx layouts can be weird but... it should work just as well.

 

Personally I would also have a batch file that moves all successfully processed xlsx files to a Processed folder at the end.

Hi Will,

 

Thanks a lot for your response, I've been able to get the input side of things working. I'm now having a bit of trouble with the Output and was wondering if you could explain both options a bit more if possible please. My issue is that I want to append to my newly created sheet, but I'm getting duplicates whether or not the 'Approved' field has been edited.

 

For the output, load in the old file and just union the new stuff to it (and rewrite data with drop sheet)

At what point are we loading in the 'old file'? with a union will I not still have the issue of duplication i.e Approved empty and fill since last run.

 

Batch file that moves all successfully processed xlsx files to a Processed folder at the end

I like this idea but am not completely sure what you mean, is it a batch macro? Could you give me an idea of how it might be constructed.

 

Many thanks.

Labels