Reading data from an Excel table without modifying its structure before
Reading data from an Excel table without modifying its structure before
Dear GAMS folks,
a quick question regarding reading data into GAMS.
To get things really clean, I would like to take a dataset that I get from somewhere else in Excel format without touching it before reading it into GAMS.
The data structure is straight forward:
Say, what I would like to automatically read-in from that table is the following things:
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
I can easily read in price and size as follows, but I have problems with the typemap(product, productType) mapping set.
So, I would go ahead and define an "Index" sheet in my Excel file that looks like this:
And would then read that in like this:
********************************************************************************
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
* Additional parameters for data upload (not needed in model)
Set attribute;
Parameter productData(product,attribute);
********************************************************************************
$onUNDF
$call gdxxrw "test.xlsx" o=data.gdx Index=Index!A1
$GDXIN data.gdx
$load attribute product productType
$load productData
$gdxin
$offUNDF
price(product) = productData(product,"price");
size(product) = productData(product,"size");
Execute_unload 'mydata.gdx';
********************************************************************************
This way, price and size are correctly filled. Now: How would I go about also reading in typemap(product, productType) automatically (without modifying the original input data), so it has e.g.
typemap("prod1", "fruit") = YES;
?
Thanks in advance for any helpful responses!
Cheers
Ingmar
--
- Attachments
-
[The extension has been deactivated and can no longer be displayed.]
-
[The extension has been deactivated and can no longer be displayed.]
Re: Reading data from an Excel table without modifying its structure before
Ingmar,
The lastest version of gdxxrw has the option IgnoreColumns. So in order to read the typemap you can read "set=typemap rng=A2 rdim=2 cdim=0 ignorecolumns=B:D"
Another way that is even more flexible to read such structured Excel sheets is to use the ODBC driver to Excel and read via sql2gms into GDX (http://gams.com/help/topic/gams.doc/too ... OM_MSEXCEL).
Hope this helps,
Michael
On Tuesday, April 12, 2016 at 9:05:35 AM UTC-4, Ingmar Schlecht wrote:
Dear GAMS folks,
a quick question regarding reading data into GAMS.
To get things really clean, I would like to take a dataset that I get from somewhere else in Excel format without touching it before reading it into GAMS.
The data structure is straight forward:
Say, what I would like to automatically read-in from that table is the following things:
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
I can easily read in price and size as follows, but I have problems with the typemap(product, productType) mapping set.
So, I would go ahead and define an "Index" sheet in my Excel file that looks like this:
And would then read that in like this:
********************************************************************************
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
* Additional parameters for data upload (not needed in model)
Set attribute;
Parameter productData(product,attribute);
********************************************************************************
$onUNDF
$call gdxxrw "test.xlsx" o=data.gdx Index=Index!A1
$GDXIN data.gdx
$load attribute product productType
$load productData
$gdxin
$offUNDF
price(product) = productData(product,"price");
size(product) = productData(product,"size");
Execute_unload 'mydata.gdx';
********************************************************************************
This way, price and size are correctly filled. Now: How would I go about also reading in typemap(product, productType) automatically (without modifying the original input data), so it has e.g.
typemap("prod1", "fruit") = YES;
?
Thanks in advance for any helpful responses!
Cheers
Ingmar
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Re: Reading data from an Excel table without modifying its structure before
Thanks Michael, that looks promising.
Best,
Ingmar
On Tuesday, April 12, 2016 at 3:30:13 PM UTC+2, Michael Bussieck wrote:
Ingmar,
The lastest version of gdxxrw has the option IgnoreColumns. So in order to read the typemap you can read "set=typemap rng=A2 rdim=2 cdim=0 ignorecolumns=B:D"
Another way that is even more flexible to read such structured Excel sheets is to use the ODBC driver to Excel and read via sql2gms into GDX (http://gams.com/help/topic/gams.doc/too ... OM_MSEXCEL).
Hope this helps,
Michael
On Tuesday, April 12, 2016 at 9:05:35 AM UTC-4, Ingmar Schlecht wrote:
Dear GAMS folks,
a quick question regarding reading data into GAMS.
To get things really clean, I would like to take a dataset that I get from somewhere else in Excel format without touching it before reading it into GAMS.
The data structure is straight forward:
Say, what I would like to automatically read-in from that table is the following things:
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
I can easily read in price and size as follows, but I have problems with the typemap(product, productType) mapping set.
So, I would go ahead and define an "Index" sheet in my Excel file that looks like this:
And would then read that in like this:
********************************************************************************
Sets
product
productType
typemap(product, productType)
;
Parameters
price(product)
size(product)
;
* Additional parameters for data upload (not needed in model)
Set attribute;
Parameter productData(product,attribute);
********************************************************************************
$onUNDF
$call gdxxrw "test.xlsx" o=data.gdx Index=Index!A1
$GDXIN data.gdx
$load attribute product productType
$load productData
$gdxin
$offUNDF
price(product) = productData(product,"price");
size(product) = productData(product,"size");
Execute_unload 'mydata.gdx';
********************************************************************************
This way, price and size are correctly filled. Now: How would I go about also reading in typemap(product, productType) automatically (without modifying the original input data), so it has e.g.
typemap("prod1", "fruit") = YES;
?
Thanks in advance for any helpful responses!
Cheers
Ingmar
--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.