_ExcelReadSheetToArray() = empty array?

[mugh]

Hello,

I used Excel.au3 / _ExcelReadSheetToArray() to read an Excel file into an array. The Excel file consists of only one sheet containing 600 rows and 7 columns.

I tried the following code. But it returns nothing, when callung _ArrayDisplay().

Here are my questions:

- How can one read the content of the Excelsheet into an array? Alternatively, how is it possible to read from an CSV file into an array?

- How can one transfer that data/array to include it into AutoIT V3 source code in the form

myarray [row0/col0] [row1/col1] .. [rowX/colX]

.

The use is, that one has all data in source code and that there are no external files necessary at runtime.

- What ist the right way to address (read/write) one element within an array (e.g.

$myelement = myarray [1] [2]; addresses the element contained in the 2nd row and 3rd column ?

That is what I tried to solve the first problem;

#include <GUIConstants.au3>
; #include <ExcelCOM_UDF.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

#cs
    format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <-
    declare an array for the login data sets first
#ce
Dim $arLogin[1000][7]

; Declare variables for Excel.au3
$oExcel = "liste.xls"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row)
$iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column)
;$iRowCnt =; $iRowCnt - Count of rows to read, defaults to 0 (all)
;$iColCnt =; $iColCnt - Count of columns to read, defaults to 0 (all)
;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.

; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3
$arLogin = _ExcelReadSheetToArray($oExcel)

; Alternative method explicitly mentioning the defaults
; $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt)

_ArrayDisplay($arLogin,"ArrayDisplay")
MsgBox(0, "MyArraqy", $arLogin)

Martin Haneke

[Andreik]

Hello,

I used Excel.au3 / _ExcelReadSheetToArray() to read an Excel file into an array. The Excel file consists of only one sheet containing 600 rows and 7 columns.

I tried the following code. But it returns nothing, when callung _ArrayDisplay().

Here are my questions:

- How can one read the content of the Excelsheet into an array? Alternatively, how is it possible to read from an CSV file into an array?

- How can one transfer that data/array to include it into AutoIT V3 source code in the form

myarray [row0/col0] [row1/col1] .. [rowX/colX]

.

The use is, that one has all data in source code and that there are no external files necessary at runtime.

- What ist the right way to address (read/write) one element within an array (e.g.

$myelement = myarray [1] [2]; addresses the element contained in the 2nd row and 3rd column ?

That is what I tried to solve the first problem;

#include <GUIConstants.au3>
; #include <ExcelCOM_UDF.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

#cs
    format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <-
    declare an array for the login data sets first
#ce
Dim $arLogin[1000][7]

; Declare variables for Excel.au3
$oExcel = "liste.xls"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row)
$iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column)
;$iRowCnt =; $iRowCnt - Count of rows to read, defaults to 0 (all)
;$iColCnt =; $iColCnt - Count of columns to read, defaults to 0 (all)
;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.

; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3
$arLogin = _ExcelReadSheetToArray($oExcel)

; Alternative method explicitly mentioning the defaults
; $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt)

_ArrayDisplay($arLogin,"ArrayDisplay")
MsgBox(0, "MyArraqy", $arLogin)

Martin Haneke

Example:

$FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]])
$ROW1_COL1 = $FILE[1][1]
$ROW4_COL5 = $FILE[4][5]

[mugh]

Dear Andreik,

thanks for that quick response.

I set values for the variables now although it was mentioned that Excel.au3 contained the defaults for

$iStartRow, $iStartColumn, $iRowCnt, $iColCnt

Now I have the error:

ExcelArrayTest.au3 (32) : ==> Subscript used with non-Array variable.:

; Declare variables for Excel.au3
$oExcel = "liste.xls"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row)
$iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column)
$iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all)
$iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all)
;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.




; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3
;$FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]); Andreik´s example
 
;$arLogin = _ExcelReadSheetToArray($oExcel); deactivated

$arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt); changed 

$ROW1_COL1 = $arLogin[1][1]; added
$ROW4_COL5 = $arLogin[4][5]; added

;  _ArrayDisplay($arLogin,"OtherArray")
_ArrayDisplay($arLogin,"Display arLogin")

MsgBox(0, "MyArraqy", $arLogin)
MsgBox(0, "MyArraqy", $ROW1_COL1); added
MsgBox(0, "MyArraqy", $ROW4_COL5); added

Martin

