RE: Reading data from Excel, but only one column?

[Renger van Nieuwkoop]

Hi Jackson

 

When you want to read one column you have probably a parameter defined over a set. Something like m(year) and then want to fill this parameter with the data from the excel sheet.

So, you should have two columns: one with the set over which you defined the parameter m and one with the values. You then can just read all the values with your command but the range A2:B1014.

Cheers

Renger

 

From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Jackson
Sent: Tuesday, July 03, 2012 2:50 PM
To: gams...@googlegroups.com
Subject: Reading data from Excel, but only one column?

 

Hello everyone!

 

I just started using GAMS today and I am trying to figure out how to input Excel data into GAMS.  I've gone over some of the GDXXRW example files in the User Guide but I can't seem to figure out how to only read one whole column from an Excel sheet.  I keep getting a "bad data range for symbol m" error.

 

Here is my code:

 

Sets

         i SKU /1*1013/

 

* Reading Margin data

$CALL GDXXRW.EXE skudata.xls par=m rng=sheet!B2:B1014 Cdim=0 Rdim=1

 

Parameters m(i) Margin;

$GDXIN skudata.gdx

$LOAD m

$GDXIN

 

 

And this is the error I get:

 

--- call GDXXRW.EXE skudata.xls par=m rng=sheet!B2:B1014 Cdim=0 Rdim=1

 

GDXXRW           Mar 17, 2012 23.8.2 WIN 31442.32372 VS8 x86/MS Windows       

Input file : C:\Users\jachou\Documents\gamsdir\projdir\skudata.xls

Output file: C:\Users\jachou\Documents\gamsdir\projdir\skudata.gdx

**** Bad data range for symbol m : sheet!B2:B1014

Output gdx file deleted

 

Any help or advice would be greatly appreciated.  Thanks!

 

--
You received this message because you are subscribed to the Google Groups "gamsworld" group.
To view this discussion on the web visit https://groups.google.com/d/msg/gamsworld/-/6z9qhs7vJLAJ.
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.

[Renger van Nieuwkoop]

Keep CDIM=0 and start your range just below your column title, then you don’t need the set a.

Renger

 

From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Jackson
Sent: Tuesday, July 03, 2012 4:04 PM
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?

 

Hello Renger,

 

Thanks for the advice.  I actually figured out that I needed a set label column to match each element for the margin parameter.

However, I'm not sure if I'm doing this correctly.  I ended up creating a dummy set:

 

Sets

         i SKU number /1*1013/

         a Column entry /Margin/;

 

* Reading Margin data

$CALL GDXXRW.EXE skudata.xls par=m rng=margin!A1:B1014 Rdim=1 Cdim=1

 

Parameters m(i,a) Margin;

$GDXIN skudata.gdx

$LOAD m

$GDXIN

 

display m;

 

This code works and matches up with the set i.  But is there a way where I can simply have one set?  I don't want to be creating new dummy sets for each column of data.

 

Thanks,

Jackson

To view this discussion on the web visit https://groups.google.com/d/msg/gamsworld/-/GtWpqwMfCusJ.

[Renger van Nieuwkoop]

Just define two parameters and read them with two calls of gdxxrw:

$CALL GDXXRW.EXE skudata.xls par=m rng=margin!A1:B1014 Rdim=1 Cdim=0

 

$CALL GDXXRW.EXE skudata.xls par=n rng=margin!C1:D1014 Rdim=1 Cdim=0

 

Or define a set for the column names and define one parameter with two dimensions ( m(I, columnamesset)) and delete the set column between the two value columns and read the parameter with

$CALL GDXXRW.EXE skudata.xls par=m rng=margin!A1:C1014 Rdim=1 Cdim=1

 

 

Cheers

Renger

 

 

From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Jackson
Sent: Tuesday, July 03, 2012 4:55 PM
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?

 

Hello Renger,

 

I seem to have a different problem now.  How would I go about reading multiple columns with one set column?

 

	Column 1		Column 2
1	9661.98	1	19484.58471
2	61695.89	2	24140.97804
3	1284.61	3	6188.350606
4	20969.93	4	13397.07451
5	14374.08	5	13668.80667

I tried to add another set label column and used the cell ranges C1:D6, but it will only give me an empty parameter set.

 

Here's my code:

 

Sets

         i SKU number /1*1013/;

 

* Reading Margin data

$CALL GDXXRW.EXE skudata.xls par=m rng=margin!A1:B1014 Rdim=1 Cdim=0

 

Parameters m(i) Margin;

$GDXIN skudata.gdx

$LOAD m

$GDXIN

 

display m;

 

* Reading Column 2 data

$CALL GDXXRW.EXE skudata.xls par=c rng=margin!C1:D1014 Rdim=1 Cdim=0

 

Parameters c(i) Column 2;

$GDXIN skudata.gdx

$LOAD c

$GDXIN

 

display c;

 

 

Thanks,

Jackson

To view this discussion on the web visit https://groups.google.com/d/msg/gamsworld/-/YUo-0iReUE8J.

[jyo padmanabhan]

Hi Renger

I read the data from excel using

$ Call gdxxrw.exe i= input.xlsx par=ACROPYL O=ACROPYL,GDX Index=INDEXSH!A4

but I get  output gdx file deleted.

*** Duplicate Identifier =ACROPYL.

