Author Topic: Excel cannot complete this task with available resources  (Read 3267 times)

0 Members and 1 Guest are viewing this topic.

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Excel cannot complete this task with available resources
« on: January 19, 2018, 05:33:02 pm »
I'm just curious about the error... what could be causing it since it seems random?

I'm planning to run putty to log serial data, then use Excel to plot the data. I was testing this setup with a USB GPS puck.

Putty updates the log file, seemingly every time data is received. I've imported this text data into Excel and set the update properties to 1/min.

I've created plots from the data and, magically, when the data is updated, so are my plot ranges.

This ran a few hours yesterday, but this morning Excel was displaying the subject message. I clicked cancel and Excel seemed locked, or rather the whole computer was just being slow (the plots would not display again). I closed Excel and it prompted me to save.

I restarted Excel and it picked up from where it was, updating my data and plots again, until the subject message appeared after maybe an hour. I restarted and it's running fine again.

The problem does not really matter much because putty continues to build the log file, and I do not really need quasi-live plot data, but it would be nice to have it available. CPU usage is very low and memory is ~70% of 8GB installed (but I have a lot of productivity software running now, like MS Office and some Adobe apps). There are ~150k lines of data now in about 20 rows.
 

Offline BergRD

  • Contributor
  • Posts: 22
  • Country: us
Re: Excel cannot complete this task with available resources
« Reply #1 on: January 19, 2018, 06:13:11 pm »
Based on the error and 20y in IT I would think it is tied to available memory.

While 8g is a fair amount, everything you run or have running including the OS itself; is fighting for those resources.

Excel has in the past had some issues with data input and of course the more samples/data; the longer things are going to take.  Not knowing how the data is formed (CSV, other delimited text) that's quite a bit of data to load and analyze on the fly.  Also because the file is 'in use' by Putty, in your quasi-live logging, it might also have file locking issues while trying to load into Excel while logging live.  You could try to stop the logging and save the file then load in Excel to see if the same issues arise.

My opinion, without further information, is available RAM to load the data.  Best case, reboot OS and launch nothing or terminate all unneeded tasks running and try again.  You may get farther but if it's something you need it may be best to investigate logging into a database.  There are many free databases out there that can be used to log data which will help in the saving, indexing, etc of the data which takes the work away from Excel; using it only as a reporting device/graphing device instead of trying to do all the work internal.  mySQL and others are out there to use and I have used various products over the years from mySQL, Microsoft SQL Server as well Oracle, but the latter 2 are licensed products you would have to purchase.

Good luck and hoping some hints help!
 

Offline BergRD

  • Contributor
  • Posts: 22
  • Country: us
Re: Excel cannot complete this task with available resources
« Reply #2 on: January 19, 2018, 06:14:45 pm »
Searching while posting above this 'guide' came up result #1.

It recommends many of the things I tried to state above and ultimately ending in upgrading RAM for more space.
https://www.statsilk.com/support/faq/resolving-error-excel-cannot-complete-task-available-resources-choose-less-data-or-close-other
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #3 on: January 19, 2018, 06:33:36 pm »
The data is a few lines of CSV NMEA data that emits every second. My data will likely be simpler, probably just two or three comma separated values every second or thereabouts.

It is 64-bit Excel and RAM was ~50% used after closing all other apps. The file is approaching 10MB.

It might be that putty starts to write data to the log file when Excel is still reading it. It does work for a good long while once I restart Excel, so it does not seem to be at a resource limit. Excel does partially lock up - I cannot select other worksheets or use the ribbon, but can save the file and close.
 

Offline BrianHG

  • Super Contributor
  • ***
  • Posts: 7660
  • Country: ca
Re: Excel cannot complete this task with available resources
« Reply #4 on: January 19, 2018, 06:46:37 pm »
Try OpenOffice's Calc.  maybe it can handle it.
 

Offline Lorenzo_1

  • Regular Contributor
  • *
  • Posts: 218
  • Country: au
