[Help-glpk] Problems with Connecting GLPK to Excel

[aly]

Hi All

I appreciate your continuous help.

I asked earlier about connecting GLPK to Excel, and xypron gave me help. I 
already connected it through ODBC. Now, I have 2 questions in the same issue:



1. Suppose I only need to read the values in Excel, while the rest are zeros, 
how can that be done?

In other words, suppose a certain parameter capacity_it has some values for 
some combinations of i and t, and zero for the rest. In the Excel file, only 
those values that are not zeros are mentioned. 

How can we tell MathProg to only read those values and set the rest to zeros??

I know in AMPL, there is something called a "list" that is different from 
tables that can handle this. Is there something similar in MathProg?? And how 
is that used/coded exactly??

Also, another method could be setting all parameters to zero first, then read 
them from Excel so that only non-zero values are overwritten. Is that a correct 
idea?? How can that be done exactly? I mean, what is the code for setting 
values to zeros, and where should I write it in my model file? 


2. While connecting GLPK (MathProg) to Excel, I have a problem. To best 
understand this problem, please see the attached Excel file. You will find an 
original structure for the parameter capacity_ipt, where i is the set of 
suppliers, p is the set of products and t is the set of periods. I'm not able 
to input that parameter in its original structure directly to GLPK. I have to 
modify it to the new structure, found in the same file, first. After modifying 
it to the new structure, I simply use the code:

table cap_it IN "ODBC"
  'DRIVER={Microsoft Excel Driver (*.xls)};dbq=book3.xls'
  'SELECT * FROM [sheet1$]':
 [suppliers,products,periods], cap ~ capacity;


My question is: can I connect directly from the original structure, without 
needing to convert it to the new one?? And how is that done exactly? Also, put 
in mind that the user might add or delete any of the inputs later one (for 
instance add or delete a period).


Your help will be greatly appreciated! Thanks a lot in advance!

Aly

----- Original Message -----
From: "glpk xypron" <address@hidden>
To: "Aly Megahed" <address@hidden>
Sent: Saturday, April 24, 2010 9:57:06 AM GMT -05:00 US/Canada Eastern
Subject: Fwd: Re: [Help-glpk] Connecting GLPK to Excel

-------- Original-Nachricht --------
Datum: Sat, 24 Apr 2010 06:54:55 -0700 (PDT)
Von: xypron <address@hidden>
An: address@hidden
Betreff: Re: [Help-glpk] Connecting GLPK to Excel


Hello Aly,

GLPK can read csv files, see examples/csv/transp_csv.mod and doc/tables.pdf
in GLPK 4.43.
GLPK expect the decimal delimitor to be a point and the field separator to
be a comma.
If Excel does not save in this format you may change your Windows region
settings to
Englisch/USA.

You can use ODBC to connect to Excel.
https://winglpk.svn.sourceforge.net/svnroot/winglpk/trunk/examples/sql/
has an example.
My Windows XP ODBC driver only supported the Excel 97 format.

Best regards

Xypron


Aly Megahed wrote:
> 
> Hi All,
> 
> I hope you are doing well. Your previous help concerning the details of
> connecting GLPK/MathProg to Access worked greatly.
> 
> Now, do you mind writing me (and all users) detailed instructions and an
> example of how to connect it to Excel (either through that csv or dbf, or
> whatever) in order to be able to get data from Excel and send variable
> values back there?
> 
> I use GLPK_4_41.dll and glpsol.exe.
> 
> Your help is greatly appreciated.
> 
> Best,
> 
> Aly
> 

-- 
View this message in context: 
http://old.nabble.com/Connecting-GLPK-to-Excel-tp28349592p28350459.html
Sent from the Gnu - GLPK - Help mailing list archive at Nabble.com.



_______________________________________________
Help-glpk mailing list
address@hidden
http://lists.gnu.org/mailman/listinfo/help-glpk

-- 
GRATIS fur alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

-- 
PhD Student
Vice President, Egyptian Student Association in North America, Georgia Tech 
Chapter

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA


-- 
PhD Student
Vice President, Egyptian Student Association in North America, Georgia Tech 
Chapter

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA

Book3.xls
Description: MS-Excel spreadsheet