Could you please help me out solving this problem.I'm not able to identify th e mistake.Looking  forward for suggestions.Thanks 

Jyotsna

[Renger van Nieuwkoop]

Hi Jyotsna

You have a comma instead of a point in the gdx file name. If this not causing the problem, send me the xls file

Cheers

Renger

sent from my iPad

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/groups/opt_out.

[jyo padmanabhan]

Hi Renger

I'm sending the excel file  for your reference.Thanks 

Jyotsna

[Renger van Nieuwkoop]

Hi Jyotsna

 

You are importing ACROPYL twice, once with the direct command “par=ACROPYL” and then using the command “Index=…”. If you delete “par=ACROPYL” and it works fine.

 

Cheers

 

Renger

[jyo padmanabhan]

Hi Renger

Thanks a lot.

It works.

But  now I  have another query, regarding merging multiple gdx files.  

 I  have multiple gdx files for different data inputs for a particular model.

 Instead of calling the gdx files from GAMS  one by one, I  want to merge all the gdx files and call that merged gdx file only once at the beginning of the model.

 i I  tried usung the "gdxmerge", but only the dimensions were getting loaded, not the data.  Could you please help me out.

Regards

Jyotsna

[Renger van Nieuwkoop]

Hi

For that I would need the code or an example that produces the problem.

[jyo padmanabhan]

Hi  Renger

 Please find attached the files for your reference.

Thanks a lot once again

Jyotsna 

[Renger van Nieuwkoop]

Hi Jyotsna

I merged the gdx files with

$call gdxmerge *.gdx

 

Or

 

$call gdxmerge capitalq.gdx laborq.gdx  cropq.gdx

 

And got all the data (see attached file).

 

Don’t know why this isn’t working for you.

[jyo padmanabhan]

 Hi Renger

 Thanks it works.  The mistake  I  made was  that  I  used the " " while specifying the file names.

 

 

 

Jyotsna

[jyo padmanabhan]

Hi Renger

Now that the merged gdx file  has been created my next doubt /problem is in calling the file into the model. I tried calling it after the declaration of  all sets, parameters and variables just before the equations, still I'm getting error.When do   I  call this merged file,  I  mean after what step. Thanks for your continued help.

 Jyotsna

[Renger van Nieuwkoop]

Send the code and error message, otherwise I have no idea what is going wrong.

Renger

[jyo padmanabhan]

I used the following code:

$call GDXIN merged.gdx

$LOAD PAR1 PAR2 ...................................................................................PAR N

$GDXIN.

On giving the above commands  I got not one error but multiple errors. 

Thanks once again

[Renger van Nieuwkoop]

This works fine for me. (You better have a closer look at the manual and if you produce errors, tell us what they are…Not, “I have errors”.)

 

$call gdxmerge capitalq.gdx laborq.gdx  cropq.gdx

parameter acropyl

$gdxin merged.gdx

$LOAD acropyl

 

display acropyl;

[jyo padmanabhan]

 Hi  Renger

  Ok  I shall read through the manual carefully. I apologize for the previous mail which was incomplete..........

  Thanks a lot  for clarifying my doubts.

Jyotsna  

  

On Monday, December 16, 2013 9:18:53 PM UTC+5:30, Renger van Nieuwkoop wrote:i 

[miner]

Hi, Renger,

I also need to create multiple sets of parameters. So I use two calls of gdxxrw. However, the result only give me the last one. Here is my program:

set s /s1*s100/;

parameter 

                Ta(s) ambient temperature

                 thetaA(s) wind angle;

$CALL GDXXRW.EXE rawdata.xlsx par=Ta rng=AT!A2:B100 Rdim=1 Cdim=0

$CALL GDXXRW.EXE rawdata.xlsx par=thetaA rng=thetaA!A2:B100 Rdim=1 Cdim=0

$GDXIN rawdata.gdx

$LOAD Ta thetaA

$GDXIN

display Ta, thetaA;

BTW, your post is very helpful. I tried several days to find out the solution on GAMS website, but failed. So I tried to find the solution by googleing and find your posts immediately. Hope GAMS will compile your replies in their website.

<p clas

...

[Renger van Nieuwkoop]

Hi Miner

You can’t read this using the command twice. What happens is that gdxxrw overwrites the rawdata.gdx.

You have to this in one line

 

$CALL GDXXRW.EXE rawdata.xlsx par=Ta rng=AT!A2:B100 Rdim=1 Cdim=0 par=thetaA rng=thetaA!A2:B100 Rdim=1 Cdim=0

 

Or use a text file with the instructions line by line or put the instructions for gdxxrw in the excel file (I usually do that). See the manual for some examples.

Cheers

Renger

--

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.

For more options, visit https://groups.google.com/d/optout.

[Xiao Yuan]

hi, Renger,

Thank you very much for your quick reply. The problem is solved now. Actually I do use your way, but I put a comma "," before the second 'par='. (thought it will like other commands)

What is the manual you mentioned? I was reading gdxutils (http://www.gams.com/dd/docs/tools/gdxutils.pdf). But did not find the same example. I know I can use two dimensional way. But I do not like this way. Hope the new gdxutils will includes this example in the future.

Thanks a lot.

Miner

--
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/3iB2ciTd1Lo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to gamsworld+...@googlegroups.com.