multi dim arrays

[Scorcher]

I need to build an array of known data and use it for callback variables
Array will consist of ex:
System("Hoonah", 00486263, "s91.gif");
System("Juneau", 00264167, "s32.gif");
System("Clipperton", 00679491, "s73.gif");
System("Seattle", 0030402, "s41.gif");
System("Olympia", 00179513, "s21.gif");
System("Salem", 00331473, "s92.gif");
System("Portland", 00419316, "s21.gif");
System("Eugene", 00294127, "s41.gif");
System("Yuma", 00661162, "s31.gif");

i need to be able to call into this array by name "Hoonah" and recive all data back as strings

so call value would be "Hoonah" and return would be "Hoonah", 00486263, "s91.gif"

i am doing extreamly repeatitive tasks and coding will take 1,00's of lines...but i know that an array will drop coding length down to 100's.

Thank you very much.

[Gintaras]

There are two tasks. 1. Build the array. 2. Search the array. Do you already have a way to build the array? If not, what is the data source (eg Excel file), or do you populate the array by assigning each value (eg a[0 0]="Hoonah"; ...)?

[Scorcher]

i have qm version 2.1.6 build 1
i don't think that version support excel. but if it does then that would be great to access an excel doc.
for this...how do i access the excel doc?

otherwise i will populate the array manualy.
what would be best to populate?
how would i build it?


second: For either type you suggest. How do i call the array to return data?

[Gintaras]

Code:

Copy      Help

ARRAY(str) a
ExcelSheetToArray2 a

str s="Hoonah"

int i found
for(i 0 a.len(2)) if(a[0 i]~s) found=1; break
if(found)
,;out a[2 i]
,System(a[0 i] val(a[1 i]) a[2 i])

Function ExcelSheetToArray2:

Code:

Copy      Help

;/
function ARRAY(str)&a

;Stores Excel cells into two-dimensional array.

;EXAMPLE
;ARRAY(str) a
;ExcelSheetToArray a
;int r c
;for r 0 a.len
,;out "-----Row %i-----" r+1
,;for c 0 a.len(1)
,,;out a[c r]


typelib Excel {00020813-0000-0000-C000-000000000046} 1.2

Excel.Application xlApp._getactive; err act; act; xlApp._getactive
Excel.Worksheet ws=xlApp.ActiveSheet
Excel.Range r=ws.UsedRange
int i j nr(r.Rows.Count) nc(r.Columns.Count)
a.create(nc nr)
for i 0 nr
,for j 0 nc
,,a[j i]=r.Item(i+1 j+1)

[jimbog]

Hi Gindi,

I am using ExcelSheetToArray2 to bring in large sets of data. I then manipulate the values in the array. I then sort the array and divide it into smaller arrays. I would like to save these individually as Excel files. I think Ruby has something like this. Not that Ruby is otherwise any good. QM is much better!

Is there (or could you create? ) something like ArrayToExcelSheet? Ideally the function would create a new Excel file of location & name you determine. Alternatively, or maybe in a second function, you could send the array to a sheet in an existing file (ie, perhaps one you had already set up a pivot table or more formulas in).

-jimbog

[Gintaras]

Creating new file is more difficult. Open empty file in Excel and use code like this to populate it.

Code:

Copy      Help

ARRAY(str) a="one[]two"
ExcelSheet es.Init
int i
for i 0 a.len
,es.SetCell(a[i] 1 i+1)

[jimbog]

Great. Thanks for the idea.

The QM array is two dimensions (like a spreadsheet), so I guess I will have to break it into a different array for each column to get it into Excel automatically (unless I'm missing something). Thanks again.

[Gintaras]

Code:

Copy      Help

;create array to be used in the example
ARRAY(str) a.create(3 3)
int r c
for r 0 a.len(1)
,for c 0 a.len(2)
,,a[c r].format("%c%i" 'A'+c r+1)
,,;out a[c r]

;example
ExcelSheet es.Init
for r 0 a.len(1)
,for c 0 a.len(2)
,,es.SetCell(a[c r] c+1 r+1)

[jimbog]

Cool! Thanks Gindi!!