Hi everyone
I would like to enter the data for parameters from an excel-file with several sheets. I created a model which seems to work with data entered in the simplest way possible but as I want to extend the model with larger data sets I need another method to integrate/include the data.
As I am working on a macOS computer i cannot use the gdxxrw method (as far as I understand it). I tried the following 3 other methods from the tutorial and documentary:
- Data Exchange with Text Files (https://www.gams.com/latest/docs/UG_Dat ... ASCII.html)
- Data Exchange with CSV2GDX (https://www.gams.com/latest/docs/T_CSV2GDX.html)
- Data Exchange with GAMS Connect (https://www.gams.com/latest/docs/UG_GAMSCONNECT.html)
Unfortunately these methods do not work on my model / computer. What method would you suggest for integrating greater data sets from excel to GAMS (on a mac computer)? Is any method advantageous compared to another one?
And sorry for the naive choice of words. I'm a newbie in GAMS and in programming in general.
I would greatly appreciate any help or hint from you. Thanks a lot in advance.
Marco
Data exchange from excel to gams (on macOS)
Re: Data exchange from excel to gams (on macOS)
Hi Marco,
GAMS Connect is a new platform independent tool set to read data from a range of external sources (including Excel), transform it, and make it available to GAMS models. This will be the future for data connectivity in GAMS. We already announced the retirement of a couple of older data tools and will offer proper replacements in Connect. To be able to use Connect you will need to update to GAMS 39.
I attached your example with some minor modifications to get it running. The added < characters in the parameter declaration will implicitly define the sets i, j and h. I also needed to modify the Excel file a bit (see attached) since the PandasExcelReader expects artificial indices for rowDimension=0 and/or columnDimension=0 (see https://www.gams.com/latest/docs/UG_GAM ... DERCONCEPT).
Best,
Aileen
GAMS Connect is a new platform independent tool set to read data from a range of external sources (including Excel), transform it, and make it available to GAMS models. This will be the future for data connectivity in GAMS. We already announced the retirement of a couple of older data tools and will offer proper replacements in Connect. To be able to use Connect you will need to update to GAMS 39.
I attached your example with some minor modifications to get it running. The added < characters in the parameter declaration will implicitly define the sets i, j and h. I also needed to modify the Excel file a bit (see attached) since the PandasExcelReader expects artificial indices for rowDimension=0 and/or columnDimension=0 (see https://www.gams.com/latest/docs/UG_GAM ... DERCONCEPT).
Best,
Aileen
Re: Data exchange from excel to gams (on macOS)
Hi Aileen
Thank you very much for the explanations and for the modifications to the model and to the excel file. I'm so happy about your help. It would have taken me ages to find all that information and solve these errors.
Thanks again and best regards
Marco
Thank you very much for the explanations and for the modifications to the model and to the excel file. I'm so happy about your help. It would have taken me ages to find all that information and solve these errors.
Thanks again and best regards
Marco
Re: Data exchange from excel to gams (on macOS)
Hi Aileen, thanks for the explanation. I am wondering how we could be able to write data from GAMS to excel. I would be grateful if you provide me any resource or a simple example code.
Re: Data exchange from excel to gams (on macOS)
Hello,
I would also be very interested in these resources or simple codes as @T_k said. It will be a great help for me.
I would also be very interested in these resources or simple codes as @T_k said. It will be a great help for me.
My website here
Re: Data exchange from excel to gams (on macOS)
The GAMS Connect documentation has examples: https://www.gams.com/latest/docs/UG_GAM ... _CONNECT03
-Michael
-Michael
Re: Data exchange from excel to gams (on macOS)
With the GAMS 46 release the PandasExcelReader is deprecated and it is recommended to use the new ExcelReader instead. The ExcelReader has the same functionalities as the PandasExcelReader but removes some limitations (e.g. the requirement for artificial indices) and adds some new features (e.g. skipEmpty, index).
With the ExcelReader you can read the original Excel file without modifying it by adding artificial indices. See attached files.
As the above documentation link is not valid anymore, here you find a simple Connect example for Excel. There are also additional examples in the Examples section of the documentation.
Aileen
With the ExcelReader you can read the original Excel file without modifying it by adding artificial indices. See attached files.
As the above documentation link is not valid anymore, here you find a simple Connect example for Excel. There are also additional examples in the Examples section of the documentation.
Aileen
- Attachments
-
- indata.xlsx
- (17.47 KiB) Downloaded 266 times
-
- Strommarkt mit loop + Data Connect.gms
- (6.79 KiB) Downloaded 270 times
Re: Data exchange from excel to gams (on macOS)
Hello I am learning Gams, I have a MacBook and I am trying to use ExcelReader to convert my SAM, I am unable please help. The message I am getting " symbol redefined- a second dat statement for the same symbol or a data statement after an assignment"
- Attachments
-
- Sample data.xlsx
- (34.77 KiB) Downloaded 76 times
Hello
I am new to GAMS and I am trying to import an excel workbook in GAMS on a MacBook. can I get some help please. I have been using Pandas Reader.
stg1_data.xlsx
I am new to GAMS and I am trying to import an excel workbook in GAMS on a MacBook. can I get some help please. I have been using Pandas Reader.
stg1_data.xlsx
Re: Data exchange from excel to gams (on macOS)
Hi,
The error indicates that you try to set data for your parameter twice. Perhaps your parameter has data already and you try to set data again from an embedded code section. Anyhow, the following code should bring the data from the Excel sheet into a two dimensional GAMS parameter:
The ExcelReader reads the data from the Excel file. rowDimension: 1 and columnDimension: 1 can be omitted since this is the default. If you want to drop all zeroes, you can specify valueSubstitutions: {0: .nan}. You don't need this if you only use the GAMSWriter afterwards anyways, but for writing a GDX file without keeping zeroes. The GAMSWriter agent finally writes the data to the GAMS symbol p.
Best,
Clemens
The error indicates that you try to set data for your parameter twice. Perhaps your parameter has data already and you try to set data again from an embedded code section. Anyhow, the following code should bring the data from the Excel sheet into a two dimensional GAMS parameter:
Code: Select all
Parameter p(*,*);
$onEmbeddedCode Connect:
- ExcelReader:
file: Sample data.xlsx
symbols:
- name: p
range: Sheet1!A1
#rowDimension: 1 # default: 1
#columnDimension: 1 # default: 1
#valueSubstitutions: {0: .nan} # drop 0 values (e.g. for writing a GDX file without zeroes)
#- GDXWriter: # write to GDX
# file: data.gdx
- GAMSWriter: # write to GAMS
writeAll: True
$offEmbeddedCode
display p;
Best,
Clemens
Re: Data exchange from excel to gams (on macOS)
Thanks so much for your help. really appreciate it.
Hello
I am new to GAMS and I am trying to import an excel workbook in GAMS on a MacBook. can I get some help please. I have been using Pandas Reader.
stg1_data.xlsx
I am new to GAMS and I am trying to import an excel workbook in GAMS on a MacBook. can I get some help please. I have been using Pandas Reader.
stg1_data.xlsx