Output to Excel error

I have several workflows that are sending output to an Excel file. When I open the Excel file I always get a message "We found a problem with some content..." Do you want us to recover as much as we can?...." I always answer "Yes" and then I get a message about the repairs to the file which always says "Removed Records: Formula from /xl/calcChain.xml part"

I have no idea what any of that means. I'm able to use the excel file, and run the macros in it. However, I'd like to avoid those messages if I can before I hand these files off to someone else.

Output

Accepted answers

32bit

Hey, everyone. I'm having the same problem, but I have another solution to offer. For anyone that wasn't aware, an xlsx and xlsm file is just a zip file of XML documents that define the Excel Workbook. Alteryx is using this to output data to these formats without having to automate and Excel.exe object. It seems like updating the calcChain.xml part of the file is an oversight on their part.

Follow this procedure to prepare any templates you are dumping data into and it will temporarily resolve the problem until you have to open and edit the template again using Excel. This means that you are ok to keep using Alteryx to dump to the same file without worries. I am not able to upload any screenshots due to my firewall at work.

Open the xlsx or xlsm file using your zip file editor. I use 7-zip. You may have to temporarily rename the Excel file into a zip.
In the root directory, edit the [Content_Types].xml file. There will be an entry for "<Override PartName="/xl/calcChain.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.calcChain+xml"/>" Delete this part of the XML document. Save and replace it in the zip.
Inside the zip document, go into the xl subdirectory and delete the calcChain.xml file.
You're done. Rename back to xlsx or xlsm if you had to rename it.

Now you can use this to output data from Alteryx without the file repair popping up. From what I can tell, there are no drawbacks to removing the calcChain. It is an optional part of the file defined in the SpreadsheetML specification (last paragraph) so it doesn't have to be calculated later. Formulas continue to work correctly in my test.

All comments

MarqueeCrew

@MsBindy,

If you simplify the workflow to take data from a text input tool and write it to the output file, you could export (options -> export workflow) and post the yxzp file to the community for us to take a look at. Macros in the workbook are something that I avoid. I like a clean xlsx file and am weary of xlsm files.

Cheers,

Mark

MsBindy

Thanks...Let me trim it down some. I'm kind of new to this and have created a monstrosity of a workflow. I'll try to make a much more simple case. The reason for the macros in the outputted Excel files is that I'm formatting them to send out to a client. The reporting tools in Alteryx are currently way above my current skill level.

jacqgeek

How long is your output Excel sheet name? I got the same error due to running out of the maximum of the length of Excel sheet name.

MsBindy

I don't remember how long the sheet name was, but that's an interesting idea.

dylanStone1

I'm having the same problem when my workflow tries to overwrite an existing excel workbook file. I have a workflow where I union some Text Input values with the rest of the values I'm trying to output. If I configure the Output Data Tool to Create New Sheet, it writes the output correctly (however, it still has the error message "We have found a problem with the content..." when opening the excel file even after the very first time running the workflow). However, if I configure the Output Data Tool to Overwrite File (Remove), then in addition to the excel error message upon opening said file, the only values written to the excel file are the test values from the Text Input Tool, and none from the actual workflow. This is a workflow that we plan to run once daily and I'd like it to be able to overwrite the file with the updated values each time I run it rather than have to create a new file every time.

MikeMKH

I know I am very late to the party but I had a similar issue today with an Excel output that would give the "we found a problem with some content" error message.

The problem with the workflow I was working with was the FixedDecimal Type seems to be causing the issue when the Size is large, in my case I had FixedDecimal Sizes greater than 40.13 being passed to the Excel output. I resized the offending Fields to be FixedDecimal Size of 40.13 and now the Excel output does not have any issues.

Hopefully this will help someone else.

bmurphy

Typically this error comes from sheet names longer than 31 characters including spaces. So if you have take field name set for the sheet name or are using a formula to build the filepath and sheet name that's too long it'll give you the corrupt errors upon opening the file in excel.

yaoerpi

This happens to me too when I try to output to a name range or specific cells - none of the solution above is working for me. It hurts when opening up files with the pop-up error, making it seem like corrupted.

Amaya

This fixed a similar error I was having too.. I shortened the sheet names up where the exported file truncated the tab name to.

Fixed it! Thank you!

Brennan

For me the fix was going into the source sql and casting the field to float. Then Alteryx interpreted the datatype as Double, then my excel issues all went away. I would stay away from anything that results in Fixed Decimal, clearly there is some undocumented limitation to this datatype.

PeterAP

Apologies if someone else has already mentioned this, but I find this error normally occurs for me when the Excel sheet names specified in Alteryx are too long and are truncated in the Excel File.

When I shorten the sheet names so they aren't truncated in excel this error disappears.

Hope this helps

Gina2021

@PeterAP & @DennyChan Thank you both tons! I shortened my tab names and took off the '.

My filepath now looks like:

FY22\InvoiceDispute.xlsx|||1$a1:h9

FY22\InvoiceDispute.xlsx|||1$a11:h24

FY22\InvoiceDispute.xlsx|||1$a26:h38

FY22\InvoiceDispute.xlsx|||2Left

FY22\InvoiceDispute.xlsx|||3Inv

FY22\InvoiceDispute.xlsx|||4Dis

I also added Block Till Done where two lines were coming out of the same tool, going different places. I let streams going to tab 1 finish first, and the rest were lined up with Block Till Done output 2 or 3.

mystasz

I hope this can help something in the future....

I was just having this issue and it seemed to be related to how I was creating the FullPath in the Formula tool, specifically the sheet name.

Before (errors): "C:\Users\UserID\Box\ + [FileName] + "_Summary" +'.xlsx|||' + "'"+"Record Summary"+"$'"

After (no errors): "C:\Users\userID\Box\" + [FileName] + "_Summary" +'.xlsx|||Record Summary'

KGT

Same as @mystasz & @DennyChan

I just linked to this while looking for a solution to the issue regarding workbook properties. I have always referenced sheets using 'SheetName$' when writing out from Alteryx, however when I say always there is a chance that a change in 2020/2021 wouldn't have crossed my path as I don't do much of actually building workflows with output from scratch these days.

When I looked at the workbook properties for a previous file that worked, and compared to the new one that didn't, that reference was the only difference and referencing it without the quotes/$ has it work fine.

On the right in the below screenshot works and on the left has the issue. I'm now curious to know whether the 'SheetName$' was something that was needed for XLS but not for Xlsx or something like that, because it's odd that the solution is to remove something that used to be the standard and required in some cases...

notepad++_yEw59yTpdc.png

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703