Re: Excel cannot complete this task with available resources
« Reply #5 on: January 19, 2018, 09:56:42 pm »
Not sure which version of Excel you're using but it's pretty limited for handling very big data files. My 2003 Excel only handles 64k lines of data and won't plot more than 32k points IIRC. Open Office calc handles 132k lines. But both get pretty slow with big files- even using HPZ800 with 96GB RAM. 

I use freeware/open source R for all big jobs - easy install, fantastic analysis capabilities. Handles parallel processing for really big jobs. I quite often handle files with a few million data lines from simulation outputs.  But there's some overhead learning programming syntax (though you can install a GUI interface that makes it easier to read files and do plots) and I've never tried live logging with it.

Or you could try searching for other live logging freeware suited for your purpose.
 
The following users thanked this post: BergRD

Online Zero999

  • Super Contributor
  • ***
  • Posts: 19345
  • Country: gb
  • 0999
Re: Excel cannot complete this task with available resources
« Reply #6 on: January 19, 2018, 11:35:47 pm »
OpenOffice.org Calc supports up to 1048 576 lines. I don't expect it to  be any better than Excel for large files. Modern spreadsheet software is quite bloated.

You could try Gnumeric, which I believe is better suited to large quantities of data, than MS or Open Office but according to Wikipedia, it stopped being ported to Windows back in 2014. I don't know how well the last Windows version will run on Windows 10.
 

Offline IanB

  • Super Contributor
  • ***
  • Posts: 11790
  • Country: us
Re: Excel cannot complete this task with available resources
« Reply #7 on: January 20, 2018, 12:14:29 am »
I'm planning to run putty to log serial data, then use Excel to plot the data.

If you are mainly interested in plotting then you might consider using GNUplot instead of Excel to do the plotting.

Here is a note on how to do that:

https://stackoverflow.com/questions/44470965/how-can-you-watch-gnuplot-realtime-data-plots-as-a-live-graph-with-automatic-up
 

Offline bitseeker

  • Super Contributor
  • ***
  • Posts: 9057
  • Country: us
  • Lots of engineer-tweakable parts inside!
