Sign in to follow this  
Followers 0
ACalcutt

Read From Excel Faster?

10 posts in this topic

#1 ·  Posted (edited)

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 by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

no ideas?

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

#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

[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 by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?...


Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

how do i read this without sql?

Edited by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

:mellow:

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")

#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 by ACalcutt

Andrew Calcutt

Http://www.Vistumbler.net

Http://www.TechIdiots.net

Its not an error, its a undocumented feature

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0