Read Excel file into array is SLOOOOOOW

[Zest]

Hello,

I've made a script to read a book database from an Excel sheet. I have it determine the number of columns and rows in the sheet and read this into a 2D array.

The Database has about 450 rows (books). The loop which reads the data row by row into an array is very slow (about 13 seconds on my Athlon 64 3200+).

I'd really appreciate any help on how to get this faster.

I've posted the relevant part of the code here, where I have marked the slow part:

;Declare global database array with maximum size
Global $Database[5000][30]
;Declare global (empty) array to overwrite the database with when it must be emptied
Global $DatabaseClean[5000][30]
;Declare global number of books in database
Global $NumOfBooks = 0
;Declare global counters for number of data columns and rows found in the Excel sheet
Global $ColNumber = 1
Global $RowNumber = 1

expandcollapse popup

Func OpenDatabase()
    SplashTextOn("Öffnen der Datenbank", @CRLF & "Die Datenbank wird geöffnet." & @CRLF & "Der Ladevorgang kann einige Zeit in Anspruch nehmen...",500,70,-1,-1,0,"",10,400)
;reset book counter
    GUICtrlSetData($BookCounter,1)
;reset database
    $Database=$DatabaseClean
;reset number of books
    $NumOfBooks = 0
    $oExcel = _ExcelBookOpen(@ScriptDir & "\Database\Literaturkatalog.xls",0,True)
    If @error Then
        ConsoleWrite("Debug: Failed to open Excel book: " & $oExcel & @LF)
    EndIf
;Activate selected sheet for opening (=selected database)
    _ExcelSheetActivate($oExcel, $DBCounter)
;---------------------------------------
;read in database array from first sheet
;---------------------------------------
;determine number of columns
    $ColNotEmpty = 1
    $ColNumber = 1
    While $ColNotEmpty = 1
        $ExcelColArray = _ExcelReadArray($oExcel,1,$ColNumber,1,0,0)
    ;_ArrayDisplay($ExcelColArray, "TEST")
        If $ExcelColArray[0] = "" Then
            $ColNotEmpty = 0
        EndIf
        $ColNumber = $ColNumber + 1
    WEnd
    $ColNumber = $ColNumber - 1
;Number of columns with data is $ColNumber-1, because the first columns is empty!
;MsgBox(0,"Number of columns", "Number of columns: "& $ColNumber & @CRLF & "Number of columns with data: " & $ColNumber-1)

;Read in rows until first cell is empty
    $RowNotEmpty = 1
    $RowNumber = 1
; --------------------------------
; THIS PART IS VERY SLOW
; --------------------------------
    While $RowNotEmpty = 1
        $ExcelArray = _ExcelReadArray($oExcel,$RowNumber,1,$ColNumber-1,0,1)
        For $n = 1 to $ColNumber-1  
            $Database[$RowNumber][$n] = $ExcelArray[$n]
        Next
        If $Database[$RowNumber][1] = "" Then
            $RowNotEmpty = 0
        EndIf
        $RowNumber = $RowNumber + 1
    WEnd
; --------------------------------
; THIS IS THE END OF THE VERY SLOW PART
; --------------------------------
    $RowNumber = $RowNumber - 1
    ReDim $Database[$RowNumber][$ColNumber]
    $NumOfBooks = $RowNumber-2
;MsgBox(0,"$NumOfBooks", $NumOfBooks)
    _ExcelBookClose($oExcel)
;_ArrayDisplay($Database, "Read Array from Excel sheet")
    StripDatabase()
    Update()
    SplashOff()
EndFunc

[randallc]

Hi,

Don't loop for each row; read the whole required worksheet area into a 2D array in one read.

best, randall

[PsaltyDS]

Hello,

I've made a script to read a book database from an Excel sheet. I have it determine the number of columns and rows in the sheet and read this into a 2D array.

The Database has about 450 rows (books). The loop which reads the data row by row into an array is very slow (about 13 seconds on my Athlon 64 3200+).

I'd really appreciate any help on how to get this faster.

Make sure you have the latest version of Locodarwin's ExcelCOM_UDF.au3, and use the _ExcelReadSheetToArray() function to get the whole sheet (or an entire row/col selection) at once.

If that is still too slow, you can access an excel spreadsheet as a database with ADODB. I believe randallc or ptrex posted an example a while back.

[Zest]

First of all, thank you both very much for your help. I didn't notice in the Excel COM UDF there was a function to read the entire sheet.

For some weird reason I can't get it to work though...

Here is a piece af very simple code I tried to test the reading of the sheet with, but it gives me an Error 2 and Extended 1 with _ExcelReadSheetToArray.

I have the Excel COM UDF v1.4 and I'm using Excel 2000. The test.xls file contains nothing but a few rondomly typed letters in the first 4 columns and first 4 rows.

I'm sorry if this question is stupid, but what am I doing wrong here?

#include <GuiConstants.au3>
#include <File.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>

    $oExcel = _ExcelBookOpen(@ScriptDir & "\Database\test.xls",0,True)
    If @error Then
        MsgBox(0,"ERROR","Failed to open Excel book: " & $oExcel & @LF)
    EndIf
    $Database = _ExcelReadSheetToArray($oExcel)
    If @error Then
        MsgBox(0,"ERROR","Failed to read Excel sheet to array" & @CRLF & "ERROR: " & @error & @CRLF & "Extended: " & @extended)
    EndIf
;~  _ArrayDisplay($Database)

[PsaltyDS]

First of all, thank you both very much for your help. I didn't notice in the Excel COM UDF there was a function to read the entire sheet.

For some weird reason I can't get it to work though...

Here is a piece af very simple code I tried to test the reading of the sheet with, but it gives me an Error 2 and Extended 1 with _ExcelReadSheetToArray.

I have the Excel COM UDF v1.4 and I'm using Excel 2000. The test.xls file contains nothing but a few rondomly typed letters in the first 4 columns and first 4 rows.

I'm sorry if this question is stupid, but what am I doing wrong here?

#include <GuiConstants.au3>
#include <File.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>

    $oExcel = _ExcelBookOpen(@ScriptDir & "\Database\test.xls",0,True)
    If @error Then
        MsgBox(0,"ERROR","Failed to open Excel book: " & $oExcel & @LF)
    EndIf
    $Database = _ExcelReadSheetToArray($oExcel)
    If @error Then
        MsgBox(0,"ERROR","Failed to read Excel sheet to array" & @CRLF & "ERROR: " & @error & @CRLF & "Extended: " & @extended)
    EndIf
;~  _ArrayDisplay($Database)

That snippet of code looks fine. I don't have Excel here to test, but will take a look when I can. The returned @error = 2 and @extended = 1 would be for a column input parameter error, but this code defaults those, so it should work fine.

[Zest]

Hello, I've found the cause of the error and wrote some code which I think fixes the poroblem. Check it out here:

http://www.autoitscript.com/forum/index.ph...mp;#entry491607

[PsaltyDS]

Hello, I've found the cause of the error and wrote some code which I think fixes the poroblem. Check it out here:

http://www.autoitscript.com/forum/index.ph...mp;#entry491607

Replied in that topic with a patched version of the function to test.