Excel output to formatted sheet

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?

Output

Accepted answers

AshleyL

Hi @GoldenDesign04 ,

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

Hope this helps!

Ashley

ExcelPreserveFormat.yxzp

All comments

AshleyL

Hi @GoldenDesign04 ,

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

Hope this helps!

Ashley

ExcelPreserveFormat.yxzp

DavidP

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!

GoldenDesign04

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

NewUser2

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!

Corrupt Message.JPG Output Issue.JPG

psk009

Hi there,

You need to create the path by using the formula tool and use that in the output tool by choosing the option take File/Table name from Field and select the "Change Entire File Path" (refer the snapshot). formula for File Path should be like: abc_path\fileName.xlsx|||'sheetName$a2:f500'

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167