Page 1 of 1

Reading Data from Excel , except some Cells

Posted: Tue Dec 04, 2018 9:14 am
by mahdigh
1 1 1 1
1 2 3 4
1 1 4887000 2003400 11252850
1 2 4174200 1682100 9444750
1 3 1949400 775950 4174800
1 4 1323000 557550 2601900
1 5 2011500 796950 3807300
1 6 5262300 2140950 11458650
1 7 3777300 1482600 8004150
1 8 3755700 1501500 7776300
1 9 82500
1 10 355500

Hello
I want My Gams code reads this Excel file - Except empty cells?
Is it possible?
How can i write its code?

Re: Reading Data from Excel , except some Cells

Posted: Tue Dec 04, 2018 9:57 am
by gamsadmin
Hi
For this you can use gdxxrw (there is a comprehensive documentation with lots of examples: https://www.gams.com/latest/docs/T_GDXXRW.html ). If there is no value in the spread sheet, this is no problem.
Cheers
Renger

Re: Reading Data from Excel , except some Cells

Posted: Tue Dec 04, 2018 11:01 am
by mahdigh
Thanks
i found https://www.gams.com/latest/docs/T_GDXX ... _SKIPEMPTY
but Gams should ignore or skip blank cells - this is for blank rows or columns
how can i solve this problem?

Re: Reading Data from Excel , except some Cells

Posted: Tue Dec 04, 2018 11:21 am
by mahdigh
how can Gams skip blank cells (not rows or columns) just some Cells?

Re: Reading Data from Excel , except some Cells

Posted: Wed Dec 05, 2018 6:56 am
by Renger
Please give an example and why it is necessary to skip a cell. If there is no value, there is no value in the data you read...
Cheers
Renger

Re: Reading Data from Excel , except some Cells

Posted: Wed Dec 05, 2018 11:39 am
by mahdigh
Renger wrote: 5 years ago Please give an example and why it is necessary to skip a cell. If there is no value, there is no value in the data you read...
Cheers
Renger

How can i send a picture to give an example?
there is 4 modes for transportation
1 - truck , 2-rail , 3-boat , 4-pipeline
imagine we want to transport our products from i to j
there is no way for number 3 mode in some i to j ! and we have to skip it!

Re: Reading Data from Excel , except some Cells

Posted: Thu Dec 06, 2018 7:58 am
by Renger
Hi
You can use mappings to exclude not-allowed options:

Code: Select all

 set mapArcs(i,j,mode) 
 /
    1.2.1
    1.2.2
    1.2.4
    1.3.1
    1.3.2
...
/
Now you can use the mapping in your equations:

inflow_eq(i,ode)..
IF(i,mode) =E= sum(j,$map(i,j,mode), X(i,j,mode));
...
[/code]
You can also use gdxxrw to define the mapping:

Code: Select all

set  map(i,j,mode);
map(i,j,mode)$myparam(i,j,mode) = YES;
This defines the members of the set map(i,j,mode) based on the non-zero elements in the parameter read from your excel file.

Cheers
Renger