File too big...import as stream or line-by-line?

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi. I was able to import an XML file no probs. Now I am running into memory limit issues. So want to import the XML as a stream, or import it line by line. How would one acheive this in excel 2007?

Any help appreciated

Kind regards
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
I would suggest that you look at the Data Tab and Import Other Sources and then XML.

I would say i'm no expert on the XML side of Excel.
 
Upvote 0
That's the method I have been using...fails!

I get the name scheme (ns1) headers but no data pulls in. Works ok sometimes, rest of time the machie feels like it gets clogged up and then I get memory errors now won't import anything

Have changes registry to clear the page file on shutdown etc...still problem persists. Then all of a sudden it may function correctly...

?
 
Upvote 0
If the data is to large to import to Excel have you considered using Access then split down via queries, access can usually hold 2 gig memory.
 
Upvote 0
I need it as a flat table. Access import each individual table

Don't have time to build queries etc and identify all links and primary keys

Excel did import up to 70mb files, now won't even import a 35mb file

Then may suddenly work fine

Is their any way to dedicate 2gb or RAM to excel in windows...?
 
Upvote 0
How many XLM-lines are you talking about ?
And after import, what do you want to do with it in Excel ?

Code:
Sub alternative()
  open "C:\example.XML" for input as #1
   sq=split(Input(LOF(1),#1),vbcrLf)
  Close
 
  sheets(1).cells(1).resize(ubound(sq)+1)=application.transpose(sq)
End Sub
 
Upvote 0
I get an error 14, out of string space?

It is 70mb, quite a few lines. Once imported it is not a problem, can pivot etc

It has been importing fine for 2 weeks, now suddenly lots of problems related to memory.

The file is not corrupt (have tested it on several).

It is the size causing the prob. Before I could import 70mb+ no problem. Now it won't evem import 30mb without an error relating to out of memory, resource or 1004.

Is there any way to allocate all RAM and system resource to Excel on a priority basis?

Thanks
 
Upvote 0
Looking at system monitor, the memory usage creeps up to 580,000 K then bombs out

Surely I can clear the memory mid macro.

This occurs when I filter the imported XML, copy the range, go back to produce another filter, copy the range etc etc

Why does the memory usage keep increasing?
 
Upvote 0
This is the mem usage when it crashes (rapidly increased from 650mb)

http://freeuploadimages.org/images/h337djlqpu83bf95d7e5.jpg

h337djlqpu83bf95d7e5.jpg
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top