hyperjase

_Excel_RangeRead to Array?

16 posts in this topic

#1 ·  Posted (edited)

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 by hyperjase

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by MikahS

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by hyperjase

Share this post


Link to post
Share on other sites

Can you post what you've tried?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by hyperjase

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

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 by MikahS

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by hyperjase

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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])
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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