Read From Excel Faster?

[ACalcutt]

I am writing a script that tallys up a list of parts i order at work.

right now i am just trying to figure out the best way to get the data from the excel document.

using the excelcom udf i tried this....but it is really slow...any ideas to do it faster?

#include<ExcelCom.au3>
#include<Array.au3>
Dim $row=4
Dim $array[1]
Dim $ReadXLPath=@ScriptDir & "\test.xls"

While 1
    $contents = _XLRowToString($ReadXLPath,1, "A:H",$row)
    If StringInStr($contents, ",,,,,,,") And StringInStr(_XLRowToString($ReadXLPath,1, "A:H",$row + 1), ",,,,,,,") Then ExitLoop
    ToolTip($row)
    _ArrayAdd ( $array, $contents )
    $array[0] += 1
    $row += 1
WEnd

_ArrayDisplay ( $array, "" )

Edited March 23, 2006 by ACalcutt

[ACalcutt]

no ideas?

still using the slow command this is what i have so far

expandcollapse popup

#include<ExcelCom.au3>
#include<Array.au3>
#include <GuiConstants.au3>

$settings = @ScriptDir & "\settings.ini"
IniDelete($settings, "totals")
Dim $category
Dim $row = 4
Dim $array[1], $totalslist[1]
Dim $ReadXLPath = @ScriptDir & "\test.xls"
$array[0] = 0

While 1
    $contents = _XLRowToString ($ReadXLPath, 1, "A:H", $row)
    If StringInStr($contents, ",,,,,,,") And StringInStr(_XLRowToString ($ReadXLPath, 1, "A:H", $row + 1), ",,,,,,,") Then ExitLoop
    ToolTip($contents)
    If Not StringInStr($contents, ",,,,,,,") Then
        If StringInStr($contents, ",,") Then ExitLoop
        _ArrayAdd($array, $contents)
        $array[0] += 1
    EndIf
    $row += 1
WEnd

For $loop = 1 To $array[0]
    $found = 0
    $splitstring = StringSplit($array[$loop], ",")
    $var = IniReadSection($settings, "defs")
    If @error Then
        MsgBox(4096, "", "Error occured, probably no INI file.")
    Else
        For $i = 1 To $var[0][0]
            If StringInStr($var[$i][1], $splitstring[1]) Then
                $found = 1
                IniWrite($settings, "totals", StringLower($splitstring[2] & "|" & $var[$i][0]), IniRead($settings, "totals", StringLower($splitstring[2] & "|" & $var[$i][0]), "0") + 1)
            EndIf
        Next
        If $found = 0 Then
    ;$category = InputBox("what category", $splitstring[1])
            
            
    ;|< pic a category if one does not exist
            GUICreate('Pick a category for "' & $splitstring[1] & '"', 400, 40, -1, -1)
            $category = GUICtrlCreateCombo("", 10, 10, 300, 21)
            $ok = GUICtrlCreateButton("", 320, 10, 70, 20)
    ;read categories
            $categoryiniread = IniReadSection($settings, "defs")
            If Not @error Then
                For $i = 1 To $categoryiniread[0][0]
                    GUICtrlSetData($category, $categoryiniread[$i][0], $categoryiniread[$i][0])
                Next
            EndIf
            GUISetState()
            While 1
                $msg = GUIGetMsg()
                Select
                    Case $msg = $GUI_EVENT_CLOSE Or $msg = $ok
                        $category = GUICtrlRead($category)
                        ExitLoop
                EndSelect
            WEnd
            GUIDelete
    ;>|
            
            If $category <> "" Then
                IniWrite($settings, "defs", $category, StringLower(IniRead($settings, "defs", $category, "") & $category & ","))
                IniWrite($settings, "totals", StringLower($splitstring[2] & "|" & $category), IniRead($settings, "totals", StringLower($splitstring[2] & "|" & $category), "0") + 1)
            EndIf
        EndIf
    EndIf
Next

$totals = IniReadSection($settings, "totals")
For $i = 1 To $totals[0][0]
    _ArrayAdd($totalslist, $totals[$i][0] & "|" & $totals[$i][1])
Next
$totalslist[0] = "*** New Parts***"
_ArrayDisplay($totalslist, "Write Totals?")

settings.ini

expandcollapse popup

[d600]
sheet=1
year_row=E
harddrive=4
keyboard=5
lcd=6
memory=7
cdrom=8
motherboard=9
touchpad=10
processor=11
cover_above_kb=12
bottom_plastics=13
lcd_bezel=14
battery=15
lcd_latch=16
ac_adaptor=17
power_cord=18
laptop=19
top_cover=20
wireless=21

