Solved

Multidimensional data input from excel

  • 2 November 2019
  • 4 replies
  • 385 views

Hi there,

 

I have a 3-dimension binary variable as my parameter, which indicating what combination of items are in a paticular batch of order as below (the example shows I have item 1,2,0 in my Bathch-1).

 

 

My question is that, if I have 1000 batches, is it possible to directly input this data from excel? I know the answer is probably yes with the Spreadsheet:: functions, but I’m a little bit confused how should I arrange my data in excel? Should I include the data of one batch in one spreadsheet and read the entire excel file use Spreadsheet::RetrieveTable? If this is the way, what should I do when I have a variable of 4 dimensions?

 

And another (dummy) question-if I need to use the code to retrive data from excel, which section should I put this code in? MainExecution?

 

Thanks in advance!

icon

Best answer by mohansx 3 November 2019, 06:56

View original

4 replies

Userlevel 5
Badge +2

@lin86 , you’ll be better of using the AXLL library than the spreadsheet functions. 

See instructions here, https://how-to.aimms.com/Articles/122/122-AXLL-Library.html

You can use either axll::ReadList or axll::ReadTable depending on how you have the data in your Excel sheet. 

Is it in a matrix form like the pic you attached ? Then use axll::ReadTable. 

Is data like a csv file (a composite table) ? Then use axll::ReadList

 

And another (dummy) question-if I need to use the code to retrive data from excel, which section should I put this code in? MainExecution?

 

You can put it in any procedure. A section is a chapter in your AIMMS project. A procedure is a script that you can execute. MainExecution is just a special procedure which is linked to F6 as a keyboard shortcut. You can have any number of procedures in your project.

 

Hi @mohansx! Thanks for your reply!  Currently, I’m testing the model with a small number of batches (say only 3 batches),  so I just manually input all batch information without any data file input. The picture I attached is just the data for one batch, meaning if I have 3 batches, I’ll have 3 these matrices in total. That’s why I’m confused about how should I arrange the data structure since there’re more than two dimensions and I assume one spreadsheet can only include two dimensions? 

Userlevel 5
Badge +2

 @lin86 , you can have multidimensional data in a spreadsheet too. Like a database table or in a matrix tabular form. See attached example file. 

@mohansx Thank you so much for the helpful example!

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator