Jump to content
Sign in to follow this  
uncommon

3D array from excel

Recommended Posts

I have look at the examples in the forms and in the help file however I still dont think I understand what is wrong with my script. I keep getting a "Subscript used with non-Array variable" error. Maybe I am delcaring the array wrong but I am not sure.

I am trying to get the excel sheets to populate into a 3D array.

#include <GUIConstantsEx.au3>
#include <GuiButton.au3>
#include <GuiTreeView.au3>
#include <WindowsConstants.au3>
#include <GuiTab.au3>
#include <array.au3>
#include <Excel.au3>
Global $next = 0
Opt("MustDeclareVars", 0)

;...

$oExcel = _ExcelBookOpen(@WorkingDir & "\Databaseproject.xls", 0)
dim $Databaseproject[10]
dim $array[100][100]
For $sheet = 1 to 10
_ExcelSheetActivate($oExcel, $sheet)
$array = _ExcelReadSheetToArray($oExcel, 1, 1, 100, 100)
$Databaseproject[$sheet] = $array[100][100]
Next
_ArrayDisplay($Databaseproject)
_ExcelBookClose($oExcel)

No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites

You're not using a 3D array for one thing, and for another, you never test to see if the _ExcelReadSheetToArray returns anything, or if it does return anything, whether there are 100 rows and columns. Also, does your spreadsheet really have 10 sheets in it?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

You're not using a 3D array for one thing, and for another, you never test to see if the _ExcelReadSheetToArray returns anything, or if it does return anything, whether there are 100 rows and columns. Also, does your spreadsheet really have 10 sheets in it?

If I am not using a 3D array then I must be declaring it wrong, could you give me an example of what it should look like?

I did test to see if I was getting an array from excel using _ArrayDisplay but I thought was was unnecessary for this post. As far as returning the 100 sheets, that's just over compensating if I need it later. I put ten sheets in there in case I would need it.


No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites

single d array: $array[1]

2d" $array[1][1]

3d: $array[1][1][1]

so you can setup like $array[sheet][x][y]

or, an array of arrays, using the excel read sheet to array function:

$array[sheet] = _ExcelReadSheetToArray() (so, this is a 1d array, comprised of 2d arrays)

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Easier to handle are 2D arrays. 3D arrays can become quite complex to work with.

Do you really need to process multiple sheets in one go? Or could you process sheet by sheet?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Easier to handle are 2D arrays. 3D arrays can become quite complex to work with.

Do you really need to process multiple sheets in one go? Or could you process sheet by sheet?

Maybe you are right Water, I might be over doing this a bit. I must think on this, maybe there is a way I can flatten the data.

Universalist, an Array within array, I did not think that was possible but I will try that function out. Thanks for your comments.


No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites

uncommon,

This is a rough example of what jdelaney has advised...

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

local $myExcelFile = @scriptdir & '\budget.xls'
Local $oExcel = _ExcelBookOpen($myExcelFile,1)

switch @error
    case 1
        ConsoleWrite('Unable to create object' & @LF)
        exit
    case 2
        ConsoleWrite('File does not exist' & @LF)
        Exit
endswitch

local $sheet_list = _excelsheetlist($oExcel)                ; get list of sheets to array, count is at offset 0
local $aSheets[$sheet_list[0]]                              ; define array from sheet list

for $1 = 0 to ubound($aSheets) - 1
    _ExcelSheetActivate($oExcel, $sheet_list[$1])           ; activate each sheet
    $aSheets[$1] = _ExcelReadSheetToArray($oExcel)          ; read each sheet (2D array) to an element of $aSheets (1D array)
next

for $1 = 0 to ubound($aSheets) - 1
    _arraydisplay($asheets[$1])                             ; $aSheets[$1] contains a 2D array
    ConsoleWrite('Number of dimensions for sheet # ' & $1 & ' = ' & ubound($aSheets[$1],0) & @LF)
next

