How to Read data to 2D Array

[MaoMao]

Problem to read Excel data of two columns to 2D array. Please help how to add and change in the line 2D array:

The question: How to read two separate columns to a 2D array.

$aArray2D [50][2] = [[_ExcelReadArray($oExcel, 2, 1, 50, 1), _ExcelReadArray($oExcel, 2, 5, 50, 1)]]

Other than writing the cells one by one, are there better method?

Only Able to read array separately to 1D array

$aArray1 = _ExcelReadArray($oExcel, 2, 1, 50, 1) ;Direction is Vertical

$aArray2 = _ExcelReadArray($oExcel, 2, 5, 50, 1) ;Direction is Vertical

Any function to combine the $aArray1 and $aArray2 to a 2D [50][2] array?

After processing:

Are there function to separate the 2D array to 1D array?

ExcelWriteSheetFromArray($oExcel, $aArray, $StartROW, $StartCOL, 0, 0) ;

Alternatively, how to just write the result column two of the array back to End Output Excel file.

Edited January 6, 2012 by MaoMao

[goldenix]

very confusing question, try to post a screenshot of your exel sheet & explain what you want to do with it.

Or let me try to guess:

You have 2, 1D arrays & you want to combine them into 1, 2D array.

I made 2 samples, check them out:

expandcollapse popup

;# ====================================================================================
;# Sample 1
;# First make 2d array, make sure u know how many entries u need, hire I made  6 rows
#include <Array.au3>

Global $aArray1[6]

$aArray1[1] = "Holger"
$aArray1[2] = "Jon"
$aArray1[3] = "Larry"
$aArray1[4] = "Jeremy"
$aArray1[5] = "Valik"
_ArrayDisplay($aArray1, '$aArray1') ; show it

Global $aArray2[6]

$aArray2[1] = "lger"
$aArray2[2] = "n"
$aArray2[3] = "rry"
$aArray2[4] = "remy"
$aArray2[5] = "lik"
_ArrayDisplay($aArray2, '$aArray2') ; show it

;# Creating 2D array
Global $2D_arr[6][2] ; 6 rows & 3 columns
_ArrayDisplay($2D_arr, '$2D_arr') ; show it

_fill_2d_array($aArray1, 0)
_ArrayDisplay($2D_arr, '$2D_arr') ; show it
_fill_2d_array($aArray2, 1)
_ArrayDisplay($2D_arr, '$2D_arr') ; show it

;# Loop through given array & pur its contents into 2d Array
func _fill_2d_array($_Input_Array, $Column)
   For $i = 1 To UBound($_Input_Array) -1
      $2D_arr[$i][$Column] = $_Input_Array[$i]
   Next
EndFunc

expandcollapse popup

;# =====================================================================================
;# Sample 2 in case you do not know how many entries you will have in your 1D arrays
;# In this sample we will make new row, each time We add new data to 2d array.
;# This sample is using ReDim , but be aware Redim will slow down performance.!

#include <Array.au3>

Global $aArray1[6]

$aArray1[1] = "Holger"
$aArray1[2] = "Jon"
$aArray1[3] = "Larry"
$aArray1[4] = "Jeremy"
$aArray1[5] = "Valik"
_ArrayDisplay($aArray1, '$aArray1') ; show it

Global $aArray2[6]

$aArray2[1] = "lger"
$aArray2[2] = "n"
$aArray2[3] = "rry"
$aArray2[4] = "remy"
$aArray2[5] = "lik"
_ArrayDisplay($aArray2, '$aArray2') ; show it

;# Creating 2D array
Global $2D_arr[1][2] ; 1 row, 3 columns

_fill_2d_array_sample2($aArray1, 0)
_ArrayDisplay($2D_arr, '$2D_arr') ; show it

_fill_2d_array_sample2($aArray1, 1)
_ArrayDisplay($2D_arr, '$2D_arr') ; show it


