Import multiple excel sheets

GAMS
1

Hello,

I am trying to import multiple sheets of data from an excel sheet. I want my data to go into a single gdx file under different parameter names for each sheet.

So far, I have tried two methods.

If I use $CALL GDXXRW.EXE multiple times and create the same gdx file each time, the last gdx file overwrites the ones before it.

$CALL GDXXRW.EXE CoalHeat2004.xlsx par=total rng=Total!a1:o165 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=internal rng=IEA_Internal!a1:o127 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=external rng=IEA_ThirdParty!a1:o127 Rdim=1 Cdim=1


The above lines yield CoalHeat2004.gdx with only the parameter “external”. I want “total” and “internal” to also be in the gdx file.

I also tried creating separate gdx files and merging them into a single file, but this method results in a single merged set of data without separate parameter names. I can’t figure out how to use execute_unload or whatever other command to get my parameters onto the gdx file.

Thanks for your help.

Kirby

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

2

Hi Kibry

I have the same problem but couldn’t find any solution yet. Have you found the solution? I wonder if you suggest a solution.
Best
Saeed

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

3

Hi Kibry



If you use the unload command, you can save all parameters in the same gdx file. After that you call the gdx command to write the stuff to your excel sheets:



parameter total, check;



total = 1;

check = 3;

execute_unload “results.gdx”, total,check;



$call gdxxrw.exe i=results.gdx o=results.xlsx par=total rng=Total!a1

$call gdxxrw.exe i=results.gdx o=results.xlsx par=check rng=Total!b1


\

  • Or using a put file



    $onecho > taskout.txt

par = total rng=Total!a1

par =check rng=Total!b1

$offecho



execute ‘gdxxrw.exe o=results.xlsx i=results.gdx @taskout.txt’



Hope this helps



Cheers



Renger





From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Saeed Sayyadi
Sent: Dienstag, 14. Oktober 2014 16:40
To: gamsworld@googlegroups.com
Subject: Re: Import multiple excel sheets



Hi Kibry



I have the same problem but couldn’t find any solution yet. Have you found the solution? I wonder if you suggest a solution.

Best

Saeed


\

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.

4

Hi Coders,


I am trying to load my data from Excel. However, I get Error 66 (The Symbol shown has not been defined …).
I am positive that the code loads data perfectly, but it stuck somewhere.
Is there any chance that GAMS would not be able to load large Excel files (My code/model is a simple logistic model, but the size is very large)?
Besides, I am guessing the code might have some difficulty to manage the alias (T,TT) .

Any help from your side would be appreciated.

Here is my excel/code https://drive.google.com/open?id=0B_JUYNC5gg5zfjdBeXVhcHdzRDVXSHBzdXphN3A1U2gtak1sMzRydkJxNmlxY2thaHY0NTA&authuser=0


Thanks,
Arash

On Friday, June 27, 2014 at 12:02:31 PM UTC-4, Kirby Ledvina wrote:

Hello,

I am trying to import multiple sheets of data from an excel sheet. I want my data to go into a single gdx file under different parameter names for each sheet.

So far, I have tried two methods.

If I use $CALL GDXXRW.EXE multiple times and create the same gdx file each time, the last gdx file overwrites the ones before it.

$CALL GDXXRW.EXE CoalHeat2004.xlsx par=total rng=Total!a1:o165 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=internal rng=IEA_Internal!a1:o127 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=external rng=IEA_ThirdParty!a1:o127 Rdim=1 Cdim=1


The above lines yield CoalHeat2004.gdx with only the parameter “external”. I want “total” and “internal” to also be in the gdx file.

I also tried creating separate gdx files and merging them into a single file, but this method results in a single merged set of data without separate parameter names. I can’t figure out how to use execute_unload or whatever other command to get my parameters onto the gdx file.

Thanks for your help.

Kirby

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

5

Hi Coders,


I am trying to load my data from Excel. However, I get Error 66 (The Symbol shown has not been defined …).
I am positive that the code loads data perfectly, but it stuck somewhere.
Is there any chance that GAMS would not be able to load large Excel files (My code/model is a simple logistic model, but the size is very large)?
Besides, I am guessing the code might have some difficulty to manage the alias (T,TT) .

Any help from your side would be appreciated.

Here is my excel/code https://drive.google.com/open?id=0B_JUYNC5gg5zfjdBeXVhcHdzRDVXSHBzdXphN3A1U2gtak1sMzRydkJxNmlxY2thaHY0NTA&authuser=0


Thanks,
Arash

On Friday, June 27, 2014 at 12:02:31 PM UTC-4, Kirby Ledvina wrote:

Hello,

I am trying to import multiple sheets of data from an excel sheet. I want my data to go into a single gdx file under different parameter names for each sheet.

So far, I have tried two methods.

If I use $CALL GDXXRW.EXE multiple times and create the same gdx file each time, the last gdx file overwrites the ones before it.

$CALL GDXXRW.EXE CoalHeat2004.xlsx par=total rng=Total!a1:o165 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=internal rng=IEA_Internal!a1:o127 Rdim=1 Cdim=1
$CALL GDXXRW.EXE CoalHeat2004.xlsx par=external rng=IEA_ThirdParty!a1:o127 Rdim=1 Cdim=1


The above lines yield CoalHeat2004.gdx with only the parameter “external”. I want “total” and “internal” to also be in the gdx file.

I also tried creating separate gdx files and merging them into a single file, but this method results in a single merged set of data without separate parameter names. I can’t figure out how to use execute_unload or whatever other command to get my parameters onto the gdx file.

Thanks for your help.

Kirby

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

6

found the solution here >>https://forum.gamsworld.org/viewtopic.php?f=13&t=7590&hilit=multiple+excel+sheet&start=20

<<You cannot read this using the command twice. What happens is that gdxxrw overwrites the rawdata.gdx.
You have to do 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>>