Re: Excel cannot complete this task with available resources
« Reply #8 on: January 20, 2018, 12:30:47 am »
That definitely sounds like a memory issue, whether actual (not enough RAM) or virtual (software limitation that can't utilize available RAM). Excel isn't all that efficient (I had some processing tasks take all night and not finish), but I have had better results using it on large files than with Calc. The largest spreadsheets I've worked on recently got up to about 59MB in compressed XLSX format and I have maxed out the million or so row limit. So, generally speaking, 150K rows shouldn't be an issue especially with the 64-bit version.

Although spreadsheets are a convenient, general-purpose tool, for more reliable logging, processing, plotting, etc. that you intend to do often, it'd be good to look into software that's designed for such tasks or, if you have programming skills, write what you need.
TEA is the way. | TEA Time channel
 

Offline BrianHG

  • Super Contributor
  • ***
  • Posts: 7660
  • Country: ca
Re: Excel cannot complete this task with available resources
« Reply #9 on: January 20, 2018, 11:28:45 pm »
I'm planning to run putty to log serial data, then use Excel to plot the data.

If you are mainly interested in plotting then you might consider using GNUplot instead of Excel to do the plotting.

Here is a note on how to do that:

https://stackoverflow.com/questions/44470965/how-can-you-watch-gnuplot-realtime-data-plots-as-a-live-graph-with-automatic-up
Now you tell me this, when I needed such a tool 7 years ago, and ended up writing a complete COM port direct logging and plotting software in FreeBasic.  Though it started out simple enough, it's grown into a little beast since I wanted additional real time status meters and clocks with different font sizes outside of the scrolling graph & wanted an export .bmp image saving function.  I needed to since my logs got up into the 100mb range and I wanted the speed of a hard written logging tool, working within the log file directly in real time using piratically no system memory and virtually no CPU utilization.  In fact, the tool should have no trouble addressing 2gb files.  Anything larger, and I would have to split the files, unless the new FreeBasic64 now supports 64bit longs for seeking within files.

« Last Edit: January 20, 2018, 11:36:46 pm by BrianHG »
 

Offline BrianHG

  • Super Contributor
  • ***
  • Posts: 7660
  • Country: ca
Re: Excel cannot complete this task with available resources
« Reply #10 on: January 20, 2018, 11:50:30 pm »
I'm planning to run putty to log serial data, then use Excel to plot the data. I was testing this setup with a USB GPS puck.

Putty updates the log file, seemingly every time data is received. I've imported this text data into Excel and set the update properties to 1/min.

I've created plots from the data and, magically, when the data is updated, so are my plot ranges.
When you say plotting, are you plotting on a real map, or, just an X/Y graph which you can zoom in and out of?
If you are doing just X/Y graphing, and your GPS puck can be accessed as a COM# port, you can create a realtime logger and plotter in FreeBasic with around 50-150 lines of code.  Though, if you never programed in basic, it'll probably take too long.  You can also do a real map background as freebasic supports 32 bit color, any window or screen size with mega scroll-able bitmaps, or even OpenGL 2D and 3D rendered screens, but this would require more work.

Your 150k x 20 rows (64 bit floats) is only 22 megabytes of data, or 11 megabytes if the 20 rows are 32 bit floats.  Re-loading and plotting such a file would be done in under 1 minute in FreeBasic with a 1 core 2GHz cpu with 512mb of system memory.  Done within a few seconds with code which first loads the entire log file into ram, then plots it.

FreeBasic also has a Linux version.  No windows emulation needed unless you are using MS Windows specific functions.
« Last Edit: January 21, 2018, 12:04:20 am by BrianHG »
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #11 on: January 22, 2018, 08:52:50 pm »
Thanks, All. I think it is an IO conflict. I let the logging continue most of the weekend without Excel open, and Excel does not have a problem opening the file or updating the data until after some time - so it looks like both programs accessing the log file is the problem. I'll look at gnuplot sometime later. The download site is blocked here.
 

Offline tooki

  • Super Contributor
  • ***
  • Posts: 11341
  • Country: ch
Re: Excel cannot complete this task with available resources
« Reply #12 on: January 22, 2018, 11:50:31 pm »
The data is a few lines of CSV NMEA data that emits every second. My data will likely be simpler, probably just two or three comma separated values every second or thereabouts.

It is 64-bit Excel and RAM was ~50% used after closing all other apps. The file is approaching 10MB.

It might be that putty starts to write data to the log file when Excel is still reading it. It does work for a good long while once I restart Excel, so it does not seem to be at a resource limit. Excel does partially lock up - I cannot select other worksheets or use the ribbon, but can save the file and close.
According to https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 the worksheet size limits in recent versions of Excel are 2^20 rows (a bit over a million) and 2^14 columns (16K-ish). Almost certainly the error isn’t actually insufficient RAM, but some internal overflow or race condition that’s throwing a technically incorrect error message. What happens if you duplicate the file before attempting to open it?
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #13 on: January 22, 2018, 11:57:40 pm »
The data is a few lines of CSV NMEA data that emits every second. My data will likely be simpler, probably just two or three comma separated values every second or thereabouts.

It is 64-bit Excel and RAM was ~50% used after closing all other apps. The file is approaching 10MB.

It might be that putty starts to write data to the log file when Excel is still reading it. It does work for a good long while once I restart Excel, so it does not seem to be at a resource limit. Excel does partially lock up - I cannot select other worksheets or use the ribbon, but can save the file and close.
According to https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 the worksheet size limits in recent versions of Excel are 2^20 rows (a bit over a million) and 2^14 columns (16K-ish). Almost certainly the error isn’t actually insufficient RAM, but some internal overflow or race condition that’s throwing a technically incorrect error message. What happens if you duplicate the file before attempting to open it?

duplicate the log file?

There is no problem just opening the file, but when Excel is already open and has a timer to update the external data once every minute, after about an hour it throws the error. I can close Excel and start it again and it will be fine for roughly an hour, or 20 mins...

Maybe I will just open the filled in log file and let Excel stare at that all night and see if it produces an error. There will be no logging, and I do not expect an error.
 

Offline tooki

  • Super Contributor
  • ***
  • Posts: 11341
  • Country: ch
Re: Excel cannot complete this task with available resources
« Reply #14 on: January 23, 2018, 01:18:15 am »
Oh, sorry, I missed that it was actually opening the file at all. I thought it was failing initially, and given Windows' usually-pickier rules about concurrent file access, I thought that might be the issue. Duplicating it would have given a file that wasn't gonna get updated halfway through.

Nonetheless, I suspect that memory starvation as such probably isn't the problem, but rather some internal counter or something overflowing, or a race condition (makes even more sense now that I understand that Excel is re-reading the file potentially concurrently to the log file being written to).

Any reason you couldn't just have a macro that runs every few minutes that opens Excel, generates the chart, and then closes the workbook? (Or, indeed, just use software specific to this purpose.)
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #15 on: January 23, 2018, 02:54:48 pm »
Excel threw the same error when updating content from a static log file. It took several hours, but Excel eventually complained about updating data that it had already updated many times before. Perhaps there is some other computer function that interrupts the process. It does take Excel several seconds to read all 24kB of data and produce a plot.
 

Online paulca

  • Super Contributor
  • ***
  • Posts: 4003
  • Country: gb
Re: Excel cannot complete this task with available resources
« Reply #16 on: January 23, 2018, 03:24:03 pm »
Can I humbly suggest RRDTool?

https://oss.oetiker.ch/rrdtool/
"What could possibly go wrong?"
Current Open Projects:  STM32F411RE+ESP32+TFT for home IoT (NoT) projects.  Child's advent xmas countdown toy.  Digital audio routing board.
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #17 on: January 23, 2018, 04:22:47 pm »
I downloaded a binary RRDTool package and extracted the contents. I've not figured out how to use the software.

Maybe I downloaded source files. Both links to latest stable and local win binary go to the same download page, and further down I found an older win32 zip file. clicking the install.cmd produces a command window with message that ppm is not a recognized command.
« Last Edit: January 23, 2018, 04:27:40 pm by metrologist »
 

Online paulca

  • Super Contributor
  • ***
  • Posts: 4003
  • Country: gb
Re: Excel cannot complete this task with available resources
« Reply #18 on: January 23, 2018, 04:59:44 pm »
I downloaded a binary RRDTool package and extracted the contents. I've not figured out how to use the software.

Maybe I downloaded source files. Both links to latest stable and local win binary go to the same download page, and further down I found an older win32 zip file. clicking the install.cmd produces a command window with message that ppm is not a recognized command.

I can't look it up in work, sorry.  I haven't used it in windows.

I'll give you a quick low down on it here to give you an idea or whether it will work for you.

RRD focuses around "Round robin archives".  They are time based data archives of fixed size which automatically wrap around when they fill up.  However before wrapping they reduce the current data down.  Usually averaging down, but it does many other wonder things such as min, max etc.  You might have data sampled once second, kept for 1 day, after that day the data is averaged down to one sample per minute for a week.  After that week the data averages down to one sample per hour for a month and so on.  The data storage remains a fixed size.

A few things.  I'm not sure of it's smallest time sample window.  This might be the limiting factor.  If you data is sampling more often than once per second you might need to do ugly things to RRD, like lie to it about the time.

RRDTool is a suite of smaller applications wrapped up in one.  It's a command line tool.  So will require you use DOSPrompt or Cygwin or better a Raspberry PI.

There are web front ends for it and the raspberry PI should have installers for them which will usually set them up for you.

Have a poke around Cacti for ideas: https://www.cacti.net/
"What could possibly go wrong?"
Current Open Projects:  STM32F411RE+ESP32+TFT for home IoT (NoT) projects.  Child's advent xmas countdown toy.  Digital audio routing board.
 

Offline metrologistTopic starter

  • Super Contributor
  • ***
  • Posts: 2199
  • Country: 00
Re: Excel cannot complete this task with available resources
« Reply #19 on: January 23, 2018, 09:16:17 pm »
Cool, I do have a RPi3 sitting around and should try to use it for something. I'll probably also look more generally too.
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf