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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

How do I output to an Excel template file?

Alteryx_KB
Import
Created on

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
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

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!

 

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.

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!

Meteor

Is this possibble through a published app?

ACE Emeritus
ACE Emeritus

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

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

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

Meteoroid

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?

Moderator
Moderator

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,

Meteoroid

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

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

Meteor

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

Meteoroid

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

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? 

Atom

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 🙂 

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

 

 

 

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

 

 

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?

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).