Excel array

[venkat]

Hi,

I have a excel sheet which contains data from row 8 column 4 and it numeric. I have to read this to array by direction downwards till valid data is there in column. I need to count the column. With this column i have to send each value to an application and get the corresponding data to array and write that to excel.

I used this function,

_ExcelReadArray($oExcel, 8, 4, $iNumCells, $iDirection = 0, $iIndexBase = 0)

If Not IsNumber($iNumCells)

How to increase the count of $iNumCells.

But it throws error to me.

Could anybody help me out.

Regards,

Venkatraman.

[MrMitchell]

What is the error message?? Do you have some code we could test? In your line:

_ExcelReadArray($oExcel, 8, 4, $iNumCells, $iDirection = 0, $iIndexBase = 0)

You should change $iDirection to 1 because you want to read downward. Get rid of the $iDirection and $iIndexBase and just leave their values of 1 and 0, respectively. $iNumCells needs to be a number or you need to somehow assign a value to that variable prior to running that line of code. Same with $oExcel. You may already have those done but you didn't post code so we don't know...

Example:

Local $oExcel = _ExcelBookAttach("C:\temp\Book1.xls")     ;$oExcel object attaches to C:\temp\Book1.xls
$iNumCells = 50                                                                     ; How many cells you want to read
$aArray1 = _ExcelReadArray($oExcel, 8, 4, $iNumCells, 1);  Read 50 elements into $aArray1 starting at Row 8 column 4 in downward Direction

Say you really only had 40 cells that actually had any values, you would then have 10 empty elements at the end if your array. You could just process these empty elements out later on.

How many elements do you expect in the array after executing your code? What's the min and max you would expect?

[venkat]

What is the error message?? Do you have some code we could test? In your line:

_ExcelReadArray($oExcel, 8, 4, $iNumCells, $iDirection = 0, $iIndexBase = 0)

You should change $iDirection to 1 because you want to read downward. Get rid of the $iDirection and $iIndexBase and just leave their values of 1 and 0, respectively. $iNumCells needs to be a number or you need to somehow assign a value to that variable prior to running that line of code. Same with $oExcel. You may already have those done but you didn't post code so we don't know...

Example:

Local $oExcel = _ExcelBookAttach("C:\temp\Book1.xls")    ;$oExcel object attaches to C:\temp\Book1.xls
$iNumCells = 50                                                                 ; How many cells you want to read
$aArray1 = _ExcelReadArray($oExcel, 8, 4, $iNumCells, 1);  Read 50 elements into $aArray1 starting at Row 8 column 4 in downward Direction

Say you really only had 40 cells that actually had any values, you would then have 10 empty elements at the end if your array. You could just process these empty elements out later on.

How many elements do you expect in the array after executing your code? What's the min and max you would expect?

Thanks Mitch.

The elements in array may vary. That's the problem i am facing.

I got the values in the array. How to process the empty elements in the array.

If $aArray1[0] = ' ' then i should not send to the application.

I also need the info, like is it possible to increase the count one by one and read the value to check whether it is empty in column 4.

[MrMitchell]

Maybe you should use _ExcelReadCell instead. Read each cell starting with row 8 column 4, then increase row by 1 and input into an array as you go. When it detects empty cell have it stop. Gimme a few and I'll try to write something up as I've never tried this before.

[MrMitchell]

Maybe something like this:

Local $oExcel = _ExcelBookOpen("C:\temp\Book1.xls")

$startRow = 8
$lastRow = $startRow
$column = 4

While 1
    $nextCell = _ExcelReadCell($oExcel, $lastRow, $column)
    If $nextCell = "" Then ExitLoop
    ConsoleWrite("$lastRow = " & $lastRow & ", column = " & $column & ", $nextCell = " & $nextCell & @CRLF)
    $lastRow += 1
WEnd

$aArray1 = _ExcelReadArray($oExcel, $startRow, $column, $lastRow - $startRow, 1) ;Direction is Vertical
_ArrayDisplay($aArray1, "Vertical")

$startRow is where you want to start, at 8.

$column is what column you want to read, 4.

$lastRow is where you want to end, by default the same as $startRow

The while loop will start at the first cell, examine it and if it contains a value it will increase your $lastRow value by 1.

It will continue to loop until $nextCell = "" (nothing), then break out of the loop.

The _ExcelReadArray will use the $lastRow value to determine where the values end then pull it into a 0-based array for you called $aArray1.

_ArrayDisplay just gives you visual of the array so you know what it looks like.

Get it?

Edit: You're essentially going through the cells twice over; once to check where the values start and end, then again to read them into an array. Probably not the best way to go in terms of efficiency, but it gets the job done. You could also just read them into an array as you go but I imagine the array would need to be ReDim in case it wasn't declared with enough elements. You can figure that part out.

Edited May 9, 2009 by MrMitchell

[venkat]

Maybe something like this:

Local $oExcel = _ExcelBookOpen("C:\temp\Book1.xls")

$startRow = 8
$lastRow = $startRow
$column = 4

While 1
    $nextCell = _ExcelReadCell($oExcel, $lastRow, $column)
    If $nextCell = "" Then ExitLoop
    ConsoleWrite("$lastRow = " & $lastRow & ", column = " & $column & ", $nextCell = " & $nextCell & @CRLF)
    $lastRow += 1
WEnd

$aArray1 = _ExcelReadArray($oExcel, $startRow, $column, $lastRow - $startRow, 1) ;Direction is Vertical
_ArrayDisplay($aArray1, "Vertical")

$startRow is where you want to start, at 8.

$column is what column you want to read, 4.

