import data from Excel to GAMS

[D.Dileep]

Hi all,

Plz, can any one tell me how to import data from Excel to GAMS?

[Paul Van der Eijk]

Look for GDXXRW in gdxutils.pdf and mccarlgamsusersguide.pdf in the docs directory.

--Paul

On Tue, Apr 10, 2012 at 10:23 AM, D.Dileep <dilipda...@gmail.com> wrote:

Hi all,

Plz, can any one tell me how to import data from Excel to GAMS?

--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To post to this group, send email to gams...@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.

--
Paul van der Eijk
GAMS Development Corporation
Tel  : (202) 342-0180  Fax  : (202) 342-0181
Email: pvand...@gams.com  
Web : http://www.gams.com

[A.R. Bahari]

Hi
Download the latest version of gams from www.gams.com
First locate the *.xls* file in the following path:
E:\Data\MatrixInputs.XLS
Now its ready to run.
Have a look at the sheet named "index" and  at the 17 first rows of my cod To know how importing data fromexcel is performed.
the 5 last rows export data to Excel
Close the excel file  when GAMS is runing. 

On Tue, Apr 10, 2012 at 5:53 PM, D.Dileep <dilipda...@gmail.com> wrote:

Hi all,

Plz, can any one tell me how to import data from Excel to GAMS?

[Krishna Prasad]

Hi,

Let us suppose example.xls file you want to read and it has row name as x1,x2,x3 ,...x10 and column name as y1,y2,...y20 and let us suppose you want to save it in a parameter of name xydata then you can read in gams as follow:

Set i  /x1*x10/;

Set j  /y1*y20/;

PARAMETER xydata(i,j)

$CALL GDXXRW example.xls par=xydata rng=sheet_name!A1:U10 Cdim=1 Rdim=1

$GDXIN example.gdx

$LOAD xydata

$GDXIN

Display xydata;

You can see the attached file. one thing is that you must save the .xls in 2003-2007 format 

 

Best Regards

Krishna Prasad

[Dileep Damayyawar]

Thank you very much Paul and Bahari...

--
Regards
Dileep Damayyawar

        

[Dileep Damayyawar]

Thanks Krishna..

Regards
Dileep Damayyawar

        

[Dileep Damayyawar]

Hi friends,

I want some more help from you. I have data in 15 excel sheets and these sheets are in different workbooks. Then,  how can I import this data in my GAMS file?

--
Regards
Dileep Damayyawar

[AC]

Hi Dileep

To read from more than one file then you need to repeat the gdx read
commands. For example imagine you have two workbooks xyz.xls and
abc.xls:

** Read the data from xyz.xls
PARAMETER xydata(i,j)
$CALL GDXXRW xyz.xls par=xydata rng=sheet_name!A1:U10 Cdim=1 Rdim=1
$GDXIN xyz.gdx

$LOAD xydata
$GDXIN
Display xydata;

** Read the data from abc.xls
PARAMETER abcdata(i,j)
$CALL GDXXRW abc.xls par=abcdata rng=sheet_name!A1:U10 Cdim=1 Rdim=1
$GDXIN abc.gdx
$LOAD abcdata
$GDXIN
Display abcdata;


It can also be a good idea to use a text file to list all the sets and
parameters you want to read in from each file. This stops your list
of set=/par= and rng= getting very long if you are reading a lot of
different sets and parameters from the same workbook. I suggest
looking at Example 6 in the gdxutils.pdf that came with your GAMS
installation to see how that works.

HTH
AndyC


On Apr 11, 11:10 pm, Dileep Damayyawar <dilipdamayya...@gmail.com>
wrote:

> Hi friends,
>
> I want some more help from you. I have data in 15 excel sheets and these
> sheets are in different workbooks. Then,  how can I import this data in my
> GAMS file?
>
> --
> Regards
> Dileep Damayyawar
>

> On 10 April 2012 23:44, Dileep Damayyawar <dilipdamayya...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Thanks Krishna..
>

> > On 10 April 2012 10:22, Krishna Prasad <kprasad.i...@gmail.com> wrote:
>
> >> Hi,
>
> >> Let us suppose example.xls file you want to read and it has row name as
> >> x1,x2,x3 ,...x10 and column name as y1,y2,...y20 and let us suppose you
> >> want to save it in a parameter of name xydata then you can read in gams as
> >> follow:
>
> >> Set i  /x1*x10/;
> >> Set j  /y1*y20/;
>
> >> PARAMETER xydata(i,j)
> >> $CALL GDXXRW example.xls par=xydata rng=sheet_name!A1:U10 Cdim=1 Rdim=1
> >> $GDXIN example.gdx
> >> $LOAD xydata
> >> $GDXIN
> >> Display xydata;
>
> >> You can see the attached file. one thing is that you must save the .xls
> >> in 2003-2007 format
>
> >> Best Regards
> >>  Krishna Prasad
>

