Writing to pre-formatted Excel in specific range with "Overwrite Sheet (drop)"

[AkisM]

'I have 2 different environments with the same version of alteryx running on each, the exact same routine running on each, and in 1 environment the excel output breaks (when opening it it says "a problem was found - do you want to attempt to fix - clicking yes does the "fix", but my target sheets now all empty instead of showing the output data in them) - attached screenshot as "UnexplainedErrorNotFixed"

In the other it doesn't break (when opening it still displays the "a problem was found - do you want to attempt to fix" message, but clicking "yes" actually fixes the excel as expected, and my data appears where it should be) - attached screenshot as "ExpectedErrorFixed"

Environment A: Alteryx Server 2020.4.5.12471 (Local VM) - Behavior is as expected

Environment B: Alteryx Server 2020.4.5.12471 (Azure Server) - Output excel breaks

For both environments, the routine used is exactly the same, the same data, created by the same Alteryx Designer version (Alteryx Admin Designer 2019.3.5.17947)

My routine is reading a pre-formatted excel with data in it from a Shared Drive that is visible to alteryx, performs some calculations, and then enriches that same excel with new data, in specific sheets, specific ranges. I am attaching a screenshot of the specific output settings I'm using.

Can you give me some pointers as to what factors may be causing the exact same routine to behave differently in 1 environment than the other, so as to drill down on the potential issue? I'm also attaching a simplified version of the routine "Example of issue.yxzp"

Thanks in advance.

[csmith11]

This is a common issue discussed all over Alteryx Community with Various Solution reached.

My opinion is that it has less to do with the configuration you are using in Alteryx and more to do with the current unreadable/unwriting state of your Excel files. Something somehow has been corrupted.

Something I've done in the past that resolved the issue for me was to create a new Excel file from scratch with the appropriate formatting. This new file became my write to template. This new file has never been written to by Alteryx and the first few executions of Alteryx never generates an error for me.

To prevent the issue from ever arising again, I used the run command tool to  make a copy of my template in the output file path and I write to this copy, thus never corrupting  my Template Excel File again.

I'll edit and add a link to how this can be accomplished shortly.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Create-multiple-excel-sheets-with-two-...

[csmith11]

Please let me know if starting with a fresh output file still shows any issues. If so, there's a few other solutions you may be able to use. But starting fresh has been a solution for most others on the community as well.

[AkisM]

I can't start with a fresh excel file. My use case specifically requires that I write to a template (which is in fact a fresh file), but then all subsequent runs of the workflow must write to that same existing file which already has data in from previous runs.

[csmith11]

So a couple of thoughts that still may not address the question but may resolve the issue:

The Question: Why different results for different environments? To this question, I don't have a good explanation..

The Issue: There was a bug related to writing to excel range when skipping field names that would generate this kind of excel corruption. 

Solution: Uncheck the Skip Field Names option.

My friend @fmvizcaino addresses this issue here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-File-Corrupt-After-Writing-to-Ex...

Which version of Alteryx are you using?

New version 2020.2 has this bug fixed already

[AkisM]

@csmith11 

I am aware of the "skip field names" bug, however for our use case unfortunately we do need to skip the field names. Currently we use a 2019 version of alteryx so the bug exists. That's why I am trying to ignore the fact that the excel breaks (since clicking yes fixes it) and trying to focus on why the excel is fixed correctly in one environment but incorrectly in the other...

Right now upgrading to 2020 isn't yet possible as per my organization's instructions.

[csmith11 · Accepted solution]

One last thought: Still not the answer. 

If the only reason you need to skip headers is to avoid overwriting formatting:

Have you considered using a hidden row like below:

You start writing to row 5 instead: This unformatted row can then be hidden as show above. And the  "skip field names" can be unchecked. Corruption avoided. I used this solution on a client facing Delivery and it worked out great for their department.

This would allow you to no long need a custom field mapping as the mapping can use the hidden row instead.

If needed the Append to Existing Sheet can be used as well.

If this is still not a viable solution: Please consider reposting your question noting the work arounds we already discussed so other community members don't present the same solutions. This might generate additional participants in the discussion.

[AkisM]

Thanks @csmith11 , that actually looks like a potential solution that if it works as expected is acceptable, and bypasses the issue. I tested it a couple of times and it seems to work fine, no corruption or anything. Further testing is needed with more data to confirm it's a robust workaround but so far it's looking good.

[csmith11]

@AkisM Glad to hear its a possible workaround! 

Hopefully it works out for you! Please consider marking the above as the Solution so other users can find it quicker.

Obviously, once you are able to upgrade to the latest version of Alteryx the Skip First row bug will be fixed. But in addition to that you'll have the following setting available. Which gives you the ability write to the header row without losing your formatting. 

[apathetichell]

one addendum to @csmith11  's very solid explanation - you want the output option to be "overwrite sheet or range - preserve formatting"