$lastRow is where you want to end, by default the same as $startRow

The while loop will start at the first cell, examine it and if it contains a value it will increase your $lastRow value by 1.

It will continue to loop until $nextCell = "" (nothing), then break out of the loop.

The _ExcelReadArray will use the $lastRow value to determine where the values end then pull it into a 0-based array for you called $aArray1.

_ArrayDisplay just gives you visual of the array so you know what it looks like.

Get it?

Edit: You're essentially going through the cells twice over; once to check where the values start and end, then again to read them into an array. Probably not the best way to go in terms of efficiency, but it gets the job done. You could also just read them into an array as you go but I imagine the array would need to be ReDim in case it wasn't declared with enough elements. You can figure that part out.

I got that thank you very much.

I have another clarification in this.

I have a excel sheet. In that I have to copy the data in the column 4 from Row 2 till the empty cell which i did exactly.

But Now,

For Example.

col1 col2 col3 col4 col5 col6 col7 col8

row1 a b c 1235856 r t y second

row2 a b c 2356879 t y u first

row2 a b c 2356879 t y u second

I have to copy the col4 from row 2 unitl it is blank on col8 = second.

Thanks in advance

[venkat]

Hi,

I have a excel sheet. In that I have to copy the data in the column 4 from Row 2 till the empty cell which i did exactly.

But Now,

For Example.

col1 col2 col3 col4 col5 col6 col7 col8

row1 a b c 1235856 r t y second

row2 a b c 2356879 t y u first

row2 a b c 2356879 t y u second

I have to copy the col4(which is seven digit number) from row 2 unitl it is blank on col8 = second.

Col 8( which has second or first has entry)

Copy the Column4(which is seven digit number) to array from excel sheet when column8(which has 'second') until column4 is blank.

I tried with Excel read array but not working.

Thanks in advance

Edited May 11, 2009 by venkat

[MrMitchell]

I'm sure whatever you want to do now is very possible, but I'm a little confused... are you trying to read values from column 4 only when column 8 contains the word "second" or until column 8 is at an empty cell?

[venkat]

I'm sure whatever you want to do now is very possible, but I'm a little confused... are you trying to read values from column 4 only when column 8 contains the word "second" or until column 8 is at an empty cell?

I am trying to read column4 when column 8 is "second" this should be done until column 8 is blank.

Thanks.

[MrMitchell]

You said you tried already, can you post the code? _ExcelReadArray() may work if you know when the list ends, but may not be the easiest way to go because you don't want every row.

What I gave you earlier can easily be tweaked to suit your next task. It's very easy as a matter of fact... give it a shot and post what you come up with.

[venkat]

You said you tried already, can you post the code? _ExcelReadArray() may work if you know when the list ends, but may not be the easiest way to go because you don't want every row.

What I gave you earlier can easily be tweaked to suit your next task. It's very easy as a matter of fact... give it a shot and post what you come up with.

What I did is actullay used your own code, But instead of picking the row which has second using autoit. I wrote a macro to filter the other things in excel.

Then i used the same code.

But it would be good if i do that in autoit itself

[venkat]

What I did is actullay used your own code, But instead of picking the row which has second using autoit. I wrote a macro to filter the other things in excel.

Then i used the same code.

But it would be good if i do that in autoit itself

Code which i tried.

$startRow = 2

$lastRow = $startRow

$column = 8

$column1 = 4

While 1

$nextCell = _ExcelReadCell($oExcel, $lastRow, $column)

If $nextCell = "Secondary Incident" then

$nextcell1 = _ExcelReadcell($oExcel, $lastRow, $column1)

_ExcelwriteArray(10,1 $aArray)

ConsoleWrite("$lastRow = " & $lastRow & ", column = " & $column & ", $nextCell = " & $nextCell & @CRLF)

$lastRow += 1

ElseIf $nextcell = " " Then

EndIf

WEnd

EndFunc

[MrMitchell]

Yah you're getting there... you can also place code tags around your code to format it. [ code] code here, but remove the space after opening bracket [ /code]

Here's what I came up with:

Local $oExcel = _ExcelBookOpen("C:\temp\Book1.xls")
Local $array2[1], $counter = 0

MsgBox(0, "", "")
$row = 2
$readColumn = 4
$checkColumn = 8

While 1
    $nextCell = _ExcelReadCell($oExcel, $row, $checkColumn)
    If $nextCell = "Second" Then 
        _ArrayAdd($array2, _ExcelReadCell($oExcel, $row, $readColumn))
        $counter += 1
    ElseIf $nextCell = "" Then 
        ExitLoop
    EndIf
    $row += 1
WEnd

_ArrayDisplay($array2, "All rows with ""Second"" as column 8 value")

Edited May 12, 2009 by MrMitchell

[PsaltyDS]

The function _ExcelReadSheetToArray() detects the used extent of the sheet, so you don't have to tell it how many rows/cols to get:

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

; Read out only Cols 4-8 of rows 2 thru the end
Global $oExcel = _ExcelBookOpen("C:\temp\Book1.xls")
Global $avData = _ExcelReadSheetToArray($oExcel, 2, 4, 0, 5) 
_ArrayDisplay($avData, "$avData Before")

; Delete rows without "Second"
For $n = $avData[0][0] To 1
    If $avData[$n][5] = "Second" Then
        ContinueLoop
    Else
        _ArrayDelete($avData, $n)
    EndIf
Next
$avData[0][0] = Ubound($avData) - 1
_ArrayDisplay($avData, "$avData After")

This pulls just the elements you want to test into a 2D array, then deletes all rows that don't have "Second" in col 8 (which was read into col 5 of $avData).