> >> On Tue, Apr 10, 2012 at 9:39 PM, A.R. Bahari <7arbah...@gmail.com> wrote:
>
> >>> Hi
> >>> Download the latest version of gams fromwww.gams.com
> >>> First locate the *.xls* file in the following path:
> >>> E:\Data\MatrixInputs.XLS
> >>> Now its ready to run.
> >>> Have a look at the sheet named "index" and  at the 17 first rows of my
> >>> cod To know how importing data fromexcel is performed.
> >>> the 5 last rows export data to Excel
> >>> Close the excel file  when GAMS is runing.
>

[Harish Singh]

[Renger van Nieuwkoop]

Hi

What about the gdxutils.pdf manual (p. 13/14)… or searching the mailing lists…

 

RDIM is the number of indices you have in your columns

e.g.

 

Start      End Value

A             B       10

…

 

This would be RDIM = 2, CDIM = 1

Gams now knows when it reads the parameter results(Start,End,*), that the table in Excel is formatted with in the first two columns the first two sets (Start, End) and in the row the third index.

 

Renger

 

Dim = integer

The total dimension for the symbol

Cdim = Integer

Column dimension: the number of rows in the data range that will be used to define the labels for

the columns. The first Cdim rows of the data range will be used for labels.

 

Rdim = Integer

Row dimension: the number of columns in the data range that will be used to define the labels for

the rows. The first Rdim columns of the data range will be used for the labels.

 

Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von Pablo Sepúlveda Gutiérrez
Gesendet: Dienstag, 2. April 2013 17:03
An: gams...@googlegroups.com
Betreff: Re: import data from Excel to GAMS

 

Hi Alireza Bahari !!!!

 

Thanks for your help, but i have a question. ¿what's the meaning of "DIM" and "RDIM" values of your *.xls* file? I was trying to compile the file without that values, but the model gave me some errors.

Thank for all.

 

Cheers!

El martes, 10 de abril de 2012 13:09:03 UTC-3, Alireza Bahari escribió:

Hi
Download the latest version of gams from www.gams.com
First locate the *.xls* file in the following path:
E:\Data\MatrixInputs.XLS
Now its ready to run.
Have a look at the sheet named "index" and  at the 17 first rows of my cod To know how importing data fromexcel is performed.
the 5 last rows export data to Excel
Close the excel file  when GAMS is runing. 

--

You received this message because you are subscribed to the Google Groups "gamsworld" group.

To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.

To post to this group, send email to

gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ines TR]

Krish,
I am trying to import data as you suggest:

set i/product1*product10/;
set j/supplier1*supplier10/;
set r/dsct1*dsct5/;

Parameter
table b(j,r)
$call GDXXRW InputGAMS.xls rng=b!B Cdim=5 Rdim=10
$GDXIN InputGAMS.gdx
$LOAD b
$GDXIN
Display b
;
table k(j,r)
$call GDXXRW InputGAMS.xls rng=k!K Cdim=5 Rdim=10
$GDXIN InputGAMS.gdx
$LOAD k
$GDXIN
Display k
;
table p(i,j)
$call GDXXRW InputGAMS.xls rng=price!P Cdim=10 Rdim=10
$GDXIN InputGAMS.gdx
$LOAD P
$GDXIN
Display P
;

But I am getting the following errors:
- No such file or directory
- Unable to open gdx file for $GDXIN

Any idea of how I can fix this?
Thank you!
Ines

[Pedro Javier Ramírez Torrealba]

Dear Ines,

1) Check the extension of your excel file. Is it xls or xlsx?.

2) The range of the input data is not correctly specified. Check: http://gams.com/mccarl/mccarlhtml/rng_equal_symbol.htm

3) The dimensions of the input data are not correct. Check: http://gams.com/mccarl/mccarlhtml/gams_item_dimension_dim_rdim_cdim.htm

For table b(j,r), the excel reading code must look like this:

$CALL GDXXRW I=InputGAMS.xlsx O=InputGAMS.gdx rng=Sheet1!B1 Cdim=1 Rdim=1

$GDXIN InputGAMS
$LOAD b
$GDXIN

Cheers,

Pedro

To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.

To post to this group, send email to gams...@googlegroups.com.

Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
PEDRO JAVIER RAMÍREZ TORREALBA
Ingeniero Civil Eléctrico PUC
MSc en Ingeniería Eléctrica PUC
Londres, REINO UNIDO

Celular: +44-(0)75-8069-3119

[inest]

Thanks Pedro!,

I am amazed how blind one can be come when under pressure.

Ines

--
You received this message because you are subscribed to a topic in the Google Groups "gamsworld" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/gamsworld/ujx7tWY6X94/unsubscribe.
To unsubscribe from this group and all its topics, send an email to gamsworld+...@googlegroups.com.

To post to this group, send email to gams...@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--

Aineth Torres

Profesora de Cátedra en el área de Cadena de Suministro

Depto. de Ingeniería Industrial

ITESM-Puebla /

PhD candidate in Industrial Engineering 

Penn State University