Groot .csv bestand in 1 keer in een 2 dimensionale array zetten

[VenA]

Voor een projectje moet ik een groot .csv bestand bewerken. De bottleneck in snelheid zit in het inladen van het .csv bestand. Het bestand bestaat uit 100+ kolommen en 200000+ rijen.

Het openen via getobject duurt lang. Het inlezen als textbestand in een array en vervolgens per rij splitsen gaat wel sneller maar lijkt mij omslachtig. Is er een optie om dit sneller te krijgen?

Sub test1()
  'import230000rijen.csv
  'Import_20200816.csv
  t = Timer
  c00 = "E:\Temp\Import_20200816.csv"
  ar = Split(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, vbCrLf) 'vbLf in het orgineel
  y = UBound(Split(ar(1), ";"))
  ReDim ar1(UBound(ar), y)
  For j = 0 To UBound(ar) - 1
    x = Split(ar(j), ";")
      For jj = 0 To y
        ar1(j, jj) = x(jj)
      Next jj
  Next j
  MsgBox Timer - t
End Sub

 

[cow18]

ik probeer het zaakje te simuleren, maar loopt vast van zodra rijen > 50.000.
Dan kan ik die array ar1 niet meer netjes wegschrijven.

Over welke tijden praten we hier, seconden of minuten ?

 

[snb]

Waarom niet gewoon in Excel geopend ?

Sub M_snb()
   Workbooks.Open "J:\download\import_20200816.csv", , , , , , , , , , , , , 1
End Sub

 

[VenA]

Even met het echte bestandje getest in Excel 365 (ruim 236000 rijen)

@cow18, het wegschrijven gaat bij mij prima. Maar dat wil ik nog niet. Eerst de bewerkingen op de array loslaten en dan pas wegschrijven.

@snb,
Even getest inclusief inlezen in array en sluiten bestand.

• Workbooks.open ± 27 seconden;
• Getobject ± 23 seconden;
• opentextfile inclusief bewerkingen ± 13 seconden.

Dus dat is de reden dat ik geen Workbooks.open gebruik.

 

[snb]

Maar waarvoor is die array nodig ?

Ik vermoed dat Excel 2010 veel sneller werkt met openen van een csv-bestand.

 

[VenA]

Waar de array voor nodig is voor nu nog even niet van belang.

Dat Excel 365 zeker met het openen van bestanden trager is dan Excel 2010 is mij al vaker opgevallen. Scheelt ook bij dit bestand zeker 5 seconden. Waarbij getobject nog steeds sneller is. (22sec om 17sec) Maar is zit vast aan Excel 365

 

[snb]

Dat is het wel; vanwege de volgorde:

Sub M_snb()
   sn = Workbooks.Open("J:\download\import_20200816.csv", , , , , , , , , , , , , 1).Sheets(1).UsedRange
End Sub

Bij 210.000 regels:
workbooks.open: 18,7 sec.
getobject: 18,7 sec.

Alternatief:

