Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

How do I output to an Excel template file?

Alteryx
Alteryx
Created

How do I output to an Excel template file?

It is possible to output your data to an existing Excel document that already has modified formats and column names. For example, the below Excel file has existing data in the first 4 rows. If you wanted to add addresses to this file while keeping the first 4 rows, the first step would be to highlight the area you want to write to. If you don’t know the exact length/width of your data, I would recommend going large:


Once you have your desired area highlighted, right-click and choose the Define Name… option:



A popup box will appear, enter in a name of your choosing and click OK:


You also need to make sure that the sheet you are saving to doesn’t contain any spaces in the sheet name. Once verified, save the template and close out:


Below is an example of the sample data that will be added to the above template:


In Alteryx, use a Input tool to point to the data you would like to use to update the template file:


In the Output, you will want to choose the template file, which will cause the below message to appear, choose yes to overwrite:


When saving to Excel, the below window will popup, enter the name you used for the range you highlighted in the template file:


After clicking OK, the Output configuration area will populate. Change the Output Options to Delete Data & Append:


You can now run the module. Once the module is finished, you can open the updated template file, you should see your previously formatted rows/columns plus the new data you wanted to append:



If you set a format to the range you named (color, text style, bold, etc), Excel will keep it so that the data you are writing to the file will appear with the specified format.

Comments
5 - Atom

Nice, thanks! Is it possible to do the same (Delete Data & Append) to a sheet with a formula? For example, let's imagine that I have a formula on column J that uses data from column D. When automatically running alteryx will the formula automatically refreshs? I tried it here but an error on Excel appears, saying it finds unreadable content.

 

Thanks

7 - Meteor

I have had success doing this by outputing to excel and from there linking the cells to the final sheet I would like to use. 

 

Before you save your worksheet you will want to break those links to prevent the next person that opens that sheet from accedentally having those linked cells update. (Data, Edit, Break links)

 

 

This way you will be able to use your date in whatever formulas or template you like. 

 

Thanks!

 

5 - Atom

Okay, got it! So basically the formulas have to be in a different sheet from the one that the data from Alteryx come, correct?

 

Thanks!

But, just to make sure... I'm talking about having the formulas before exporting data from Alteryx to Excel, so when I export the data, it will automatically calculate what I want.

7 - Meteor

Yup!

 

Its not the cleanest process in the world but it works. 

 

You will need to write a formula in column J that uses the new data in the linked cell pushed from Alteryx.

 

Someone else may have another way of doing this however this is simply what has worked for me as defining cells within a complex template of an existing worksheet for me did not work that well. Exporting and then linking the cells seems to work fine, albeit an extra step. 

 

If you can provide an example I may be able to help further and get you something to work with.

 

Thanks!

7 - Meteor

Is this possibble through a published app?

Alteryx Partner

Hi!  Great post, appreciate the detail!  Outputting to excel templates is something we do often, and frequently have trouble with. Two questions:

 

1) What is the purpose of the step to name the defined range "Target"?  Does this get used by Alteryx somehow?  Seems like a neat shortcut.

 

2) Any tips on what to do if suddenly you need to add a new column to this template?  Our tests work well if we remove a column, the overwrite continues to work; but if we need to add a new column, we receive errors and a blank output file.

 

Thanks!

Alteryx Certified Partner

Hi !

 

We use a lot Excel output for reporting, but we have a problem concerning the format of the Excel.

For example, we create aexcel report with a layout :

 

1.JPG

 

When we inject data's in the Excel sheet, the layout is deleted :

 

2.JPG


Can someone help me with this problem ?

 

Thanks a lot 🙂

 

Olivia

5 - Atom

hi,

 

I would like to write two different results to the same excel file but different sheets. He writes down the first results but for the second result I get the following error:

"Unable to open file for write"

 

The file is opened to write down the first result and therefore cannot be used to write down the second result. How can I change write multiple results to the same file but different sheets?

 

Thanks.

 

Lisanne

Alteryx Certified Partner

Hi,

You just have to add a 'Block until done' (Developer) tool before the output tools. 

 

 

BlockUntilDone.PNG

Alteryx will execute the output tools one by one and you will not receive the error message anymore.

 

Kind Regards,

 

Olivia

7 - Meteor

Hi,

is it possible to sidestep the 'append field map' when outputting to a excel template?

Im using a macro to loop through various selections and the outputs will have different headers, so I want to output all headers each time in the order they appear.  Currently im getting the error all fields must be mapped to a destination field name.

 

Thanks

7 - Meteor

I'm using Alteryx 10.6 and having trouble following this example.  I don't see an option to choose 'template'.


@Alteryx_KB wrote:


In the Output, you will want to choose the template file, which will cause the below message to appear, choose yes to overwrite:


The Output Options no longer have 'Delete Data & Append'.

 


@Alteryx_KB wrote:

 Change the Output Options to Delete Data & Append:


Any thoughts on how to accomplish this in the newer version?

Alteryx
Alteryx

Hi @Mychele,

 

 

Thanks for letting us know that you are having trouble with the sample. 

 

The template file is the existing formatted xlsx file that you want to output your data to.

 

You may need to install the legacy excel driver in order for 'delete data and append' to show up.

You can download it from here

 

More on legacy drivers in this post 

 

Please try downloading the driver and you should see that option pop up.

 

Thanks,

7 - Meteor

To close the loop on my question, I needed to choose File Format: Microsoft Excel Legacy.

7 - Meteor

Is it possible to use this method to output different reports to the same excel just within separate sheets?

 

I have 2 sheets, both of which are formatted as templates and separate reports to output to each. Can anyone help?

 

TIA

7 - Meteor

This doesn't appear to work for *.XLSM templates. Does an XLSM solution exist Thx

6 - Meteoroid

I am looking for this functionality for .XLSM file. Is there any workaround for the same?

6 - Meteoroid

 Hi,

 

I'm fairly new to alteryx and I'm not sure if anyone was able to figure it out or was answered else where. I have looked at numerous post but just can't seem to find a solution to append where the format does not get overwrite. Is there another solution? I basically just want the output data(2 excel data joined) from Alteryx to be exported to a formatted excel document.

 

Any help is appreciated.

 

Thanks!

 

Alteryx Certified Partner

Hi Mrpug !

Finally we are working with hidden sheets. We export the results in this hidden sheet (which is not formatted), and we add formula from the formatted excel sheet to the hidden sheet.

Alteryx Partner

Any idea when the "Delete Data and Append" option will be restored? 

6 - Meteoroid

I am also asking to restore  "Delete Data and Append" for .xlsx files. At the moment it is available for .xls files, but it is not our company standard. So, please restore this asap 🙂 

8 - Asteroid

@OriginalAdrian

Regarding your post on exporting data from Alteryx and having excel formulas in a different tab access the exported data, I have a few questions.

 

Are you the user of the excel files? 

 

Or do you have other users and therefore, need to lock down the excel formulas to keep them from being changed?

 

Thanks

 

 

 

5 - Atom

When I use the .xlsx Legacy file type I lose the ability to Skip Field Names. 

Alternatively, when I use .xlsx as my file type, the prior rows of data are not deleted and then I get an error message when opening the output file.

 

Any suggestions for other workarounds?

ErrorMsg2.png 

ErrorMsg.png

 

 

5 - Atom

I follow your instruction but Instead of delete and append data in existing sheet w, It's create new sheet the same name but plus number "Sample1"

Please help?

5 - Atom

I tried the workaround of using an *.xls file to get the "Delete Data & Append" option.  After selecting the option and running the workflow, I receive the Output Data error message "The 2017-10-10 Windows security patch prevents the Jet driver from reading *.xls files; install the 2010 32-bit Access driver' which my Company will likely NOT do. 

 

Please restore the option to xlsx files.  Seems so much easier then scripts and complicated ranges/formulas just to accomplish the same thing (especially since they are currently beyond my skill set).

8 - Asteroid

@Alteryx_KB Is there a way to replicate the output file and save to a new one? The business case is I need to save a daily version of that export based on a current workbook template. Only the names ranges in two of the many sheets within that workbook is to be updated and saved as a new file with date updated for the file name.

8 - Asteroid

Actually, answering my own question as I later figured it out, the Blob in and Blob out tools work perfectly in my case.

additional readings- please try to search Alteryx: Excel templates with Blob tools. I cannot post the link here, I don't know why.

 

it's from intersectionsandoverlaps.wordpress.com

5 - Atom

This article really helped me to find the solution to what I wanted my workflow to achieve, thanks to SciencePeak for referencing the Blob tools.

 

For reference if anyone has the same issue: I wanted to output my data to three separate tabs within the same pre-formatted excel template and save in a different folder with a unique file name.

 

I was able to pick up my excel template using the Blob Input, create a new unique URL/filepath with a formula tool (including DateTimeNow & Username) to then copy the template to using Blob Output.

 

I then added a Block Until Done before the Blob Output, to ensure the new renamed template was ready to output to, and appended the new URL field to my output data.  This URL field, with the addition of sheet name and output range in another formula tool, can then be used in the Output Data tool to 'Change Entire File Path' and 'Overwrite Sheet or Range' while 'Preserving Formatting on Overwrite'.

Alteryx
Alteryx

@juliebb 
The issue that caused the Excel file corruption was DE18410, fixed in 2020.2.