CODEWITHSUNDEEP
excelvbamacos
Peekay
Peekay

Reputation: 236

Handling huge Excel file

Need your help badly. I am dealing with a workbook which has 7000 rows X 5000 columns data in one sheet. Each of this datapoint has to be manipulated and pasted in another sheet. The manipulations is relatively simple where each manipulation will take less than 10 lines of code (simple multiplications and divisions with a couple of Ifs). However, the file crashes every now and then and getting various types of errors. The problem is the filesize. To overcome this problem, I am trying a few approaches

a) Separate the data and output in different files. Keep both files open and take data chunk by chunk (typically 200 rows x 5000 columns) and manipulate that and paste that in output file. However, if both files are open, then I am not sure it remedies the problem since the memory consumed will be same either way i.e. instead of one file consuming a large memory, it would be two files together consuming the same memory.

b) Separate the data and output in different files. Access the data in the data file while it is still closed by inserting links in the output file through a macro, manipulate the data and paste it in output. This can be done chunk by chunk.

c) Separate the data and output in different files. Run a macro to open the data file and load a chunk of data say 200 rows into memory into an array and close it. Process the array and open the output file and paste the array results.

Which of the three approaches are better? I am sure there are other methods which are more efficient. Kindly suggest.

I am not familiar with Access but I tried to import the raw data into Access and it failed because it allowed only 255 columns.

Is there a way to keep the file open but wash it in and out of Memory. Then slight variations to a and c above can be tried. (I am afraid repeated opening and closing will crash the file.)

Look forward to your suggestions

Upvotes: 0

Views: 6484

Answers (2)

N Mason
N Mason

Reputation: 326

If you don't want to leave Excel, one trick you can use is to save the base excel file as a binary ".xlsb". This will clean out a lot of potential rubbish that might be in the file (it all depends on where it first came from.)

I just shrank a load of webdata by 99.5% - from 300MB to 1.5MB - by doing this, and now the various manipulation in excel works like a dream.

The other trick (from the 80s :) ) if you are using a lot of in cell formulae rather than a macro to iterate through, is to:

  1. turn calculate off.
  2. copy your formulae
  3. turn calculate on, or just run calculate manually
  4. copy and paste-special-values the formulae outputs.

Upvotes: 1

DMunchkin
DMunchkin

Reputation: 111

My suggestion is using a scripting language of your choice and working with decomposition/composition of spreadsheets in it.

I was composing and decomposing spreadsheets back in the days (in PHP, oh shame) and it worked like a charm. I wasn't even using any libraries.

Just grab yourself xlutils library for Python and get your hands dirty.

Upvotes: 0

Related Questions