Reading Data from Excel , except some Cells

questions about GAMS' tools
Post Reply
mahdigh
User
User
Posts: 4
Joined: 1 week ago

Reading Data from Excel , except some Cells

Post by mahdigh » 1 week ago

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?

User avatar
gamsadmin
Site Admin
Site Admin
Posts: 11
Joined: 2 years ago

Re: Reading Data from Excel , except some Cells

Post by gamsadmin » 1 week ago

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

mahdigh
User
User
Posts: 4
Joined: 1 week ago

Re: Reading Data from Excel , except some Cells

Post by mahdigh » 1 week ago

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?

mahdigh
User
User
Posts: 4
Joined: 1 week ago

Re: Reading Data from Excel , except some Cells

Post by mahdigh » 1 week ago

how can Gams skip blank cells (not rows or columns) just some Cells?

User avatar
Renger
Posts: 219
Joined: 2 years ago

Re: Reading Data from Excel , except some Cells

Post by Renger » 1 week 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

mahdigh
User
User
Posts: 4
Joined: 1 week ago

Re: Reading Data from Excel , except some Cells

Post by mahdigh » 1 week ago

Renger wrote:
1 week 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!

User avatar
Renger
Posts: 219
Joined: 2 years ago

Re: Reading Data from Excel , except some Cells

Post by Renger » 1 week ago

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

Post Reply