.inc files

GAMS
1

Hi there,

I am having trouble importing data using “.inc” files.

I am able to import data my parameters from an excel sheet. But it takes such a long time to process, and I have an old example where parameters were imported using .inc files so I decided to give it a try (this seemed faster for the parameters i was able to import, but correct me if not!).

For parameters which only contain one value or an array i managed to read the .inc file. But when I have columns I can’t. I suppose this has to do with how data is formatted (i copied a table from excel and pasted it on the notepad file) - i get errors 170 (domain violation - dimension different) and 334 (i guess because of 170). I have also tried .csv files, but i did not manage to do it: anyway, would it be faster than from the excel file?

example of the data i need to input (couldn’t attach, “invalid format”):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
d1 20 40 60 80 100 120 140 160 180 200 220 240 260 280 300 320 340 360 380 400 420 440 460 480
d2 30 50 70 90 110 130 150 170 190 210 230 250 270 290 310 330 350 370 390 410 430 450 470 490
d3 34 54 74 94 114 134 154 174 194 214 234 254 274 294 314 334 354 374 394 414 434 454 474 494
d4 38 58 78 98 118 138 158 178 198 218 238 258 278 298 318 338 358 378 398 418 438 458 478 498

Many thanks in advance.
F

2

The following works for me. Since you did not align the data (required for normal Table statement), you need to read under $ondelim. For this you need to add the ‘d’ in the header row:

set i / 1*24 /, d / d1*d4 /;
$onecho > data.inc
d 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
d1 20 40 60 80 100 120 140 160 180 200 220 240 260 280 300 320 340 360 380 400 420 440 460 480
d2 30 50 70 90 110 130 150 170 190 210 230 250 270 290 310 330 350 370 390 410 430 450 470 490
d3 34 54 74 94 114 134 154 174 194 214 234 254 274 294 314 334 354 374 394 414 434 454 474 494
d4 38 58 78 98 118 138 158 178 198 218 238 258 278 298 318 338 358 378 398 418 438 458 478 498
$offecho
table data(d,i)
$ondelim
$include data.inc
$offdelim
;

-Michael

3

Michael,

Thanks so much for your quick feedback.

Understood the $ondelim and $offdelim, also the first “d”. Additionally, I was only declaring the table as “parameter” (and not “table”) - and that was working for importing data from the excel.

4

Not sure how you read data from Excel, but with gdxxrw (the best supported way) you get a GDX file and then it does not matter if you declare as parameter or table.

-Michael

5

Dear Ferrib
I agree with bussieck. If your data is in Excel sheet file, the best way is to use GDXXRW which is a very powerful command to read and write data from and on Excel sheet files.
I had many problems to use .inc files, but I am using the above command without any problem.
Regards, Abbas Mahmoudabadi

6

Hi there,

I tried GDXXRW but .inc is way faster. I was losing 1 minute to input the data via GDXXRW, but with .inc files it takes literally nothing.

Best regards

7

Hi
GDXXRW has the option “CheckDate”: it checks before importing the data from an excel sheet if the sheet has been changed in the meantime. If not, it will skip the import and use the already available gdx file from the most recent import.
Cheers
Renger

8

Hi again,

Just ressurrecting this topic to see if any of you can provide an example with the CheckDate option. I can’t make it work. Let’s say my example for loading data is as follows, where should i place my “checkdate”?

Set d;
$call gdxxrw.exe i=input.xlsx set=d rng=Sheet1!c2 rdim=1 cdim=0
$gdxin input.gdx
$load d
$gdxin
display d;

Thanks in advance

9

Hi
It should be written at the end of the line and correctly spelled: not “checkdate” but CheckDate.
If this doesn’t work, please send an excel sheet and the code that doesn’t work.
Cheers
Renger

10

Hi Renger,

Thanks for your help.

Attached the .xlsx file and the gams code.
Untitled_16.gms (121 Bytes)
input.xlsx (7.99 KB)

11

Hi
Everything works fine. You might be thinking that it doesn’t work because the checking takes some time. Just try to read in a parameter in excel that fills a complete sheet, then you will notice the difference.

$call gdxxrw.exe i=input.xlsx set=d rng=dem_price!a2 rdim=1 cdim=0 CheckDate
$gdxin input.gdx

Output in the console with the line "No new file written (CheckDate is active):

--- call gdxxrw.exe i=input.xlsx set=d rng=dem_price!a2 rdim=1 cdim=0 CheckDate

GDXXRW           24.9.1 r63795 Released Aug 30, 2017 VS8 x86 32bit/MS Windows 
Input file : c:\Users\renge\Downloads\input.xlsx
Output file: c:\Users\renge\Downloads\input.gdx
No new file written (CheckDate is active)
Total time = 1453 Ms
--- GDXin=c:\Users\renge\Downloads\input.gdx
--- Starting execution: elapsed 0:00:01.600
*** Status: Normal completion
--- Job Untitled_16.gms Stop 11/01/18 07:52:04 elapsed 0:00:01.601

Cheers
Renger

12

Hi Renger,

I checked the log and you are right CheckDate is working. I guess it is me who is using GDX in a wrong way. If i input many parameters using similar code will end up only saving the last parameter on GDX:

Set g;
$call gdxxrw.exe i=input.xlsx set=g rng=Bid_EN_price_gen_sell!a2 rdim=1 cdim=0
$gdxin input.gdx
$load g
$gdxin
display g;

Set d;
$call gdxxrw.exe i=input.xlsx set=d rng=Bid_EN_price_dem_buy!a2 rdim=1 cdim=0
$gdxin input.gdx
$load d
$gdxin
display d;

I will need to spend some time browsing the help files for GDX.

Thanks.

13

CheckDate checks if the GDX file is newer than the Excel file. But it should also contain the right parameters, so a code that uses the same GDX for different symbols together with CheckDate will never work. If you use different GDX names (using the output= parameter) you will be fine:

$call gdxxrw.exe i=input.xlsx output=input_g set=g rng=Bid_EN_price_gen_sell!a2 rdim=1 cdim=0 CheckDate
$gdxin input_g.gdx
$load d
$call gdxxrw.exe i=input.xlsx output=input_d set=d rng=Bid_EN_price_dem_buy!a2 rdim=1 cdim=0 CheckDate
$gdxin input_d.gdx
$load d

You can improve the Excel reading significantly by reading multiple symbols in one gdxxrw. gdxxrw works in combination with Excel. So each individual call to gdxxrw opens (with Excel in background) your workbook input.xlsx. Depending on the size of this workbook this can take significant time. By batching the gdxxrw reading requests together and calling gdxxrw once you only open the workbook once. gdxxrw alllows you to put multiple requests in one call:

$call gdxxrw.exe i=input.xlsx set=g rng=Bid_EN_price_gen_sell!a2 rdim=1 cdim=0 set=d rng=Bid_EN_price_dem_buy!a2 rdim=1 cdim=0 CheckDate
$gdxin input.gdx
$load g d

If you have many symbols to read you should use a parameter file and provide the filename with a @ sign (see gdxxrw docs at https://www.gams.com/latest/docs/T_GDXXRW.html):

$onEcho > input.txt
i=input.xlsx
set=g rng=Bid_EN_price_gen_sell!a2 rdim=1 cdim=0
set=d rng=Bid_EN_price_dem_buy!a2 rdim=1 cdim=0
$offecho
$call gdxxrw.exe  @input.txt  CheckDate
$gdxin input.gdx
$load g d

-Michael

14

Thanks Michael, works like a charm!