Alteryx Designer

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

Excel output to formatted sheet

Highlighted
8 - Asteroid

Is it possible to retain some of the formatting from an existing sheet?

 

Basically I have a sheet that I would like to overwrite daily, but in several columns I have Data Validation drop down lists. I do not want those touched.

 

In my workflow those fields are Null(), I have tried to overwrite sheet and (with and without skip field names) but it overwrites the data validation.

Append may work but I do not need previous days data. I tried to simulate if after working the document I deleted the rows from that day, so it is only the header row, then re-ran, it appends on like row 1000. It keeps the drop downs though!

 

 

Any suggestions?

 

 

Highlighted
Alteryx
Alteryx

Hi @wmcclure ,

 

Depending on what version of Designer you have, you can preserve formatting on overwrite if you put in a range when you output. If you have the same layout everyday, I'd suggest putting the columns that you don't what overwritten at the end of your table. 

 

In the attached workflow, I only brought in the fields that I wanted to update by naming the cell range (I could've also created a named range in excel). I then did any transformation I needed to and then I output directly back into the same sheet. I again, selected the range that I wanted to output to and checked the box that said 'Preserve Formatting on Overwrite' in the Options.

 

Original xlsx (I didn't bring the Validation field into Alteryx):

AshleyL_0-1587483765842.png

 

 

 

Hope this helps!

Ashley

Highlighted
16 - Nebula

Hi @AshleyL, I just upgraded to 2020.1 today and noticed these enhancements. This is a game changer for people who previously had to jump through hoops to keep formatting in their excel output files. 

 

You can now Preserve Formatting on Overwrite when overwriting a sheet or range via the Output Data tool Options. In order to preserve formatting, you have to:

  • Select Overwrite Sheet or Range via Output Options.
  • Specify cell ranges in the output file path.

 

Perhaps I should have looked at the Release Notes earlier!

Highlighted
Alteryx
Alteryx

@DavidP, it's really awesome! Even if you have conditional formatting set up on a column in the range, it will keep that conditional formatting & update the field condition if something is changed.

My workflow changed the second row from 'Not Started' to 'In Progress' and the colors updated:

AshleyL_0-1587486970083.png

 

Highlighted
8 - Asteroid

@AshleyL 

 

This is what I was looking for. Thank you for your input!

 

Unfortunately in our production environment we are using 2019.3.5

 

So I have even more reason to push for updating Alteryx across the org!

Highlighted
16 - Nebula

@AshleyL, it sure is! This is super exciting! 

Highlighted
5 - Atom

Hi @AshleyL,

 

I am on the most recent version of Alteryx. I noticed in the workflow that you attached that you chose "Create New Sheet" as your output option, but when I try to use this option to overwrite on an existing sheet Alteryx will not allow it. When I choose "Overwrite Sheet or Range," with my range specified A7:D30, it overwrites all columns in those rows, not just columns A-D. Also, when I use this output option I get a message that my file has been damaged.

 

Do you know what the issue is here?

 

Thank you!

 

Parameters.JPGCorrupt Message.JPGOutput Issue.JPG

 

 

 

 
 
Highlighted
Alteryx
Alteryx

Hi @NewUser2 ,

 

Try un-checking 'Skip Field Names'. I see a few community posts that say that this occasionally has been corrupting excel files. Since the new version of Alteryx can preserve formatting, it's not going to change how your headers look.

 

If this doesn't work, we've recently started a new initiative called the Virtual Solution Center: https://community.alteryx.com/t5/Alter-Nation-Blog/Introducing-the-Virtual-Solution-Center/ba-p/5440...

You can schedule 30 minutes with an Alteryx team member to assist you with configuration of this functionality!

 

Best,

Ashley

Labels