Sub M_snb()
   t1 = Timer
   Open "J:\download\import_20200816.csv" For Input As #1
      sn = Split(Input(LOF(1), #1), vbCrLf)
   Close
   MsgBox Timer - t1
   
   For j = 0 To UBound(sn) - 1
      st = Split(sn(j), ",")
      If j = 0 Then ReDim sp(UBound(sn), UBound(st))
      For jj = 0 To UBound(sp, 2)
         sp(j, jj) = st(jj)
      Next
   Next
   
   MsgBox Timer - t1
End Sub

 

[VenA]

Het alternatief is zo'n 1,5 seconde sneller dan de code in #1 en dat zit met name in

Open "J:\download\import_20200816.csv" For Input As #1

En dat is ook weer handig om te weten. Mag ik de conclusie trekken dat de lus nodig is?

Dan blijf natuurlijk altijd de vraag wat maakt die paar seconden uit op een mensenleven? Geen moer. Maar hoe minder lang dat irritante windows rondje draait hoe prettiger een applicatie werkt.

 

[snb]

Misschien is deze nog wat sneller:

Sub M_snb()
   t1 = Timer
   ReDim sp(210000, 110)
   
   Open "J:\download\import_20200816.csv" For Input As #1
        Do While Not EOF(1)
           Line Input #1, c00
           st = Split(c00, ",")
           For jj = 0 To UBound(st)
              sp(j, jj) = st(jj)
           Next
           j = j + 1
           If j > UBound(sp) Then Exit Do
         Loop
    Close #1
   
   MsgBox Timer - t1
End Sub

 

[snb]

Nog wat testsuggesties:

Sub M_snb()
   t1 = Timer

  Application.ScreenUpdating = False
   sn = Sheets.Add(, Sheets(Sheets.Count), , "J:\download\import_20200816.csv").UsedRange

   MsgBox Timer - t1
End Sub

Sub M_snb()
   t1 = Timer
   Application.ScreenUpdating = False
  
   Open "J:\download\import_20200816.csv" For Input As #1
      c00 = Input(LOF(1), #1)
   Close

   With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
     .Clear
     .SetText c00
     .PutInClipboard
   End With
    
   with ActiveSheet
      .Paste .Cells(1)
      sn = .usedrange
   end with

   MsgBox Timer - t1
End Sub

Sub M_snb()
  t1 = Timer

  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `import_20200816.csv`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=J:\download\", 3
     sn = .getrows
  End With
    
  MsgBox Timer - t1
End Sub

NB. In dit geval zijn de 'kolommen' 'rijen' en andersom.

Sub M_snb()
   t1 = Timer
   
   sn = Split(CreateObject("scripting.filesystemobject").opentextfile("J:\download\import_20200816.csv").readall, vbCrLf)
   MsgBox Timer - t1
     
End Sub

 

[VenA]

Even het eea getest.

• #9 Ging als een speer totdat ik de , had aangepast in een ; Ongeveer even snel als de 2e code in #7
• #10 code 1 Ubound(sn,2) = 1 dus gaat snel maar wordt niet gesplitst.
• #10 code 2 ± 37 seconden
• #10 code 3 met de ADODB.Recordset ben ik zelf ook aan het stoeien geweest en kreeg het niet aan de praat met een ; gescheiden lijst. Werkt met jouw code om dezelfde reden waarschijnlijk ook niet en is ook niet zo snel als ik verwacht had. (± 33 seconden)
• #10 code 4 Hiermee ben ik het draadje begonnen.

Nu kan ik hier wel het eea doen met de systeeminstellingen om dat wat niet werkt aan de praat te krijgen maar dat gaat op het werk een stuk lastiger worden.

Bedankt iig voor alle suggesties.

 

[cow18]

de opentextfile van #4 in 13 sec heeft nog steeds het record.
Eigenlijk wil je dat groot bestand dus enkel openen ter consultatie, je wil er iets/bepaalde zaken uit pikken en de rest vergeten.
Van zodra je er een werkblad bij betrekt zit je >30 sec, is zo de algemene teneur.

Kan je niet beter definieren wat je wil elimineren uit die data ?
Anders vind ik 13 sec/230.000 records toch "razend" snel, een kleine 18.000 records per sec.
Voorlopig was je vrij algemeen in de vraagstelling, het was het meer te doen om het globale plaatje.
Wat SNB aanreikte, dat was goed, maar voldeed, denk ik toch, niet aan je verwachtingen.

 

[VenA]

Ik was vooral benieuwd naar een methode die het zonder lusje zou kunnen. Over het algemeen is een methode zonder lusje sneller en dat zou mooi meegenomen zijn. Af en toe lopen er hier wat 'tovenaars' rond die dat in 1 of 2 regels kunnen programmeren. Je hebt natuurlijk gelijk dat 18.000 records per seconde snel genoeg is voor de dagelijks praktijk.

Af en toe is het leuk en leerzaam om de verschillende methoden te vergelijken en waar dan ook weer dingen uitkomen als het verschil tussen Excel 2010 en Excel365 in snelheid van het openen van een bestand.