Jump to content
Sign in to follow this  
mugh

_ExcelReadSheetToArray() = empty array?

Recommended Posts

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

Share this post


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

When the words fail... music speaks

Share this post


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

Share this post


Link to post
Share on other sites
Andreik

You must open xls file first:

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

PS: Don't forget

#Include <Excel.au3>
Edited by Andreik

When the words fail... music speaks

Share this post


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

Share this post


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


When the words fail... music speaks

Share this post


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

When the words fail... music speaks

Share this post


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

Share this post


Link to post
Share on other sites
mugh

Hello Andreik,

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

Martin

Share this post


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

Share this post


Link to post
Share on other sites
Andreik

What version of Autoit use?


When the words fail... music speaks

Share this post


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

Share this post


Link to post
Share on other sites
mugh

3.2.12.1 regular version

Share this post


Link to post
Share on other sites
mugh

Hello,

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

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

Share this post


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


When the words fail... music speaks

Share this post


Link to post
Share on other sites
picaxe

Depending on what object info you want you need something like

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

Share this post


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

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

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  

×