_Excel_RangeRead to Array?

[hyperjase]

Think this may be simpler than I suspect but,

I have a range of cells from a spreadsheet and I need to put them individually into an array to dump into SQL, how do I format from RangeRead to an array?  Couldn't find any suitable topics when I searched...

Edited February 9, 2015 by hyperjase

[MikahS]

If you look at example 2 in the helpfile for _Excel_RangeRead they show how to specify a range, which would return a 0 based array.

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

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range on sheet 2 of the specified workbook
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, 2, "A1:C1", 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C1 of sheet 2.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C1 of sheet 2")

Edited February 9, 2015 by MikahS

[hyperjase]

I've used  _ArrayDisplay but it only shows as a pop up, I need to access the Array in variable form so I can access each one (ie :

$Var[1]

) and insert into SQL via an INSERT statement.  I did read and try that part but doesn't appear to fit the bill of what I was looking for.

Edited February 9, 2015 by hyperjase

[water]

Can you post what you've tried?

[hyperjase]

Currently (non-working) I have this, working down the ArrayInsert route, think I can populate an array from RangeRead .... ?

$oExcel = _Excel_Open(False)
$Excel = _Excel_BookOpen ( $oExcel, $fFile , False, True )
$Output = _Excel_RangeRead ( $Excel, Default,"B12:V12",1)
_ArrayInsert($Array,"",$Output,0,",")
MsgBox(0,"",$Array[1])
_Excel_BookClose ($Excel)
_Excel_Close($oExcel)

The $Output works perfectly when used with ArrayDisplay but just can't figure out the link between what that does to adding each of the 21 figures to an array.  I also have to move down a row to B13:V13 for a second set of figures, all of which need to be dumped into SQL.

Edited February 9, 2015 by hyperjase

[MikahS]

Posting what you have tried, including script, will help us out greatly.

Did you try using _ArrayDisplay($output, "this is the output")

?

EDIT: it looks like you are trying to display the wrong array.

I see how you are using _ArrayInsert now.

EDIT: I will leave this to the gurus

Edited February 9, 2015 by MikahS

[hyperjase]

Sadly my experience with Arrays isn't great, I'm trying to create an array with the data which is output by the RangeRead, but I'm not confident I'm creating the array correctly - I also have no idea if there is a delimiter and which that delimiter actually is.

[water]

Can you please describe in simple terms what _ArrayInsert should do?

[water]

Parameter 2 for _ArrayInsert (set by you to "") is wrong. Please check the help file.

[hyperjase]

The output from RangeRead works fine using ArrayDisplay (shows perfectly the data  I need to extract), using the ArrayInsert I was attempting to iterate through the RangeRead and insert each figure into the Array.  I understand that the second parameter is wrong but my issue is, no matter what I do I can't output what ReadRange gets, MsgBox doesn't display anything.  Here is updated code which still doesn't work:

$oExcel = _Excel_Open(False)
$Excel = _Excel_BookOpen ( $oExcel, $fFile , False, True )
$Output = _Excel_RangeRead ( $Excel, Default,"B12:V13",1)
_ArrayInsert($Array,0,$Output,0,",")
MsgBox(0,"",$Array[1])
_Excel_BookClose ($Excel)
_Excel_Close($oExcel)

With an error:

"SQL Test.au3" (16) : ==> Subscript used on non-accessible variable.:
MsgBox(0,"",$Array[1])
MsgBox(0,"",$Array^ ERROR

Edited February 10, 2015 by hyperjase

[water]

Sorry, I still don't get it.

_Excel_RangeRead already returns an array. Why do you want to insert the data into another array?

The returned array has two lines and a lot of columns. Do you need to transform the array?

How should the modified array look like?

[hyperjase]

RangeRead does give me the Array but when I try this code:

$Output = _Excel_RangeRead ( $Excel, Default,"B12:V13",1)
MsgBox(0,"",$Output[0])

The MsgBox does not display anything, with the Array, I would have expected to see the first figure from the array?

[hyperjase]

All I need to do is access the Array from RangeRead, then using a while loop to iterate through each one, putting each figure into an SQL INSERT statement.

[water]

In your case _Excel_RangeRead returns a 2D array (because B12:V13 returns 2 rows and multiple columns).

Hence you need to use

MsgBox(0,"",$Output[0][0])

[hyperjase]

Ah!  2D array, never dealt with them and your solution I'd never had attempted - many thanks for your invaluable assistance!

[water]