Using csv2gdx with double column and row labels Topic is solved

Problems with modeling
Post Reply
MarcQC
User
User
Posts: 5
Joined: 5 years ago

Using csv2gdx with double column and row labels

Post by MarcQC »

Hello all,

Working on a Mac and using GAMS Studio, I want to import data from a 4 dimensional csv.file via csv2gdx.
The particularity of the csv.file is that it has two row and two column labels, and that there a empty fields.

Image

On a PC, what would work is:

Code: Select all

PARAMETER
MCS(*,*,*,*);

$CALL GDXXRW.EXE MCSAUTETA.xlsx par=MCS rng=MCS!A1:R18 Rdim=2 Cdim=2
$GDXIN MCSAUTETA.gdx
$LOAD MCS
$GDXIN
GO              = MCS('I','PUB','AG','GVT');
where GO is some value that can be found in MCS.

As I cannot directly import the excel file on a mac, what I try to do is the following:

Code: Select all

PARAMETER
MCS(*,*,*,*)

$CALL csv2gdx AUTETA.csv output=AUTETA.gdx id=data index=1,2 values = 3..lastCol autoCol=column colcount=18 acceptBadUels=y

$gdxIn AUTETA.gdx
$load MCS = data
$gdxIn
GO              = MCS('I','PUB','AG','GVT');
display GO
My goal would be to extract any value in MCS, like GO for instance.

However, I get error message 495: Load dimensions are different.

When I use instead the option: index = 1,2,3, I do not get an error message, but the AUTETA.gdx file is empty.
Image

I've tried many other options. To my understanding, option useHeader =Y is of no use to me, because it refers to a one dimensional column label.
I've also tried to define auxiliairy SETS for MCS(Column1,Column2,Row1,Row2), but is did not work either.
Finally, I tried to use of TABLE... like here: https://support.gams.com/interfaces:how ... onal_table but I did not find a workaround.

Does anybody have an idea how to solve this? I'm really stuck. Thank you in advance!

AUTETA.csv
(692 Bytes) Downloaded 178 times
AUTETA.xlsx
(8.8 KiB) Downloaded 162 times
AUTETAforForum.gms
(3.86 KiB) Downloaded 154 times
Capture d’écran, le 2021-07-02 à 15.16.28.png
Attachments
Capture d’écran, le 2021-07-02 à 15.26.02.png
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Using csv2gdx with double column and row labels

Post by bussieck »

CSV files have a single header row (if at all). No chance with multiple header rows. Since your matrix is symmetric in terms of row and column labels you can just forget the column header and patch them in later by the position. The following code does this:

Code: Select all

set r "row column labels" /r1*r100/; alias (r,c), (*,u1,u2,u3,u4); ;
parameter dataX(r,u1,u2,c);
* shave off first two lines
$call.checkErrorLevel tail -n +2 AUTETA.csv > AUTETA_nh.csv
$CALL.checkErrorLevel csv2gdx AUTETA_nh.csv output=AUTETA.gdx id=dataX index=1,2 values = 3..lastCol autoCol=r autorow=r colcount=19 fieldsep=semicolon

$gdxIn AUTETA.gdx
$load dataX
$gdxIn

set hdr(r,u1,u2); option hdr<dataX;
loop((r,u1,u2,c)$dataX(r,u1,u2,c), MCS(u1,u2,u3,u4)$hdr(c,u3,u4) = dataX(r,u1,u2,c));
display MCS;


I have attached the entire code. GAMS should really have some ways to works with spreadsheets on platforms other than Windows!

-Michael
AUTETAforForum.gms
(4.33 KiB) Downloaded 158 times
MarcQC
User
User
Posts: 5
Joined: 5 years ago

Re: Using csv2gdx with double column and row labels

Post by MarcQC »

Hello Michael,

thank you very much for your answer!

I had to cut the first two header lines manually in my csv file, because

Code: Select all

$call.checkErrorLevel tail -n +2 AUTETA.csv > AUTETA_nh.csv
would give me error message 798:
$call.xxx with illegal suffix - $call.async or $call.sync
I couldn't deal with that for now...

Your loop is working almost perfectly;-)
However, I realized that the label "TOT" from the CSV file is missing (second column, last line), and I was unable to generate it. :?
I understand that TOT is not predefined as a set, but neither is OTH, AG and F.
So it must be because TOT does not appear in the first column of the CSV file.
Do you have any idea on how to tackle that? That would be really great!!!
Thank you! Markus
AUTETAforForumR.gms
(4.89 KiB) Downloaded 157 times
AUTETAnHeads.csv
(583 Bytes) Downloaded 159 times
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Using csv2gdx with double column and row labels

Post by bussieck »

Your CSV file is missing a newline at the end of the file and that's why csv2gdx does not store this line (with TOT) in GDX. Just open in your editor and hit enter at the end of the file. You can also remove the ".checkerrorlevel" (this is a feature of recent GAMS versions, it always good to work with up-to-date software) from the $call (and hope thing work or check differently the return code from the program called). The tail -n +2 appends the new line. After that, everything works as expected.

-Michael
MarcQC
User
User
Posts: 5
Joined: 5 years ago

Re: Using csv2gdx with double column and row labels

Post by MarcQC »

Thanks so much!
Post Reply