Jump to content
Sign in to follow this  
andomatic

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
kylomas

andomatic,

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

kylomas

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

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
JLogan3o13

Very nice, kylomas :)

Edited by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
kylomas

@JL - Thanks :)


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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  

×