Need to read excel columns into array, columns not next to each other

[andomatic]

Hi, I'm very new to excel manipulation and have a sheet I need to process some data from. The sheet has 29 columns. I need to read by row the information in, for example, columns 2,3,6,9,13,19. I am looking at the _ExcelReadArray and _ExcelReadSheetToArray but neither seems to offer a way to read only certain columns. or read in all the columns and then get an index? I'm feeling like it is right in front of me but for the life of me I'm stumped. Any direction is greatly appreicated

Thanks

Andy

[JLogan3o13]

Hi, andomatic. If you read the entire sheet in and do an _ArrayDisplay you should be able to see how it is sorted, and read only from the columns you would like. One alternative would be to read the sheet in, and then delete the columns you do not want. Something like this:

Edit: Full credit to PsaltyDS for this

_ArrayDeleteCol($aArray, 0)

Func _ArrayDeleteCol(ByRef $aArray, $iCol)
    If Not IsArray($aArray) Then Return SetError(1, 0, 0); Not an array
    If UBound($aArray, 0) <> 2 Then Return SetError(1, 1, 0); Not a 2D array
    If ($iCol < 0) Or ($iCol > (UBound($aArray, 2) - 1)) Then Return SetError(1, 2, 0); $iCol out of range
    If $iCol < UBound($aArray, 2) - 1 Then
        For $c = $iCol To UBound($aArray, 2) - 2
            For $r = 0 To UBound($aArray) - 1
                $aArray[$r][$c] = $aArray[$r][$c + 1]
            Next
        Next
    EndIf
    ReDim $aArray[UBound($aArray)][UBound($aArray, 2) - 1]
    Return 1
EndFunc





Edited August 29, 2013 by JLogan3o13

[water]

If speed is an issue you could try the beta version of my rewrite of the Excel UDF. Function _Excel_RangeRead is much faster (20-50 times).

[kylomas]

andomatic,

This might get you started (same result as JLogan's but skipping the delete part - populate the array directly)

kylomas

expandcollapse popup

#include <Constants.au3>
#include <Excel.au3>
#include <Array.au3>

;---------------------------------------------------------------------------------------------------
; create an Excel test spreadsheet
;---------------------------------------------------------------------------------------------------

local $oExcel       =   _ExcelBookNew()

if @error = 1 then
    msgbox($mb_ok,'Excel Error','Excel Object does not exist' & @lf & 'Object = ' & $oExcel)
    Exit
endif

for $1 = 1 to 10
    for $2 = 1 to 29
        _excelwritecell($oExcel,stringformat('[ %02i - %02i ]',$1,$2),$1,$2)
    Next
Next

;---------------------------------------------------------------------------------------------------
; create a 2D array comprised of columns 2,3,6,9,13,19
;---------------------------------------------------------------------------------------------------

local $aResult[11][6]

for $1 = 1 to 10
    $aResult[$1][0] = _ExcelReadCell($oExcel, $1,2)
    $aResult[$1][1] = _ExcelReadCell($oExcel, $1,3)
    $aResult[$1][2] = _ExcelReadCell($oExcel, $1,6)
    $aResult[$1][3] = _ExcelReadCell($oExcel, $1,9)
    $aResult[$1][4] = _ExcelReadCell($oExcel, $1,13)
    $aResult[$1][5] = _ExcelReadCell($oExcel, $1,19)
Next

_arraydisplay($aResult)

_ExcelBookClose($oExcel)

edit: crap! I keep forgetting to close the workbook...code amended

Edited August 29, 2013 by kylomas

[JLogan3o13]

Very nice, kylomas

Edited August 29, 2013 by JLogan3o13

[kylomas]

@JL - Thanks