Combine Multiple excel worksheets in 1 excel file with another as separate tabs

[D142627]

Hello,

I need some guidance around a workflow am working on where in I have 2 excel output files with multiple sheets and I need to merge it with excel file as separate tabs. However, the final output filename contains wildcard. Can someone please assist on how can I achieve this 

Regards

Mustafa

[clmc9601]

Hi Mustafa @D142627,

So each of your output tools is writing multiple xlsx files? Are you already using the "Select Take File/Table Name From Field" option? By using this option, you can tell each output tool to write to multiple tabs on the same file, multiple files with the same tab, or multiple files with different tabs. Heads up that the "Change File/Table Name" setting on xlsx output only changes the TAB name, not the workbook name.

[D142627]

Hello Claire @clmc9601 ,

Thank you for your guidance. So basically the original requirement was to write the output in the input file itself since it creates around 7 worksheets. One point to note is the Input file name is not static - meaning every month the input file is prefixed with date time stamp. Hence I have used a wildcard to pull the file in Alteryx. However, when I use a wildcard to open the same input file and save the resulting output from the workflow into multiple sheets, it gives me an error 'Error opening file: *abc.xlsx'. Due to this I had to create a new output file with a static name to save these 7 sheets within that. Now am trying to merge the input file which already has 15 worksheets and the new output file with 7 worksheets. I have attached a snapshot of the error which I recreated using dummy data  when I try to use wildcard in the output tool. 

Is there a better way of doing it?

Regards

Mustafa

[mceleavey]

Hi @D142627 ,

can you provide some mock data to show what you're trying to do?

M.

[D142627]

Hello Chris @mceleavey,

I am attaching this workflow with dummy data. The output file that I have used is a new static file. Instead I want to merge with the Input file as separate tabs with a condition that the Input file name will have a Wildcard.

Regards

Mustafa

[mceleavey]

Hi @D142627 ,

This is a request that comes up a lot, and it's a neat trick once you know how.

I've attached the workflow for you. You will need to amend the paths, but basically I'm using a Directory tool to read in the files in a given location. If there are multiple you can use the sort and sample tools to take the latest one, or whatever you need.

I then have a couple of tools which allow you to read in the sheet names from every file, then combine them and load them all in simultaneously:

I then split them out and do the individual processing you were doing before:

(I'm ignoring the horrific IF statements in there...)

I then smoosh them together, create the output paths including the sheet names, then wrapping the output into a batch macro. This prevents Alteryx trying to write to a file twice at the same time.

I hope this helps,

M.

[D142627]

Hello Chris @mceleavey,

Thank you for the solution however I am unable to import the same in Alteryx. Seems to be version issue. I am using V.2020.3.2.

Below is the error message I am getting :

Failure to Import C:\Users\D142627\Downloads\mceleavey_solution.yxzp.
There was an error opening "C:\Users\D142627\Downloads\mceleavey_solution\mceleavey_solution.yxmd":

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

[mceleavey]

@D142627 ,

yeah, you need to upgrade your version of Alteryx.

However, you can also mess with the versioning in the XML itself to get around this.

I recommend THIS BLOG by @atcodedog05 that walks you through this.

M.

[D142627]

Hello Chris,

Thanks for the Blog link, I was able to downgrade it and today I will be testing the workflow. Will soon post an update.

Thanks a ton!!

Regards

Mustafa