Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg

[Noli Sicad]

The Excel obdc driver is still problematic compare to sqlite3 and Access models.

I read the AMPL googlegroups archive, they too have problems with
Excel ODBC. The written result is different from display on screen,
one user report. Someone is saying he can't write results in Excel
2007 and need to write in Access table.

Just for the record, see the data and solution below. The result of
excel model is erroneous.

Based on experience, CSV table is the still the best way to do
modelling since you can easily use the results in R and Mathplotlib
for graphs.

Noli

~~~~~~~
>C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m 
>"transp_odbc_sqlite3.mod"
GLPSOL: GLPK LP/MIP Solver, v4.42
Parameter(s) specified in the command line:
 --cover --clique --gomory --mir -m transp_odbc_sqlite3.mod
Reading model section from transp_odbc_sqlite3.mod...
Reading data section from transp_odbc_sqlite3.mod...
113 lines were read
Reading plants...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT PLANT, CAPA AS CAPACITY FROM transp_capa
Display statement at line 51
I:
   Seattle
   'San Diego'
Display statement at line 52
a[Seattle] = 350
a['San Diego'] = 600
Reading markets...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT MARKET, DEMAND FROM transp_demand
Reading dist...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT LOC1, LOC2, DIST FROM transp_dist
Generating cost...
Generating supply...
Generating demand...
Display statement at line 97
I:
   Seattle
   'San Diego'
J:
   'New York'
   Chicago
   Topeka
a[Seattle] = 350
a['San Diego'] = 600
b['New York'] = 325
b[Chicago] = 300
b[Topeka] = 275
d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8
d['San Diego','New York'] = 2.5
d['San Diego',Chicago] = 1.8
d['San Diego',Topeka] = 1.4
Model has been successfully generated
GLPK Simplex Optimizer, v4.42
6 rows, 6 columns, 18 non-zeros
Preprocessing...
5 rows, 6 columns, 12 non-zeros
Scaling...
 A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
Problem data seem to be well scaled
Constructing initial basis...
Size of triangular part = 5
      0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
*     4: obj =  1.561500000e+002  infeas = 0.000e+000 (0)
*     5: obj =  1.536750000e+002  infeas = 0.000e+000 (0)
OPTIMAL SOLUTION FOUND
Time used:   0.0 secs
Memory used: 0.1 Mb (133563 bytes)
Writing result...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
DELETE FROM transp_result
INSERT INTO transp_result VALUES (?,?,?)
Seattle    New York          50
Seattle     Chicago         300
Seattle      Topeka           0
San Diego    New York         275
San Diego     Chicago           0
San Diego      Topeka         275
Model has been successfully processed
>Exit code: 0    Time: 0.216

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m 
>"transp_odbc_xls2.mod"
GLPSOL: GLPK LP/MIP Solver, v4.42
Parameter(s) specified in the command line:
 --cover --clique --gomory --mir -m transp_odbc_xls2.mod
Reading model section from transp_odbc_xls2.mod...
Reading data section from transp_odbc_xls2.mod...
103 lines were read
Reading plants...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT PLANT, CAPA AS CAPACITY FROM [transp_capa$]
Reading markets...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT * FROM [transp_demand$]
Reading dist...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT * FROM [transp_dist$]
Generating cost...
Generating supply...
Generating demand...
Display statement at line 85
I:
   'San Diego'
   Seattle
J:
   Chicago
   'New York'
   Topeka
a['San Diego'] = 600
a[Seattle] = 350
b[Chicago] = 300
b['New York'] = 325
b[Topeka] = 275
d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8
d['San Diego',Chicago] = 1.8
d['San Diego','New York'] = 2.5
d['San Diego',Topeka] = 1.4
Model has been successfully generated
GLPK Simplex Optimizer, v4.42
6 rows, 6 columns, 18 non-zeros
Preprocessing...
5 rows, 6 columns, 12 non-zeros
Scaling...
 A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
Problem data seem to be well scaled
Constructing initial basis...
Size of triangular part = 5
      0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
*     4: obj =  1.662750000e+002  infeas = 0.000e+000 (0)
*     7: obj =  1.536750000e+002  infeas = 0.000e+000 (0)
OPTIMAL SOLUTION FOUND
Time used:   0.0 secs
Memory used: 0.1 Mb (141576 bytes)
Writing result...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
INSERT INTO [transp_result$] VALUES (?,?,?)
Writing result...
San Diego     Chicago           0
San Diego    New York         325
San Diego      Topeka         275
Seattle     Chicago         300
Seattle    New York           0
Seattle      Topeka           0
Model has been successfully processed
>Exit code: 0    Time: 0.423