[d610]
sheet=2
year_row=C
harddrive=4
keyboard=5
lcd=6
memory=7
cdrom=8
motherboard=9
touchpad=10
processor=11
cover_above_kb=12
bottom_plastics=13
lcd_bezel=14
battery=15
lcd_latch=16
ac_adaptor=17
power_cord=18
laptop=19
top_cover=20
wireless=21

[defs]
harddrive=harddrive,
keyboard=keyboard,
lcd=
memory=
cdrom=
motherboard=motherboard,
touchpad=
processor=
cover_above_kb=
bottom_plastics=
lcd_bezel=
battery=
lcd_latch=
ac_adaptor=
power_cord=
laptop=
top_cover=
wireless=
[totals]
d600|motherboard=1
d600|harddrive=1
d610|keyboard=1
d600|keyboard=1

eventually this will write back to another excel file

Edited March 24, 2006 by ACalcutt

[randallc]

Hi,

Sorry I don't have the time to look at this right now..

In general terms, you can...

1. Find "lastrow" using on of the udfs

2. set your range in the getarray.

3. read the array, then filesave array as csv

4. fileline process the csv as it will be much quicker.

[You could even just save the whole sheet as csv to start , given the range you appear to be using..?]

Best, Randall

[ACalcutt]

i tried saving this a a csv but that doesn't seem to help much. if you have a chance can you explain the first two things a little more?...

[randallc]

Hi,

Try;

;_XLArrayByLastRowEx.au3

#include"ExcelCom.au3"

$s_FilePath=@ScriptDir&"\book1.xls"

$i_LastRow=_XLLastRow($s_FilePath,1,0)

$XLArray=_XLArrayRead($s_FilePath,1,"A1:H"&$i_LastRow-1)

_XLClose($s_FilePath,1)

;(This gives 2D array, which you can manipulate faster?)

;only display it with the following function if you wish;

;==============================================================================

;exit

#include "SQL_View.au3"

local $sNewInput,$i_Execute=1,$sDb=@ScriptDir&"\sqlite.db3",$sDbTable="Array"

$sNewInput&=_SQL_TableFromArray2D($XLArray,$sDB,$sDBTable,"",1);,$i_Execute

_SQLGUI_View($sDb,$sDbTable,26,0,1)

Best, Randall

[ACalcutt]

i get this error...it seems to be caused by the sql part...

how do i read this without sql?

Edited March 25, 2006 by ACalcutt

[randallc]

Hi,

1. Sure - You can use a message box if the 2Darray is not too big;

or a text file/ csv and open with notepad as in following script.

2. I suspect you have not just run my script, or else "..\beta\include" would not have been called; you must have used #include<Excelcom.au3> instead of #include"Excelcom.au3"; so you'll get the same error?

3. I still think you might want to pout more effort into the "saveAsCsv" option, as it may be easier?... ? post some roubled code for that?

4. There seems to me to be a problem with beta over 113 at present with the include files, so I have gone back to 112. (?? - not sure?; anybody?..)

;_XLArrayByLastRowEx2.au3

#include"ExcelCom.au3"

$s_FilePath=@ScriptDir&"\book1.xls"

$i_LastRow=_XLLastRow($s_FilePath,1,0)

$XLArray=_XLArrayRead($s_FilePath,1,"A1:H"&$i_LastRow-1)

_XLClose($s_FilePath,1)

;==============================================================================

local $s_StringOfSingleLine

for $i=0 to ubound ($XLArray,2)-1

$ar_ArrayOfSingleLine=_Array2DTo1D( $XLArray, "Array contents", 0, $i,0); $s_i_Column = 0)

_ArrayDelete($ar_ArrayOfSingleLine,0)

$s_StringOfSingleLine&=_ArrayToString($ar_ArrayOfSingleLine,",")&@CRLF

Next

$s_ArrayPath=@ScriptDir&"\array.csv"

FileDelete($s_ArrayPath)

;MsgBox(0,"","$s_ArrayFileString="&@CRLF&$s_StringOfSingleLine)

FileWrite($s_ArrayPath,$s_StringOfSingleLine)

RunWait("Notepad.exe " & $s_ArrayPath)

Best, Randall Edited March 25, 2006 by randallc

[ACalcutt]

thankyou.....that puts it into a form that i can use...

one question though....here is an example of on row

the row looks like this in excel

LCD d610 ST##### 3/20/2006 AC 3/22/2006 AC 3/23/2006 AC

