Multiple Excel Files to One Excel File with Multiple Worksheets

[WayneWooldridge]

Multiple files can easily be combined into a single Excel file containing multiple worksheets (or tabs). The format of the files do not need to be the same; they can be completely different.

In the example attached to this article, two files, a Customer .csv file and a Store .xlsx file, are output to a single Excel spreadsheet (.xlsx format) with one tab for Customer and another for Store. These files contain different data elements and have different layouts.

The first step is to create a new field in each dataset (e.g.: 'TabName') and set the expression to the name you want each worksheet to have. In the attached example, 'TabName' is set to 'Customer' and 'Store,' respectively for each dataset.

When you get ready to output your data, use an output tool for each dataset and configure them in the same way. The file format is 'Microsoft Excel (*.xlsx)' and when prompted to select a worksheet for the output, select 'Sheet1'. Check the 'Take File/Table Name' checkbox located at the bottom of the configuration window. Select 'Change File/Table Name' in the dropdown and under 'Field Containing Name or Part of File Name' enter 'TabName'. You probably don't want TabName in your final output, so uncheck the 'Keep Field in Output' checkbox.

[Pat_Purcell]

Do you know if there is a way to order the tabs in the output file?  I've tried adding .01, .02.... before my tabs names in the formula, but it still seems to be placing them in the order the operations are being run.  I also have a block until done before each output tab, if that effects it.

[Usamah22]

Hello,

I am trying to do this but my output node is showing different options to the example. i don't have the "create new sheet" option. How can i do this?

[Ross1875]

@Usamah22  - I think the issue is that you have file format set to .csv (which cannot have multiple sheets)

Change it to .xlsx to resolve this

[iocana]

Hi, this works for me, however when I re run the workflow it says that the sheet already exists. Then I change the output options to Overwrite opposed to Create a New Sheet but then is not working and sheets are output into different workbooks

Any idea?

thanks

[Ross1875]

@iocana , could you post either a snip or a safe version (i.e. without any sensitive data) to this thread?

Thanks, Ross

[iocana]

Hi Ross 

many thanks for your reply! I already fixed it by changing it to "Overwrite sheet" opposed to Overwrite File

many thanks!

IRene

[brianvtran]

Hi,

I've attempted this method of outputting multiple tabs into one excel workbook but when I try to open the file, it says the file was damaged. In order for me to open it, it had to repair/remove the unreadable content. And when I do, it seems intact for the most part, but every tab I specified a unique tabname, it would write "Sheet1" as a prefix for every tab. For example if I wrote the TabName as "SampleTab" then it would write "Sheet1SampleTab" instead. I don't know why it does when the example shown queried the same Sheet1 but only kept the tabname. 

[Leisamac259]

I was able to do this magic, but I keep getting the error 32 "The process cannot access the file because it is being used by another process". Is there a way to get rid of this error message? Also is there a way to automatically put the date this job is run on the excel file name?

Thank you so much! This saved me so much time. I was able to connect 3 outputs into one excel file with 3 tabs. 

Leisa

[kliu4]

Super helpful tip to combine reports. Thank you for posting.