GAMS World Forum

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



--

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.

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.