but the date cells come out weird in the array (which really doesn't matter since i am only using the last number to tell if the part was recieved)

LCD ,d610,ST#####,20060320000000,AC,20060322000000,AC,20060323000000

i can see how to get the data from that....but how come it has 6 extra zeros on the end

Edited March 25, 2006 by ACalcutt

[ACalcutt]

thank you ...that worked so much better...it takes like 10 seconds now...and it took like 15 minutes before

randallc --->

there a still some problems with the totals no being correct....but thats not a problem with the script i dont think

(i think it has to do with the way i match words....ex..."keyboard plastic" vs. "keyboard")

expandcollapse popup

#include<ExcelCom.au3>
#include<Array.au3>
#include <GuiConstants.au3>

$settings = @ScriptDir & "\settings.ini"
IniDelete($settings, "totals")
Dim $category
Dim $row = 16
Dim $partarray[1], $totalslist[1]
Dim $ReadXLPath = @ScriptDir & "\Laptop parts Ordered.xls"
;Dim $ReadXLPath = "http://sharepoint.worcester.edu/internal/it/Laptop%20Parts/Laptop parts Ordered.xls"
$partarray[0] = 0

$i_LastRow = _XLLastRow ($ReadXLPath, 1, 0)
$XLArray = _XLArrayRead ($ReadXLPath, 1, "A4:H" & $i_LastRow - 1)
_XLClose ($ReadXLPath, 1)
;==============================================================================
Local $contents
For $i = 0 To UBound($XLArray, 2) - 1
    $ar_ArrayOfSingleLine = _Array2DTo1D ($XLArray, "Array contents", 0, $i, 0); $s_i_Column = 0)
    _ArrayDelete($ar_ArrayOfSingleLine, 0)
    $contents = _ArrayToString($ar_ArrayOfSingleLine, ",")
    If Not StringInStr($contents, "0,0,0,0,0,0,0,0") Then
        If StringInStr($contents, ",0,0") Then ExitLoop
        _ArrayAdd($partarray, $contents)
        $partarray[0] += 1
    EndIf
Next


For $loop = 1 To $partarray[0]
    $found = 0
    $splitstring = StringSplit($partarray[$loop], ",")
    $var = IniReadSection($settings, "defs")
    If @error Then
        MsgBox(4096, "", "Error occured, probably no INI file.")
    Else
        For $i = 1 To $var[0][0]
            If StringInStr($var[$i][1], $splitstring[1]) Then
                $found = 1
                IniWrite($settings, "totals", StringReplace(StringLower($splitstring[2] & "|" & $var[$i][0]), " ", ""), IniRead($settings, "totals", StringReplace(StringLower($splitstring[2] & "|" & $var[$i][0]), " ", ""), "0") + 1)
            EndIf
        Next
        If $found = 0 Then
;$category = InputBox("what category", $splitstring[1])
            
            
;|< pic a category if one does not exist
            GUICreate('Pick a category for "' & $splitstring[1] & '"', 400, 40, -1, -1)
            $category = GUICtrlCreateCombo("", 10, 10, 300, 21)
            $ok = GUICtrlCreateButton("", 320, 10, 70, 20)
;read categories
            $categoryiniread = IniReadSection($settings, "defs")
            If Not @error Then
                For $i = 1 To $categoryiniread[0][0]
                    GUICtrlSetData($category, $categoryiniread[$i][0], $categoryiniread[$i][0])
                Next
            EndIf
            GUISetState()
            While 1
                $msg = GUIGetMsg()
                Select
                    Case $msg = $GUI_EVENT_CLOSE Or $msg = $ok
                        $category = GUICtrlRead($category)
                        ExitLoop
                EndSelect
            WEnd
            GUIDelete()
;>|
            
            If $category <> "" Then
                IniWrite($settings, "defs", $category, StringLower(IniRead($settings, "defs", $category, "") & $splitstring[1] & ","))
                IniWrite($settings, "totals", StringReplace(StringLower($splitstring[2] & "|" & $category), " ", ""), IniRead($settings, "totals", StringReplace(StringLower($splitstring[2] & "|" & $category), " ", ""), "0") + 1)
            EndIf
        EndIf
    EndIf
Next

$totals = IniReadSection($settings, "totals")
For $i = 1 To $totals[0][0]
    _ArrayAdd($totalslist, $totals[$i][0] & "|" & $totals[$i][1])
Next
$totalslist[0] = "*** New Parts***"
_ArraySort($totalslist)
_ArrayDisplay($totalslist, "Write Totals?")

Edited March 25, 2006 by ACalcutt

[randallc]

that worked so much better

Glad to hear it!

20060323000000

Has a time component too, I think..

Best, randall