Connecting to an Alteryx output from Excel that does not make Excel Crash

[JonaV]

Hi,

Is there a way to connect to an Alteryx output in Excel so that I can run my reports in Excel?

Currently, I am outputting my data into an Excel worksheet, but the file is so large that it makes Excel crash. I also found out that we cannot connect to an Alteryx database directly from Excel. I also tried outputting my data into Access, but it's also too much to handle for Access.

Edit: The output currently uses about 370K rows (120K KB) in Excel, but for some reason it crashes some times. This could be because the file is saved in a shared folder in the company directory. I need this to be saved in the shared folder because I am not the only one using this output.

Is there a work around this?

Unfortunately, I need to keep using Excel to run my reports, otherwise I would output my data into a Tableau extract and run my reports in Tableau.

Any suggestions?

Thanks,

JV

[apathetichell]

Can you chunk it by some part of your data or run part of your report in Alteryx and then export part to Excel? Also - are you rendering or using output data? Can one assume we are talking about 500k+ rows here?

The reality is that Excel is a (very good) spreadsheet which people use for database operations but it is not a database. It has more in common with a vintage Lotus 1-2-3 than it does with a database and has significant failures as a database/data management system.  I don't know your specifics but my expectation is that there will be more problems like yours - not fewer. I am a bit surprised that Access can't handle it.

[MattBSlalom]

You don't mention the actual size of your data, so as an FYI, the limit of an Excel sheet is 1,048,576 rows.

Excel specifications and limits - Excel (microsoft.com)

Assuming you're trying to output more rows than that... let's say 2 million.  I have a few thoughts for you.

1. Are you really using ALL of that data for a single Chart?  If not, can you split up the output to write across multiple sheets / files?
2. Do you really need this presumably very detailed level of data for your Charts?  Can you summarize the data in Alteryx before outputting to Excel to limit the record count?
3. You could potentially utilize some of the PowerBI functionality built into Excel to overcome the row limit.  Using a PowerPivot model instead of a direct Excel sheet will not have the same limitations, but I'd suggest the first 2 options be fully explored before heading down this rabbit hole.

[apathetichell]

fyi - access file size limits are 2gb - unless you are using spatial (which I'd posit you should not be doing in excel) I can't see how the file is too big for access. Perhaps the output/render is just incorrect? Does it work as a .csv?

[danilang]

Hi @JonaV 

Like the others have mentioned a 120 MB Excel file with 370K rows should be ok to open in Excel.  How many columns? What error messages to you get when Excel crashes?

Dan

[apathetichell]

Is there a possibility it's crashing because this is an OPEN shared field (ie someone else is using it) while Alteryx is trying to write to it?

[MattBSlalom]

Thanks for updating with the actual row count, that helps.  As mentioned, you shouldn't be hitting a limitation of Excel with that row count.  I have seen strange/frustrating behavior when dealing with shared drives in the past.  Here are 2 more thoughts for you:

1. You said it must stay on the shared drive since it's accessed by others... but, can you leave Alteryx writing to that location (since you don't seem to mention an issue with the workflow), then copy the file (or add a 2nd output in the workflow) locally to open in Excel?  Then save/copy that finished version on top of the shared drive version.
2. 120 MB is not a small Excel file either, so I'm wondering if it could be your laptop specs that are the culprit.  Just for curiosity, how much RAM does your system have?