
From:  CHRISTINA CIGNARALE (RIT Student) 
Subject:  [Helpglpk] Excel connection 
Date:  Mon, 17 Jan 2011 15:46:38 0500 
Hi, I am using the current version of GLPK and have been
referencing the sodoku_odbc.mod example for my own project. I recently changed my
setup of reading each set of data from a different excel workbook to one
workbook where each set has a sheet. The sheets are populated from a template
sheet. I wrote some macros to transfer the data from the template to the
appropriate sheet. I now have a problem with the ODBC handler. It will not
accept the *.xlsm extension. Does anyone have any suggestions for fixing this?
I get an error from the first table statement. Thank you for your help. Christina Cignarale Industrial & Systems Engineer Rochester Institute of Technology set P; #set
of all patients set PA within P; #subset
of patients admitted until the day before set PN within P; #subset
of incoming patients requiring admission in the unit set ISOLATION; #set
of isolation needs set GENDER; #set
of genders
set R; #set
of available rooms param filepath symbolic := 'C:\glpk445\examples\RGHPatient';
# textparameter used to represent the path where the table files are stored param B {j in R}; #
number of beds available in each room j in R param G {i in P};# in GENDER ; #
gender of each patient i in P param I {i in P};# in ISOLATION; #
isolation requirement of patient i in P param c {i in P}; #
current relative criticality of patient i in P compared with all other #
patients in the unit param y {i in P, j in R} binary, default 0; #
binary parameter that is 1 if patient i was in room j the day before, and 0
o.w. param flag {i in P} binary; #
binary parameter that is 1 if patient i cannot be moved from its room, and 0
o.w. table
patient IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [patient$]': P
< [P], G, I, c, flag; table pa IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [patest$]': PA
< [PA]; table pn IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [pntest$]': PN
< [PN]; table iso IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [iso$]': ISOLATION
< [iso]; table gender IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [gender$]': GENDER
< [gender]; table rooms IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [rooms$]': R
<[R],B; table yparam IN "ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm' 'SELECT
* FROM [y$]': [P,
R], y; var x {i in P, j in R}
binary; #
binary variable: 1 if patient i is moved to room j, and 0 o.w. var delta {g in GENDER, j in R: B[j] <> 1}
binary; #
binary variable : 1 if there is at least one patient with gender g in room j,
and 0 o.w. var gamma {i in ISOLATION, j in R: B[j] <> 1} binary; #
binary variable: 1 if there is at least one patient with isolation i in room j,
and 0 o.w. var splus{ i in P, j in R} >=0; var sminus{i in P, j in R} >=0; #minimize PatientMoves1: sum {i in PA, j in R} c[i]*(x[i,j]y[i,j])
 sum { i in PN,j in R:B[j] <> 0 and B[j] <> 99 and B[j] <>1
} c[i]*x[i,j]; minimize PatientMoves2: sum {i in P, j in R}
c[i]*(splus[i,j]+sminus[i,j]); subject to const0 {i in P, j in R}:x[i,j]y[i,j] =
splus[i,j]sminus[i,j]; subject to const1{i in P}: sum {j in R} x[i,j] =1; subject to const2 {i in P, j in R: B[j] <> 0 and B[j]
<> 99 and B[j] <>1}: x[i,j] <= delta[G[i],j]; subject to const3 {i in P, j in R: B[j] <> 0 and B[j]
<> 99 and B[j] <>1}: x[i,j] <= gamma[I[i],j]; subject to const4{j in R: B[j] <> 0 and B[j] <>
99 and B[j] <>1}: sum {g in GENDER} delta[g,j]
<=1; subject to const5 {j in R: B[j] <> 0 and B[j] <>
99 and B[j] <>1}: sum {i in ISOLATION} gamma[i,j] <=1; subject to const6 {j in R:B[j] <> 0 and B[j] <>
99 }: sum {i in P} x[i,j] <= B[j];
subject to const7 {i in PA,j in R: B[j]=0}: x[i,j] = 0; subject to const8 {i in PN,j in R: B[j]= 99 }: x[i,j] =0; subject to const9 {i in P, j1 in R: B[j1]=1 and y[i,
j1]=1}:sum {j2 in R: B[j2] = 1 and j1 <>j2} x[i,j2]=0; subject to const10 {i in P}: sum{j in R: B[j] <> 0 and
B[j] <> 99} x[i,j] <= 1  flag[i]; solve; display x, y; table results {i in P, j in R: x[i,j]=1} OUT
"ODBC" 'DRIVER={Microsoft
Excel Driver (*.xlsm)};READONLY=FALSE; dbq=RGHtemplate.xlsm' 'UPDATE
[result$] set P = '''';' 'UPDATE
[result$] set R = '''';' 'UPDATE
[result$] set y = '''';' 'UPDATE
[result$] set x = '''';' 'INSERT
INTO [result$]' '(P,
R, y,x)' 'VALUES(?,?,?,?);': i
~ P, j ~ R, y[i,j]~Y, x[i,j]~X ; #
outputting results to table printf "%16s%16s%16s%16s\n",
"PATIENT","STATUS", "INITIAL_ROOM",
"NEW_ROOM" > "RGHPatient_results.txt"; #
printing results to a text file for {i in PA}{ printf
"%16i%16s", i,
"PA">>"RGHPatient_results.txt"; printf{r
in R: y[i,r]=1} "%16s", r >> filepath
&"RGHPatient_results.txt"; printf{r
in R:x[i,r]=1}"%16s\n", r >> filepath
&"RGHPatient_results.txt"; } for {i in PN}{ printf
"%16i%16s", i, "PN">>"RGHPatient_results.txt"; printf{r
in R: y[i,r]=1} "%16s", r >>filepath
&"RGHPatient_results.txt"; printf{r
in R: x[i,r]=1}"%16s\n", r >> filepath
&"RGHPatient_results.txt"; } end; 
[Prev in Thread]  Current Thread  [Next in Thread] 