[Andreik]

You must open xls file first:

$oExcel = _ExcelBookOpen($PATH & "\liste.xml",0)

PS: Don't forget

#Include <Excel.au3>

Edited July 27, 2008 by Andreik

[mugh]

Hello Andreik,

the problem stays the same: The array seems to have no content. Both _ArrayDisplay and MSsgBox return empty values.

Although I opened it first with $oExcel = _ExcelBookOpen($PATH & "\liste.xml",0)

And I defined the $PATH without trailing backslash.

BTW: Is it necessary to open the XLS-file first with $oExcel = _ExcelBookOpen() ?

I thought that this would have been done by _ExcelReadSheetToArray()

Martin

[Andreik]

Hello Andreik,

the problem stays the same: The array seems to have no content. Both _ArrayDisplay and MSsgBox return empty values.

Although I opened it first with $oExcel = _ExcelBookOpen($PATH & "\liste.xml",0)

And I defined the $PATH without trailing backslash.

BTW: Is it necessary to open the XLS-file first with $oExcel = _ExcelBookOpen() ?

I thought that this would have been done by _ExcelReadSheetToArray()

Martin

Yes it`s necessary.

First parameter of _ExcelReadSheetToArray() is $oExcel.

$oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

[Andreik]

I write an example:

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

$FILE = FileOpenDialog("OPEN",@DesktopDir,"Excel Files (*.xls)",1)
$Excel = _ExcelBookOpen($FILE,0)
$DATA = _ExcelReadSheetToArray($Excel,1,1,10,7)
_ArrayDisplay($DATA)
MsgBox(0,"TEST",$DATA[5][5])
_ExcelBookClose($Excel,0)

[mugh]

Andreik,

sorry. I read it meanwhile.

But I still have the problem, that I have empty values. Now I see, that it is the missing Excel object. I do not know why the object turns out to be empty. Any ideas?

Dim $oExcel = _ExcelBookOpen("C:\liste2.xls",0)
..
MsgBox(0, "Excelobject", $oExcel)

Martin

[mugh]

Hello Andreik,

let me test Your example first. And thank You so far.

Martin

[mugh]

Hello Anreik,

You won´t believe it: I took Your source, put it into a new File and together with book1.xls, excel.au3 and array.au3 into their own directory. Resulkt: empty values and no message box. I hav not the faintest idea what the problem is.

BTW: I shut down the abandoned excel processes which resided in my computer´s memory before I started the script.

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

$FILE = FileOpenDialog("OPEN","C:\book1","Excel Files (*.xls)",1)
$Excel = _ExcelBookOpen($FILE,0)
$DATA = _ExcelReadSheetToArray($Excel,1,1,10,7)
_ArrayDisplay($DATA)
MsgBox(0,"TEST",$DATA[5][5])
_ExcelBookClose($Excel,0)

Error Log says:

>"C:\Programme\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Book1\ExcelArrayTest02.au3" /autoit3dir "C:\Programme\AutoIt3" /UserParams  
+>16:10:25 Starting AutoIt3Wrapper v.1.10.1.8   Environment(Language:0407  Keyboard:00000407  OS:WIN_XP/Service Pack 2  CPU:X86)
>Running AU3Check (1.54.13.0)  from:C:\Programme\AutoIt3
+>16:10:25 AU3Check ended.rc:0
>Running:(3.2.12.1):C:\Programme\AutoIt3\autoit3.exe "C:\Book1\ExcelArrayTest02.au3"    
C:\Book1\ExcelArrayTest02.au3 (8) : ==> Subscript used with non-Array variable.:
MsgBox(0,"TEST",$DATA[5][5])
MsgBox(0,"TEST",$DATA^ ERROR
->16:10:28 AutoIT3.exe ended.rc:1
>Exit code: 1   Time: 3.656

Martin

[Andreik]

What version of Autoit use?

[picaxe]

Start column and row should be 1. This works for me

#include <GUIConstants.au3>
; #include <ExcelCOM_UDF.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

#cs
    format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <-
    declare an array for the login data sets first
#ce
Dim $arLogin[1000][7]
; Declare variables for Excel.au3
$Excel = "liste.xls"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row)
$iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column)
$iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all)
$iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all)
;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.

$oExcel = _ExcelBookOpen(@ScriptDir & "\" & $Excel)

; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3
;$FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]); Andreik´s example

$arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt); changed 

;~ $ROW1_COL1 = $arLogin[1][1]; added
;~ $ROW4_COL5 = $arLogin[4][5]; added

;  _ArrayDisplay($arLogin,"OtherArray")
_ArrayDisplay($arLogin,"Display arLogin")

;~ MsgBox(0, "MyArraqy", $arLogin)
;~ MsgBox(0, "MyArraqy", $ROW1_COL1); added
;~ MsgBox(0, "MyArraqy", $ROW4_COL5); added

[mugh]

3.2.12.1 regular version

[mugh]

Hello,

does anbody has an idea why the following code returns an empty value for the excelsheet?

expandcollapse popup

#include <GUIConstants.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

#cs
    format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <-
    declare an array for the login data sets first
#ce
Dim $arLogin[1000][7]


; Declare variables for Excel.au3
; Dim $oExcel = "xxxxx"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
Dim $iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row)
Dim $iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column)
Dim $iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all)
Dim $iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all)
;Dim $iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column.  False by Default to match R1C1 values.

; Syntax of _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]])
; $FILE = FileOpenDialog("OPEN","C:\book1","Excel Files (*.xls)",1)
$FILE = "C:\book1\book1.xls"
$oExcel = _ExcelBookOpen($FILE,0)

; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3
;$arLogin = _ExcelReadSheetToArray($oExcel); simple variant
$arLogin = _ExcelReadSheetToArray($oExcel,$iStartRow,$iStartColumn,$iRowCnt,$iColCnt)

; Display Excel and other objects
MsgBox(0, "Excelobject", $oExcel)

_ArrayDisplay($arLogin,"Display arLogin")

MsgBox(0, "MyArray", $arLogin)

; Close Excel Sheet
; Syntax: _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
_ExcelBookClose($oExcel,0,0)

[Andreik]

Code work fine. Look at your xls, maybe there is something wrong.

PS: Don't use

MsgBox(0, "Excelobject", $oExcel)

Will not return nothing.

Use

MsgBox(0, "Excelobject", $oExcel[0])

to view number of subscripts or a for loop to view values from array.

[picaxe]

Depending on what object info you want you need something like

MsgBox(0,"Excel File Name", $oExcel.ActiveWorkbook.Name)

[mugh]

Hello,

I did not solve my problem yet. But it turned out that _ExcelReadSheetToArray() is missing its first and most important parameter ($FilePath). While I did not found out what the problem is, may be somebody has a hint on me?

I left the parameters on defaults and "$oExcel" seems to be emtpy when calling _ExcelreadToArray(). Although MsgBox (0,$oExcel) shows up with value "C:\Book1\Books1.xls". Shotrly afteer I call _ExcelReadSheetToArray() returning errorcode 2. That means "@error=2 - Start parameter out of range".

Syntax: _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]])

expandcollapse popup

#include <GUIConstants.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

Dim $arLogin

; Declare variables for Excel.au3
;Dim $oExcel = "liste.xls"; Filename;  Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
$iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row)
$iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column)
$iRowCnt = 0; $iRowCnt - Count of rows to read, defaults to 0 (all)
$iColCnt = 0; $iColCnt - Count of columns to read, defaults to 0 (all)
$iNumCells = 6
$iIndexBase = 0
$iDirection = 0
$iColShift = False
$fReadOnly = True
$fVisible = 0
$sPassword = ""
$sWritePassword = ""
$sFilePath = "C:\Book1\Book1.xls"
$oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly, $sPassword, $sWritePassword)

MsgBox (0,"File Path",$sFilePath)
MsgBox (0,"ExitCode OpenBook",@error)

$arLogin = _ExcelReadSheetToArray($oExcel,$iStartRow,$iStartColumn,$iRowCnt,$iColCnt,$iColShift)
MsgBox (0,"ExitCode ExcelReadtoArray",@error)
MsgBox (0,"Excel Object",$oExcel)
MsgBox (0,"StartRow",$iStartRow)
MsgBox (0,"StartCol",$iStartColumn)
MsgBox (0,"RowCnt",$iRowCnt)
MsgBox (0,"ColCnt",$iColCnt)
MsgBox (0,"ColShift",$iColShift)

_ArrayDisplay($arLogin,"Display arLogin as list")
_ExcelBookClose($oExcel,0,0)
_ArrayDisplay($arLogin,"Display arLogin as list")