;# Loop through given array & pur its contents into 2d Array
func _fill_2d_array_sample2($_Input_Array, $Column)

   For $i = 1 To UBound($_Input_Array) -1

      $rows     = UBound($2D_arr, 1)
      $columns  = UBound($2D_arr, 2)
      ;# ; if not enough rows, add 1 more row, we needif hire because each time we call this function we add rows.
      ;# but if we already have enough rows, we dont want to add more, if dont understand, remove if & see what happens
      ;ReDim $2D_arr[$rows+1][$columns]
      If $rows <= $i Then  ReDim $2D_arr[$rows+1][$columns]

      $2D_arr[$i][$Column] = $_Input_Array[$i]
   Next
EndFunc

[UEZ]

I don't know whether this is a good approach to convert a 2D array to 1D arrays:

;coded by UEZ 2012 (alpha version)
#include <array.au3>

Global $aArray[6][3] = [[1, "A", "1111"], [2, "B", "2222"], [3, "C", "3333"], [4, "D", "4444"], [5, "E", "5555"], [6, "F", "6666"]]

$a = _Array2DSplit($aArray)
For $i = 0 To UBound($a) - 1
    _ArrayDisplay($a[$i])
Next


Func _Array2DSplit($a2DArray)
    If Not IsArray($a2DArray) Then Return SetError(1, 0, 0)
    If UBound($a2DArray, 2) < 1 Then Return SetError(2, 0, 0)
    Local $aWidth = UBound($a2DArray, 2), $aHeight = UBound($a2DArray)
    Local $a1DArrays[$aWidth]
    Local $i, $j, $aTmp
    For $i = 0 To $aWidth - 1
            Dim $aTmp[$aHeight]
            For $j = 0 To $aHeight - 1
                $aTmp[$j] = $a2DArray[$j][$i]
            Next
            $a1DArrays[$i] = $aTmp
    Next
    $aTmp = 0
    Return $a1DArrays
EndFunc

It will create one array with n 1D arrays. It is not fully tested and may have bugs.

For concatenate two 1D arrays have a look to _ArrayConcatenate() in help file.

Br,

UEZ

Edited January 6, 2012 by UEZ

[kylomas]

MaoMao,

Did you look at the UDF "_ExcelReadSheetToArray"?

kylomas

[MaoMao]

Thanks.

goldenix

_fill_2d_array($aArray2, 1)

_ArrayDisplay($2D_arr, '$2D_arr 1 ') ; Column 2

For the above line:fill_2d_array already fill and able to display Column 2 with 2D array.

what does the pur data Loop do exactly?

;# Loop through given array & pur its contents into 2d Array

func _fill_2d_array($_Input_Array, $Column)

UEZ

How to access the 1D array (Vertically)?

Try this display but not work : _ArrayDisplay($a1DArrays, '$a1DArray 1D')

How to access the n 1DArrays?

Able to read separate colums use _ExcelReadArray read data to array.

Need to process the data and write them back to some Excel colums to Output file.

But the source is a html page file with some tables in between. Currently use Excel for convenient.

The _ExcelReadSheetToArray do not working. ( The source got many html, logo formats and merged cells. )

It will be helpful if there are other ways to load them in and out of arrays with Excel.

[Country73]

Not saying this is the best, but threw this together and hope it's gets you on the right path for what your looking for.

*** I don't have any checks success/fail on opening excel or anything like this.

Just a quick and dirty example of what I believe you're looking for.

I have run it against an Excel sheet of mine and works without an issue.

NOTE: Change value for $FColumn and $SColumn for the Columns you wish to read from.

#include <Array.au3>                            ; Handling of the Arrays
#include <Excel.au3>                            ; Handling Excel Operations

Global $Target = "<EXCEL FILE w/PATH>"            ; Full path to Excel File to read from
Global $FColumn = 1                                ; Specify Column to read from
Global $SColumn = 3                                ; Specify Column to read from