_ExcelBookClose($oExcel, 1)

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By Colduction
      Hi dear friends!, i'm sorry for creating a new thread (a new problem), i have over than 9 lists that i want to combine them to be this (in this example, there are 3 test files):


      I've written a little code for splitting main information, but i really confused how to make results as "Output.txt", here is that code:
       
      $sRegex_1 = StringRegExp(FileRead("1.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_2 = StringRegExp(FileRead("2.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) $sRegex_3 = StringRegExp(FileRead("3.txt"), '(?s:(?<=\=\=\r\n)(.*?)(?=\r\n\=\=))', 3) For $i = 0 To UBound($sRegex_1) - 1 ConsoleWrite($sRegex_1[$i] & @CRLF) For $j = 0 To UBound($sRegex_2) - 1 ConsoleWrite($sRegex_2[$j] & @CRLF) For $k = 0 To UBound($sRegex_3) - 1 ConsoleWrite($sRegex_3[$k] & @CRLF) Next Next Next  
    • By nacerbaaziz
      hello evrybody
      here is an example about how to split your texts using a delimiter with the ability to select how much of delimiters shows in each colum  with $i_number
      e.g
      you have a long text and you want to split it in an array
      that evry colum have a number (n) of lines
      i made a function that do that for you
      just call it with a three params
      $s_text
      your text
      $i_number
      the number that you want to put in each col
      $s_siparator
      the siparator
      default is "|"
      here is the function with example
      i hope that it will be useful for you
       
      ****
       
      #include <Array.au3> $s_txt = "some text1some text2|some text3|some text4|some text5|some text6" $array = splitText($s_txt, 2) _ArrayDisplay($array) Func splitText($s_text, $i_number, $s_siparator = "|") Local $a_TXT = StringSplit($s_text, $s_siparator) Local $a_Return[$a_TXT[0] + 1] If ($a_TXT[0] <= $i_number) Or ($i_number <= 0) Then ReDim $a_Return[2] $a_Return[0] = 1 $a_Return[1] = $s_text Return $a_Return EndIf Local $i_Processed = 1, $i_arrayProcessed = 1 Do For $i = $i_Processed To ($i_Processed + $i_number) - 1 If ($a_TXT[0] < $i) Then ExitLoop If Not ($a_Return[$i_arrayProcessed]) Then $a_Return[$i_arrayProcessed] = $a_TXT[$i] Else $a_Return[$i_arrayProcessed] &= $s_siparator & $a_TXT[$i] EndIf $i_Processed += 1 Next $i_arrayProcessed += 1 Until ($a_TXT[0] < $i_Processed) ReDim $a_Return[$i_arrayProcessed] $a_Return[0] = $i_arrayProcessed - 1 Return $a_Return EndFunc ;==>splitText
      accept my greetings
      thanks to
      @Dan_555
      for his notes
       
    • By MesterPerfect
      good morning
      this is the first post here in the autoit forums
      i hope that you can help me in my problem
      i have a JSON encoded
      it a map of my forums
      where i want to make a treeview that have the same type of map
      e.g
      a system (as category)
      windows (as sub category)
      software (as an child item in the windows category)
      .....
      i don't know how to do that
      so, i know that i can do that using the json functions
      but i need your help about how we can do it as the type that i told you
      by the way i need to put the sub info for each item in an array that give me the ability to manage my items
      e.g
      can post thread
      can reply
      message cound ...
      you just give me a small example and i can continue.
      am sorry if this against the rules of the forum.
      but i realy searched a lot but i couldn't
      i hope some one give me the way.
      thank you very much in advance
       
      here is the link of json forum
      https://www.autoitscript.com/forum/topic/148114-a-non-strict-json-udf-jsmn/
      and here is my encoded json file
       
      { "tree_map": { "0": [ 1, 5, 6, 7 ], "1": [ 2 ], "2": [ 4 ], "5": [ 3 ], "6": [ 8 ], "8": [ 9, 10 ] }, "nodes": [ { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 1, "node_id": 1, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Main category", "type_data": {} }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 2, "node_name": null, "node_type_id": "Forum", "parent_node_id": 1, "title": "Main forum", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 1, "title": "Main category", "node_type_id": "Category" }, { "node_id": 2, "title": "Main forum", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 1, "node_id": 4, "node_name": null, "node_type_id": "Forum", "parent_node_id": 2, "title": "my forums1", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 2, "node_id": 5, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "Perfect", "type_data": {} }, { "breadcrumbs": [ { "node_id": 5, "title": "Perfect", "node_type_id": "Category" } ], "description": "", "display_in_list": true, "display_order": 2, "node_id": 3, "node_name": null, "node_type_id": "Forum", "parent_node_id": 5, "title": "ahmed", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 3, "node_id": 6, "node_name": null, "node_type_id": "Forum", "parent_node_id": 0, "title": "autoit", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 8, "node_name": null, "node_type_id": "Forum", "parent_node_id": 6, "title": "examples", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 3, "node_id": 9, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "GUI", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [ { "node_id": 6, "title": "autoit", "node_type_id": "Forum" }, { "node_id": 8, "title": "examples", "node_type_id": "Forum" } ], "description": "", "display_in_list": true, "display_order": 31, "node_id": 10, "node_name": null, "node_type_id": "Forum", "parent_node_id": 8, "title": "windowEX", "type_data": { "allow_poll": true, "allow_posting": true, "can_create_thread": true, "can_upload_attachment": true, "discussion_count": 0, "last_post_date": 0, "last_post_id": 0, "last_post_username": "", "last_thread_id": 0, "last_thread_prefix_id": 0, "last_thread_title": "", "message_count": 0, "min_tags": 0, "require_prefix": false } }, { "breadcrumbs": [], "description": "", "display_in_list": true, "display_order": 4, "node_id": 7, "node_name": null, "node_type_id": "Category", "parent_node_id": 0, "title": "vbs", "type_data": {} } ] }  
    • By nooneclose
      I need to dynamically resize my 2d array while looping. 
      I know this code:
      ReDim $rArray[UBound($rArray) + 1] works for the rows, however, I also need to increase the columns. How would i go about increasing both rows and columns while looping? 
×
×
  • Create New...