Power Query naar Excel onvoldoende geheugen

[Georgyboy]

Beste allen,

Heb geprobeerd via power query alle bestanden naar Excel te laden maar krijg de melding "Onvoldoende geheugen"
dit gaat om +/- 20.000 regels.

Gegevens -> Gegevens ophalen -> uit bestand -> uit map, dan de map kiezen

Bij submappen met minder regels vb. 5.000 regels lukt het wel.

Alhoewel die bestanden niet groot zijn 3.365 regels = 192 Kb (van één map)

Ligt het aan de oudere computer? of kan het ook anders naar Excel?

Alvast bedankt!
Groeten Georgyboy

 

[JEC.]

Hoe ziet je Query eruit? Je zou hier mee kunnen beginnen

Data tab> Get Data > Query Options > Data Load > "Allow data preview to download in the background" uitvinken

 

[snb]

of kan het ook anders naar Excel?

Als je precies beschrijft wat je wil (beginsituatie - eindsituatie), kan die vraag beantwoord worden.

 

[Georgyboy]

Dank voor de info!

Sorry dat ik niet duidelijk was!

De bedoeling is om alle bestanden eens in kaart te brengen die gedurende vele raren zijn gemaakt / gedownload, ......
Dit lukt vrij aardig met een submap, doch voor het totale overzicht via mijn documenten waar zo goed of alles staat lukt het niet om de data naar Excel te laden.
Die data is kolommen en 999+ rijen (vermoed +/- 20.000) wellicht kan je dit ook ergens controleren hoeveel er precies zijn.

Bij het verversen van de query zijn de nieuwe bestanden ook zichtbaar.

Via Excel zou ik een mooi overzicht krijgen.

In bijlage enkele printscreens

@ JEC. welke zou ik mogen afvinken in de opties?

Alvast bedankt!

Alle suggesties zijn welkom!

 

[roeljongman]

ik ben op werk ook tegen dit probleem aangelopen met powerquery, maar daar gaat het ook al snel over 150.000 bestanden.

ik heb echter ook een stukje code dat ik ooit online heb gevonden. waarmee ik in een paar seconden moeiteloosn 15000-2000 bestanden in vele mappen kan identificeren. zie bijlage.

 

[Georgyboy]

Dankjewel roeljongman,

Even getest en inderdaad binnen de 2 minuten bijna 29.000 lijnen.

Ben toch nog benieuwd hoe het verder kan met PowerQuery met grotere bestanden naar Excel.

Leren blijft leuk zeker met de aangeboden hulp die we steeds krijgen.

Groetjes,
Georgyboy

 

[snb]

Voor dit soort klusjes:

Sub M_snb()
  msgbox createobject("wscript.shell").exec("cmd /c dir C:\Users\georges\documents\*.* /b/s").stdout.readall
End sub

De eerste keer dat je deze macro draait duurt het even, daarna razendsnel

Ik vraag me alleen af: wat levert dit voor meerwaarde ten opzichte van de verkenner ?

 

[JEC.]

Heb even geen pc bij de hand maar het is toch de bedoeling dat de inhoud van de bestanden wordt opgehaald? Zo niet, dan moet het met gemak lukken met power query. Die optie welke ik hierboven noemde zou je uit kunnen vinken

 

[Georgyboy]

Dank voor de tips

@ JEC
Probeer het uit te zoeken wat het in het Nederlands zou zijn "Allow data preview to download in the background"

@SNB

Ik vraag me alleen af: wat levert dit voor meerwaarde ten opzichte van de verkenner ?

Ja gewoon nieuwsgierig hoe het ook anders kan en alles in één lijst hebben. Steeds leerrijk jullie antwoorden

Je code toegepast en loopt vast in Excel met een kleiner scherm.

Ook eens geprobeerd via Powershell, daar enkele van de vele bestanden terug gevonden.

Alvast bedankt!
Groeten, Georgyboy

 

[snb]

Met mijn code moet je altijd een msgbox zien.
Zolang de macro loopt zie je het DOS-scherm.

 

[Georgyboy]

Dankjewel Snb,

Het DOS scherm verschijnt even +/- 10 seconden en verdwijnt dan.
Er is dan een klein scherm in Excel met de reeds gevonden bestanden en met een rondje (van zoeken)
Na 1 uur nog steeds hetzelfde

Zou het vast lopen op een andere of zonder extensie bestand?

 

[MollyVH]

Hallo Georgyboy,

Kijk eens wat dit geeft (in een PQ-loos leeg Excel-bestand):

Sub mvh()
bestanden = Split(CreateObject("wscript.shell").exec("cmd /c dir c:\users\georges\documents\*.* /a-d /b /s").stdout.readall, vbCrLf)
Cells(1, 1).Resize(UBound(bestanden)) = Application.Transpose(bestanden)
End Sub

 

[JEC.]

@Molly, deze zal "snel" falen door het limiet van application.transpose
Rond 65000 regels ongeveer maar ik zie het al vaak veel eerder misgaan.

 

[JEC.]

Hier nog een recursieve oplossing. Met 1x object geladen FSO

Dim oFSO As Object, ar(80000, 0), x As Long

Sub jec()
 Set oFSO = CreateObject("Scripting.FileSystemObject")
 GetFiles "C:\Users\xxx\xxxx"
 If x Then Cells(2, 2).Resize(x) = ar
 Erase ar: x = 0
End Sub

Sub GetFiles(xFold)
 Dim Obj As Object
 If Right(xFold, 1) <> "\" Then xFold = xFold & "\"
 With oFSO.GetFolder(xFold)
    For Each Obj In .Files
       ar(x, 0) = Obj.Name
       x = x + 1
    Next
    For Each Obj In .SubFolders
       GetFiles xFold & Obj.Name
    Next
 End With
End Sub

 

[MollyVH]

@ JEC,
TS praat over ca. 20000 bestanden, bij mij gaat dit prima voor een map met bijna 60000, maar onbegrensd is het uiteraard niet.

Edit: was een reactie op je #13 vóór aanpassing. 64K is in theorie de limiet, maar kan inderdaad sneller fout aflopen. Op zo'n 20000 durf ik mijn geld zetten

 

[JEC.]

Laatst ging het bij mij al mis bij 6500, vraag me niet waarom. Sindsdien probeer ik het te vermijden. Meer een persoonlijk dingetje

 

[MollyVH]

Bij 6500? Daar gaan mijn centen:d

 

[JEC.]

Ik kon het niet verklaren, normaal kom ik ook wel over de 20k hoor

 

[MollyVH]

@ JEC,

Met je recursieve methode (wou ik vanzelfsprekend eens proberen) loop ik geregeld tegen een 'toegang geweigerd'-fout aan.

Dan maar de antieke weg op... met deze rudimentaire code (ik geef het graag toe) verwerkte ik een map met meer dan 250.000 bestanden in een tiental seconden:

Sub mvh()

Application.ScreenUpdating = False
bestanden = Split(CreateObject("wscript.shell").exec("cmd /c dir c:\users\georges\documents\*.* /a-d /b /s").stdout.readall, vbCrLf)
For i = 0 To UBound(bestanden)
  Cells(i + 1, 1) = bestanden(i)
Next i

End Sub

 

[JEC.]

Toegang geweigerd ken ik niet, misschien zit het in de beveiliging van een specifieke map.

Wat betreft je loop zou ik alles in een array lezen en in 1x wegschrijven. Dat zou het aanzienlijk moeten versnellen