Alteryx has stopped working; removed records in Excel output

[amanda_e]

Hi -

Background:

I just started using Alteryx a few weeks ago, but I've run into an issue that I can't find an acceptable solution for on the Alteryx Community.  I created a somewhat complicated build that combines multiple different input data sources (e.g. about 10 Excel files, a .yxdb file, and a table from Oracle) and after going through a series of configurations, the results are output into multiple tabs of an Excel file.  The Excel output is a .xlsm (macro enabled) file because I have a lot of formatting that I need to do in the Excel output before sending it to client teams.  The output is also going to named ranges in Excel since I need the data to be output to row 12 to preserve formulas in rows 1:11 on some of the tabs. The actual Alteryx build includes Interface Tools so that the build will run for different clients, depending on the input in the Interface Tool

Problem:

I'm usually able to run the build successfully once or twice without any issue, including having the results output to Excel without any error message.  However, if I try to use the Analytical App to run the data, either Alteryx crashes (see error message below) and/or I get an error when I open the Excel output for "Removed Records: cell information...." (see error message below).  I confirmed that several rows of data are being removed from the bottom of some of the tabs when this error occurs, so just hiding the error message is not the right solution.  Also note that I sometimes get the same errors even when running the original build without using the Interface Tools in the Analytical App.

Attempted and failed solutions:

I've confirmed that the named ranges in Excel are correct.  I've confirmed that my macro in Excel is working properly.  I've replaced the .yxdb file to ensure it's not corrupt.  I've removed the cache tools I had in the build.  (I had cache tools because several of my inputs do not change regularly and by running from a read-only cache tool instead of directly from the source file saves like 10 mins in the run time.)  I've deleted and recreated the Excel output template to ensure it's not corrupt.  I've downloaded and am using the latest version of Alteryx (11.7.4).  I've closed out of Alteryx and reopened it, restarted my computer, run the build 3-4 times before using the Analytical app, danced on one foot and said a prayer, etc.

Request:

How can I get Alteryx to stop crashing?  What is causing the error in the Excel output and how can I ensure it will spit out ALL of my data and not randomly delete rows from the bottom of some of the tabs?  

Thanks for any insight you can provide.

Pictures of Error Messages:

Alteryx Error MessagesExcel Output Error Messages

[MarqueeCrew]

@amanda_e,

I would export the package (after having replaced the oracle read with a cached set of data saved as a yxdb input).  By exporting, you will include all inputs and the excel output file in the zip package.  The package can be sent to support@alteryx.com (NDA is in place with your license agreement) and ask for technical support.  You can post the data and workbook here in the community if the data isn't sensitive, but since you mention "clients" I imagine that it is.

If you can replicate the issue within a simplified process, posting the problem here would suffice and I'm fairly certain that you'll get community help.  While I understand the need for your final delivery to clients in formatted excel output, I would tend to discourage the solution.  I support the use of Alteryx for data blending and when you output to excel, the more simple the better.  Personally, I wouldn't want to receive .xlsm files from anyone.  I simply don't trust them.  When my previous organization created formatted excel files, we output to excel and then called external processes to read the excel data and update template .xlsx files.

Lastly, I just wanted to assure you that "we the community" have seen your post.  We'd love to help you but it would be hard for us to step through the problem with so many unknown variables. 

Cheers,

Mark

[amanda_e · Accepted solution]

Hi -

I think I figured out the two errors I was having.  My data is extremely confidential and sensitive, so I'm not at liberty to share it, and unfortunately the build has so many pieces, that it'd be hard to recreate.

First, Alteryx was crashing.  I think this was a two part problem.  I was running low on memory space but I had an Alteryx SMS expert show me how to create a simple build in Alteryx to see where I was storing the most files so I could clean up space (build attached below). 

The second part was that I was exporting data to named ranges in multiple tabs on a .xlsm Excel file called "TEMPLATE".  This in and of itself is NOT the problem.  However, when I exported the data for Client A, if that client had 100 rows on the first tab and 200 rows on the second tab, even though I cleared the content and ensured the named ranges were updated to include >1000 rows before running Client B, when I ran Alteryx for Client B, if that client had more rows than Client A, Alteryx would crash and I would get an error that records were removed from Excel.  For some reason, it would limit the Excel output to 100 rows on the first tab and 200 rows on the second tab, even though the results in Alteryx were showing ALL of the rows related to each client (before it crashed).  

I was able to fix both errors by creating a copy of my "TEMPLATE.xlsm" file and opening the copy and saving over the original template after I ran each client in Alteryx.  Not an ideal solution, but now Alteryx is running without crashing and the output in Excel includes all records.

[Paul-Evans]

Thank you for sharing your resolution.

Upon reading that your problem seem related to the data that was previously in the template, I figured it might make sense to put dummy data in the my template range that Alteryx was overwriting. That led me to notice some formatting artifacts (I know, I should have made this template from a clean file instead of someone's example), so I did a clearing of formatting as well. 

That did the trick - no more corrupt files with lost records! FWIW - I have been using this solution to copy a clean template each time: Output & duplicate template file