Preserving Excel Formatting on Dynamic Output Files

[John_Abraham]

Hello ..I have a spreadsheet with multiple tabs, and I need to format the columns and copy the data into another spreadsheet. I created an empty test output spreadsheet with all the formatting. I have the below settings in the flow to overwrite the sheet, preserve formatting and it all works just fine, the formats are kept as I expected and data has been added correctly.

Since this a monthly process, I need to keep the input and output file names as dynamic file names. For this purpose, I created a template spreadsheet with the expected format and used file path replacement option with the file name dynamically generated. The spreadsheet is creating with the name and data that I need, but none of the formatting options are in the output file, though I checked the preserve formatting option..

Is there a way to preserve template format in the output spreadsheet in a dynamic output setting ?? If it cannot be done, then another option may be is to create a temp standard file with all the formatting needs, as in the first section above and then to run a command to copy the file to the necessary name ?? 

Thanks for your valuable suggestions.

[Luke_C · Accepted solution]

Hi @John_Abraham 

I think you'll need to go with the template approach. The run command method you noted would work, if needed reference my solution to this post that has an example. There is also another method using blob tools detailed here.

[John_Abraham]

Thanks @Luke_C . Your post and sample workflow will definitely help. I liked the blob tools option also. I plan to try both options. Is there any resource / performance considerations in either of these approaches ?? Thanks

[Luke_C]

Hi @John_Abraham, I don't think there would be any major difference in resources between the two. the blob option might be less set-up effort, although I've personally not had a chance to try it yet.

With run-commands, you will need to consider access permissions. For instance if you're going to try to put the workflow up on Alteryx Gallery, you need to make sure that the correct permissions are in place for run-commands to be executed on the server environment. This is totally possible but might require some work with the server/network team.