Help with Excel (large data)

[astrolabos]

Hello world.

I'm an old autoit user, (i think like 8+ years) and have wrote hundreds of working scripts.

Nowday' s im working on this web spider, it gets links from pages im interested with wget, and saves the links i want with the titles and some data i want so i can see them later.

I'm using Autoit 3.3.14.2, Ms Excel 2007, windows 7.

After like 20k excel rows, is loses the connection with the excel.

some of the errors i get are:

We intercepted a COM Error !
Get_Generic.au3 (585) : ==> COM Error intercepted !
    err.number is:      0x80020008
    err.windescription: Bad variable type.

    err.description is:     
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     
    err.lastdllerror is:    0
    err.scriptline is:  585
    err.retcode is:     0x00000000
    
   that line says:
   $link  = $oWorkbook.Worksheets($sWorkbook_page).Cells($Data_line, $link_column).Value

Get_Generic.au3 (3165) : ==> COM Error intercepted !
    err.number is:      0x80070057
    err.windescription: The parameter is incorrect.
    err.scriptline is:  3165
    err.retcode is:     0x00000000
    $ExcelLastRow: 21260
    
    this line says:
    If (not (IsObj($oWorkbook.Worksheets($sWorkbook_page)))) Then
    
    there happens intentional error catching, it creates a loop between the Myerror and the isobj containing functions, so it loops, with 1 sec delay, so far i have left it for most like 1-2 minutes, i.e. 100+ loops.

(the other err.XXXX are empty.)

 

I have spend some hours debuging and if found that when i read

$oWorkbook.Worksheets($sWorkbook_page).Cells(1,1).value

$oWorkbook is Object (as it should be)

$oWorkbook.Worksheets() is Object (as it should be)  (this returns the collection of sheets)

$oWorkbook.Worksheets($sWorkbook_page)  is keyword (it should be object) (it looks like not finding the page)

and im kinda run out of ideas.

The error happens on some random read attempt from excel, (not in the first read attempt), which is driving me nuts.

The fact that it works always at 10k lines but not always in 20k i guess excludes any programming mistakes I could be making. (i know i dont :P)

Thing is that if i delete like 10k lines from excel, and run it again, works like a charm, so it doesn't look like a coding error.

 

I have tried: auto closing-opening book, catching again (without close- open ) the objects via

$bSaveChanges = True
    $bForceClose = True
    $bVisible = True
    $oExcel.DisplayAlerts = False
    _Excel_BookSave($oWorkbook )
    _Excel_BookClose($oWorkbook )
    _Excel_Close($oExcel, $bSaveChanges ,$bForceClose )
    sleep(2000)


    $oExcel = _Excel_Open()
    $oWorkbook = $oExcel.Workbooks.Open($bookaddress)

but the error just repeats it self.

 

 

 

.....any ideas?

 

 

[AutoBert]

Post a runable reproducer script, each snipet seems to be ok, but all snipets together isn't the script which trows error.

[astrolabos]

Ok there it is.

Its a script that essentially opens the attached excel (zipped to fall below the 4mb forum attachment limit) and reads random values from it.

To run, unzip file on same folder with script, open script on scite, hit F5 and watch console output.

in my computer crashes after  4680-4681 reads at 26820ms after opening the excel.

I have made tries to run it with some delay between reads but crashes at fewer reads which come later.

I have included a custom error print function that demonstrates the problem i described in the first post, i.e. autoit is losing  the $oWorksheet_ 's object status, and thinking its "Keyword"

On my program from that point after even if restart script, or reopen excel (through script, sending the error function to restart script), error comes again. Of if I retry reading the excel cell comes the same error.

The file includes a function that fills the excel with data, but because it was taking too long i just used copy paste. (I later thought i should have used range write but anyways thats not the problem.

Data.zip

LargeData Error.au3

Edited August 5, 2016 by astrolabos

[PACaleala]

Excel 2010 and the script from post #3 do not crash.

Run "LargeData Error.au3 on a different computer and/or another version of Excel.

[astrolabos]

thank you for the feedback.  I have had moved on an I'm now using only Autoit tables since i have being encounter the same error on every script that tries to read past ~25k lines of excel file. I have tried: range().value, usedrange.columns(A:X).value, read it on 1-10k line blocks and other ideas I have had, but it just doesn't want to cooperate.

Now, I export the excel as txt with autoit oon script start, read it and go on from there, and when script is done i I'm importing it back so i can use the data on excel. I cant use linebreaks or "|" on cells, but ...ok.

 

Now with the tables i get random "Stack overflow at line 1" and "memory allocation" errors, but I'll make a new topic when I run out of ideas.

Unfortunately, the other available Pc is an older winxp/excel 2007 setup with inferior hardware (@parents house), and I can use it only for testing. I mean, even if it runs there, I cant use it on that pc.

Edited August 14, 2016 by astrolabos