Re: [Help-glpk] [Fwd: Read Excel data into subscripted set]

[Andrew Mason]

Christian,

If you use SolverStudio to run your GMPL model directly from Excel, then 
SolverStudio will create the data file automatically for you. 
SolverStudio knows how to pass a two dimensional table to GMPL, and 
handles all the data transfers for you so you won't need any of the 
database connection code.

Cheers,

Andrew
http://solverstudio.org


On 27/10/2012 6:24 a.m., Xypron wrote:
> Hello Christian,
>
> your mail had to be forwarded manually because you are not subscribed to
> the GLPK help list.
> See https://lists.gnu.org/mailman/listinfo/help-glpk
>
> Two dimensional tables cannot be read by GMPL. Organize your data in
> columns:
> NUTR, FOOD, amt
> Vitamin C, Hamburger, 0
> Water, Wine, 0.93
>
> glpk-4.47/doc/gmpl.pdf provides the syntax. GLPK for Windows has example
> files in glpk-4.47/examples/sql.
>
> Best regards
>
> Xypron
>
> On 26.10.2012 15:27, Andrew Makhorin wrote:
> > -------- Forwarded Message --------
> > Subject: Read Excel data into subscripted set
> > Date: Fri, 26 Oct 2012 14:46:44 +0200
> >
> > Hi,
> >
> > I have troubles with reading a 2-dimensional table in MS Excel and
> > putting the data into a subscripted set. In a first step, I try to read
> > a 2-dimensional table and fill its data into a 2-dimensional parameter.
> > The example from the AMPL book pp.196-197 (can be found online:
> > http://www.ampl.com/BOOK/CHAPTERS/13-tables.pdf) describes exactly this
> > situation. It uses the diet problem to demonstrate, how the parameter
> > amt (param amt {NUTR,FOOD} >= 0) can be read from MS Excel by
> > table dietAmts IN "ODBX" DIET2D.xls":
> > [i ~ NUTR], {j in FOOD} <amt[i,j] ~ (j)>;
> >
> >
> >
> > Since the database connection in GMPL looks a little bit different, I
> > adopted my working command for reading in the data of a 1-dimensional MS
> > Excel table to the 2-dimensional case like that:
> > table dietAmts IN 'ODBC'
> >    'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
> > *.xlsb)};dbq=.\test.xlsx'
> >    'SELECT * FROM [Tabelle2$]' :
> >    [i ~ NUTR], {j in FOOD} <amt[i,j] ~ (j)>;
> >
> > Putting everything together in one model file (why the table definition
> > needs to be before the data part, I still do not understand..), Gusek
> > gives me an error message:
> > syntax error in field list
> > Context:  , FOOD } >= 0 ; table dietAmts IN '...' '...' '...' : [ i ~
> > MathProg model processing error
> >
> > I would appreciate every help!
> >
> > Regards,
> > Christian
> >
> >
> >
> > _______________________________________________
> > Help-glpk mailing list
> > address@hidden
> > https://lists.gnu.org/mailman/listinfo/help-glpk
>
> _______________________________________________
> Help-glpk mailing list
> address@hidden
> https://lists.gnu.org/mailman/listinfo/help-glpk

--

Dr Andrew J Mason
Dept of Engineering Science
University of Auckland
New Zealand
+64 9 3737599 x87909
www.esc.auckland.ac.nz/Mason
address@hidden

www.OpenSolver.org: Open-source Solver-compatible LP/IP optimizer for Excel
www.SolverStudio.org: Excel modelling using AMPL, GAMS, PuLP, GMPL & Gurobi