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

Alteryx designer Discussions

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

Exporting to existing excel file

Hello,

 I have had a lot of luck with Alteryx, but I have a question about outputting to an excel file that is already in use.

What I want to do is export to a specific tab in excel.

Can I do this without deleting the existing table?

I just want to replace the existing data.

 

Also does anyone know how to get it linked to a pivot table?

Alteryx Certified Partner
Alteryx Certified Partner

@jonathangonzales,

 

When you use the output tool, you should be able to output to an existing Excel file and choose the tab for your target.  You may not (NEVER NEVER USE THE WORD CAN'T) have that excel file open if you want to read or write to it.

 

Once you select the Excel file, you'll be prompted to specify the table (or worksheet) name.  You can choose #3 your output options to:  Overwrite Table (drop), Append Existing, Delete Data & Append, or Create Table.  If you want to append data, you can do that and not touch the existing rows.  The next option in the configuration #4 is the append field map (click the ...) and you'll see if your field names match the Excel sheet field names.

 

If everything is kosher, you'll be in business.  I like to start from scratch and create a table from Alteryx (my preference) and write to a sheet named:  RAW_DATA.  That's my cup of tea and I hope that you'll agree with my logic.  I use the RAW_Data table and generate a fine pivot table from it.  In Excel I set the pivot table options to refresh on OPEN.  Now, when I put real data into the file the pivot table always shows the new data as part of the pivot without requiring a user to refresh the data.

 

I then HIDE the RAW_DATA tab and it appears as though I've refreshed the pivot from Alteryx.  Almost as smooth as if I had used Tableau (which is a great option).

 

If you need more guidance, please let us know (maybe provide a sample set of data and a pivot table) and I or someone in the community will help you further.

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Using two files is not an option. I could do that, output to CSV and then have Excel with Power Query connections to an external file but I would have to send the two files to my colleagues which is not feasible. 

 

I realised that I think it's only when Excel file has Data Model built-in then Alteryx must be doing something incompatible with Excel that causes it to break. If I give Alteryx the same output file it overwrites the worksheets correctly.

 

Another thing is that if I only leave Excel file with Power Query connections, it overwrites the worksheets but it looses the table names which are referenced in Power Query. 

 

Any help to make the Excel<->Alteryx integration tighter would be appreciated!

First a workaround on keeping file so you can update it.

 

  1. Mark the Excel document as read only within Explorer (this will stop Excel applying a lock)
  2. Add a Run Command to the Workflow Events Before Run to Unmark As Read Only
  3. Update the RawData sheet
  4. Add a Run Command to the Workflow Events Before Run to Unmark As Read Only

To do updating the pivot table one technique I know works (but is fiddly), is to use a dynamic named range and reference this as the input to the table and make it refresh on open.

 

I have attached a demo to this of both of these techniques.

Asteroid

I just realised that I get the same problem even when creating new flat csv files (could it be Office 2016)?

If Excel has the file open in read/write mode it locks it so you cant change it.

 

True for all version of Excel. Hence workaround suggestion of making file readonly and only unmarking within Alteryx flow

Asteroid

Hey @jdunkerley79, what if I wanted to output Log files to the same location-- does your process allow me to retro-actively name the previous file (e.g. "ErrorLog-[Time stamp]")  so I can accumulate files without repeatedly overwriting the same one?

 

EDIT: Also, I keep getting a 'Error Opening file: specified path not found' error, although the email attachment is successfully emailed. Is there something I could do to reduce errors like this or is it a non-issue because the app is still working as expected?

 

Thanks!

 

 

Atom

Mark,

 

I currently do a very similar process with my daily reports. Input the data into Excel and have all my pivots set to automatically refresh. That works well...most of the time. I noticed that when I open the Excel Document on a mobile device, the pivots DO NOT refresh. 

 

I think the work around on this would be to open the file and save it. Is that something the Alteryx could do? If I add that step in my workflow, it would basically be refreshing the pivots before sending out the report via email and therefor mobile device users would also see a refreshed pivot. 

 

Thanks in advance for your help.

Jeff

Asteroid

Block until done solves the problem of overwriting the file easily whithout causing issues. Did you try? 

Meteor

Yes,

 

Just use the overwrite Tab option. Pivot Table user will have to click 'refresh' button when they want to see updated data.

Labels