Reading data from Excel, but only one column?

Archive of Gamsworld Google Group
Post Reply
Archiver
User
User
Posts: 7876
Joined: 7 years ago

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

Post by Archiver »


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;


Cheers

Renger



From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Montag, 16. Dezember 2013 16:30
To: gamsworld@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



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

On Monday, December 16, 2013 4:12:46 PM UTC+5:30, Renger van Nieuwkoop wrote:

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

Renger

Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von jyo padmanabhan
Gesendet: Montag, 16. Dezember 2013 11:41
An: gams...@googlegroups.com
Betreff: Re: Reading data from Excel, but only one column?



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

On Monday, December 16, 2013 2:38:58 PM UTC+5:30, jyo padmanabhan wrote:

Hi Renger

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







Jyotsna
On Friday, December 13, 2013 2:26:35 PM UTC+5:30, Renger van Nieuwkoop wrote:

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.



Cheers

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Freitag, 13. Dezember 2013 05:59
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



Hi Renger

Please find attached the files for your reference.

Thanks a lot once again







Jyotsna

On Thursday, December 12, 2013 5:16:25 PM UTC+5:30, Renger van Nieuwkoop wrote:

Hi

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

Cheers

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Donnerstag, 12. Dezember 2013 09:13
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



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





On Monday, December 9, 2013 2:45:49 PM UTC+5:30, Renger van Nieuwkoop wrote:

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



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Sonntag, 8. Dezember 2013 14:41
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



Hi Renger

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







Jyotsna

On Sunday, December 8, 2013 12:13:19 AM UTC+5:30, Renger van Nieuwkoop wrote:

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


Am 07.12.2013 um 19:14 schrieb "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





On Tuesday, July 3, 2012 8:50:03 PM UTC+5:30, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 10:08:09 AM UTC-4, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 9:37:53 AM UTC-4, Renger van Nieuwkoop wrote:

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!



--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... 9qhs7vJLAJ.
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.

--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... WpqwMfCusJ.
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.

--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... o-0iReUE8J.
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.

--
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.

--
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.

--
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.

--
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.

--
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.

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

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

Post by Archiver »


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

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;


Cheers

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Montag, 16. Dezember 2013 16:30
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



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

On Monday, December 16, 2013 4:12:46 PM UTC+5:30, Renger van Nieuwkoop wrote:

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

Renger

Von: gams...@googlegroups.com [mailto:gams...@googlegroups.com] Im Auftrag von jyo padmanabhan
Gesendet: Montag, 16. Dezember 2013 11:41
An: gams...@googlegroups.com
Betreff: Re: Reading data from Excel, but only one column?



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

On Monday, December 16, 2013 2:38:58 PM UTC+5:30, jyo padmanabhan wrote:

Hi Renger

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







Jyotsna
On Friday, December 13, 2013 2:26:35 PM UTC+5:30, Renger van Nieuwkoop wrote:

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.



Cheers

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Freitag, 13. Dezember 2013 05:59
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



Hi Renger

Please find attached the files for your reference.

Thanks a lot once again







Jyotsna

On Thursday, December 12, 2013 5:16:25 PM UTC+5:30, Renger van Nieuwkoop wrote:

Hi

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

Cheers

Renger



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Donnerstag, 12. Dezember 2013 09:13
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



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





On Monday, December 9, 2013 2:45:49 PM UTC+5:30, Renger van Nieuwkoop wrote:

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



From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of jyo padmanabhan
Sent: Sonntag, 8. Dezember 2013 14:41
To: gams...@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



Hi Renger

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







Jyotsna

On Sunday, December 8, 2013 12:13:19 AM UTC+5:30, Renger van Nieuwkoop wrote:

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


Am 07.12.2013 um 19:14 schrieb "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





On Tuesday, July 3, 2012 8:50:03 PM UTC+5:30, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 10:08:09 AM UTC-4, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 9:37:53 AM UTC-4, Renger van Nieuwkoop wrote:

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!



--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... 9qhs7vJLAJ.
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.

--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... WpqwMfCusJ.
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.

--
To view this discussion on the web visit https://groups.google.com/d/msg/gamswor ... o-0iReUE8J.
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.

--
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.

--
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.

--
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.

--
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.

--
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.

--
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.

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

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

Post by Archiver »


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.

On Tuesday, July 3, 2012 10:20:03 AM UTC-5, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 10:08:09 AM UTC-4, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 9:37:53 AM UTC-4, Renger van Nieuwkoop wrote:

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
<p clas
...

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

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

Post by Archiver »


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



From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of miner
Sent: Dienstag, 18. März 2014 15:42
To: gamsworld@googlegroups.com
Subject: Re: Reading data from Excel, but only one column?



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.


On Tuesday, July 3, 2012 10:20:03 AM UTC-5, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 10:08:09 AM UTC-4, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 9:37:53 AM UTC-4, Renger van Nieuwkoop wrote:

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

<p clas

...

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

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

Post by Archiver »


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


On Tue, Mar 18, 2014 at 10:01 AM, Renger van Nieuwkoop wrote:

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



From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of miner
Sent: Dienstag, 18. März 2014 15:42
To: gamsworld@googlegroups.com

Subject: Re: Reading data from Excel, but only one column?



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.


On Tuesday, July 3, 2012 10:20:03 AM UTC-5, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 10:08:09 AM UTC-4, Renger van Nieuwkoop wrote:

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


On Tuesday, July 3, 2012 9:37:53 AM UTC-4, Renger van Nieuwkoop wrote:

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

<p clas

...

--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
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/gamsw ... nsubscribe.
To unsubscribe from this group and all its topics, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.


--
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Post Reply

Return to “Archive Google Group”