Local $oExcel = _ExcelBookOpen($Target,1,True)    ; Open Excel File - FilePath,Visible,ReadOnly
Dim $aArray[1][2] = [["",""]]                    ; Initial creation of 2D Array with values of 0
$i = 1                                            ; Excel Row to Start reading from
Do
    ReDim $aArray[UBound($aArray) + 1][2]        ; Resize Array
    $fVal = $oExcel.Cells($i,$FColumn).Value    ; First Column contents
    $sVal = $oExcel.Cells($i,$SColumn).Value    ; Third Column contents
    $aArray[0][0] = $aArray[0][0] + 1            ; Row One, Cell One of Array to hold total count
    $aArray[$i][0] = $fVal                        ; First Cell content to array
    $aArray[$i][1] = $sVal                        ; Second Cell content to array
    $i += 1                                        ; increment our count for next row in Excel and Array
Until $oExcel.Cells($i,1).Value = ""            ; Read Excel sheet until cell content is blank
_ExcelBookClose($oExcel)                        ; Close Excel

_ArrayDisplay($aArray,"Excel Contents")            ; Show what was extracted
Exit                                            ; Self explanatory

Edit: Added some more explanations in coding

Edited January 6, 2012 by Country73

[UEZ]

...

UEZ

How to access the 1D array (Vertically)?

Try this display but not work : _ArrayDisplay($a1DArrays, '$a1DArray 1D')

How to access the n 1DArrays?

...

Each of the 1D arrays are saved in the memory and you can access each of them accessing the array $a1DArrays. In $a1DArrays the pointer to n 1D arrays are saved!

That means if you want to display e.g. the 2nd 1D array use

_ArrayDisplay($a[1])

To access the one of the 1D arrays you have to use a dummy variable (copy) for it. E.g. the 3rd 1D array:

$a = _Array2DSplit($aArray)

;create a copy
$aTmp = $a[2]
_ArrayDisplay($aTmp)

;change the 1st entry
$aTmp[0] = "Changed"
_ArrayDisplay($aTmp)

;write back to original array
$a[2] = $aTmp

This was my first idea and as mentioned it may not be the best approach.

Btw, I'm not familiar with the excel stuff.

Br,

UEZ

Edited January 6, 2012 by UEZ

[goldenix]

Thanks.

goldenix

what does the pur data Loop do exactly?

;# Loop through given array & pur its contents into 2d Array

func _fill_2d_array($_Input_Array, $Column)

It should be Put not pur , a typing mistake sry.

okey let me explain the whole example 1.

As you saw I created 2, one dimensional arrays,. $aArray1[6] and $aArray2[6], and gave them both 6 row each. then I filled those 2 arrays with values like this:

$aArray1[1] = "Holger"

Now I created a third, two dimensional array $2D_arr[6][2] and gave it 6 rows & 3 columns. when you run the code you will see the array has 3 columns but the first column is used by default to display row numbers. Its the first row from the left. This leaves us 2 empty columns.

Now we want to loop through the first array & put its contents into the first empty column of the 2D array using this code, Note that this is a recursive function, different from functions we normally create, there are some variables in the brackets: First variable $aArray1 is the name of the array we want to take the data from, & second variable is the column number of the 2D array, we know that the empty column nr is 0. we use this line to call an actual function.

_fill_2d_array($aArray1, 0)

Now this is the actual function,

func _fill_2d_array($_Input_Array, $Column)

$_Input_Array is basically any array given in the function call, atm its $aArray1 & it will be used as the array we read data from.

$Column is the column number of the 2d array we want to put the data into.

This line tells us that we start looping from row nr1 up to the (lenght of the input array)

For $i = 1 To UBound($_Input_Array) -1

This line actually fills in the 2d array given column in the function call (in this example its 0), with the data it reads from the $_Input_Array

$2D_arr[$i][$Column] = $_Input_Array[$i]

You know how to use Loops right?

Maybe you are simply not familiar with recursive functions ?

If you still dont get it, I can explain you how to use a loop & how to use/create recursive functions.

Edited January 7, 2012 by goldenix