Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Output to Excel error

MsBindy
8 - Asteroid

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.

47 REPLIES 47
Underdog
5 - Atom

Is there any update on this? I have the same on 2 of my workflows which is quite painful

Kenneth_L
6 - Meteoroid

same problem here

Kenneth_L
6 - Meteoroid

It appears that the solution for my case is to delete all errors in name manager within Excel. Hope it helps.

Having similar issue with these two consecutive Excel error prompts.   I am running a routine workflow therefore overwriting .xlsx output file. When I choose to create a new sheet I avoid the prompt.  I would prefer to overwrite the sheet but want to avoid possible errors/issues and ensure my output values are correct.snip.JPG

 

snip2.JPG

 

vladimir
5 - Atom

I'm having this issue too. 

 

@Alteryx, any solution, please? This thread is opened for year and a half with the "solution" being a hot-fix macro created by a user. 

 

UPD: in my case it's the same issue as jacqgeek had: I write in tabs and SheetName is limited to 31 symbols for xlsx. I've added one more module creating a SheetName column with truncated strings in my data and put it just before the output module. 

 

IMHO, this should be integrated into the output module by Alteryx team.

32bit
8 - Asteroid

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.

 

  1. 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.
  2. 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.
  3. Inside the zip document, go into the xl subdirectory and delete the calcChain.xml file.
  4. 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.

 

 

jgilbert
5 - Atom

I am also getting a similar error, though in my case: the Excel output has no macros (.xlsx, not .xlsm), I am creating a new sheet rather than overwriting, and my sheet names are well under 31 characters. Unless I'm mistaken, the explanations above in this thread do not cover this case. Curious whether anyone else has seen this error under these conditions. 

32bit
8 - Asteroid

@jgilbert The error is not related to macros, but rather the breaking of the calc chain. This will happen if Alteryx deletes any cells (or worksheets) that have formulas in your template. If this is not the case, could you post more information about your error or template setup?

jgilbert
5 - Atom

@32bit wrote:

@jgilbert The error is not related to macros, but rather the breaking of the calc chain. This will happen if Alteryx deletes any cells (or worksheets) that have formulas in your template. If this is not the case, could you post more information about your error or template setup?



 thanks for your reply. Is this the case even when the message I am getting after repairing is "Repaired Records: worksheet properties from /xl/workbook.xml part (Workbook)" rather than the similar one about calcChain.xml? It is actually not even my intent to use a template - I just need to output the data table as is from Alteryx as an Excel file. Currently using a non-existent file name as a placeholder in my Output Data config, then taking the file/table name from a field in the table. So it doesn't seem like I am using any template, let alone one including formulas, but please correct me if I am wrong. 

32bit
8 - Asteroid

workbook.xml would be a different error than the one in this thread. I'm afraid that I wouldn't be able to attempt to troubleshoot it unless I could reproduce it and inspect the file. workbook.xml contains things such as a list of worksheets in the file, the default theme version, workbookview information such as window width and height, and could contain other information. It's like the table of contents of the file. I would suspect your error would be the result of it pointing to something that is missing such as a non-existant worksheet, but I can't confirm from here.

Labels