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

[theta]

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

 

[Trevor G]

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.

 

[theta]

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...

?

 

[Trevor G]

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.

 

[theta]

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...?

 

[snb_]

How many XLM-lines are you talking about ?
And after import, what do you want to do with it in Excel ?

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

 

[theta]

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

 

[theta]

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?

 

[theta]

This is the mem usage when it crashes (rapidly increased from 650mb)

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