Structuring of Excel output-file in GAMS

[Jarenka]

Dear,

I refer to this post: viewtopic.php?f=2&t=10904

I have a problem with structure of Excel file - output file with variables having many dimensions.
I added as recommended:

rdim=5 cdim=1

. My code looks like that in the attachment.
Parameter "qabg" is six-dimensional.

I still get the same structure of Excel as I showed in the post.

The aim of it is to receive Excel file with the same structure as in *gdx file. Mainly, all dimensions should be in columns.

I hope you understand my problem.

Irena

[Renger]

Hi Jarenka
If I understand you correctly, you want to have the parameter written to the excel file with the 6 dimension in the columns. For this, you write cdim=6 (and no rdim or rdim=0).
Cheers
Renger

[Jarenka]

Hi Renger,

This is something that I also tried.
And I didn't get any Excel output file.
See the attachment.

[Renger]

How many elements does qab has? If it is over 1 million rows, you can't save it in Excel because of the maximum size of a sheet (otal number of rows and columns on a worksheet 1,048,576 rows by 16,384 columns).
Cheers
Renger

[Jarenka]

Hi Renger,

It has 758,609 x 19 = 14,413,571 rows (in .gdx file). It seems that no matter what, it automatically transforms the gdx-file into Excel file with 758,609 rows and 24 columns (5 dimensions + 19 years).
Where did you find this number?

This is good information to know, because we are working with variables that have many dimensions. In this case, I have to find a better solution to exporting variables and their presentation.

My task is to extract variables for our users, and present them in a user-friendly way.
Since the variables are in data set form, the users cannot work with them. If they had the variables in an excel file, they would play with 'pivot' table or some figures in the file, etc.

Do you know an interface, connected to GAMS, that can easily extract some variables (with many dimensions) and present them on figures, tables, etc? The point is to create a presentation or files, which users can easily understand and work with.


Jarenka

[Renger]

Hi Jarenka

I think PowerPivot (an add-in for Excel 2010 upwards), might be the solution. I haven't worked with it, but I know it has no problems with over 150 million rows.
If the problem of importing the data from gdx would remain (even with PowerPivot), you could always, either

• write a csv file from your Gams program,
• send the data to MySQL or Access and use the data import from Excel.

Cheers
Renger
PS. THe number of around 1 million rows can be found here

[Jarenka]

Thank you Renger,

Concerning *csv files. Does it work in GAMS Studio?

[Renger]

Hi Jarenka
Yes, just use gdxdump (dumps to gdx or csv files).
Cheers
Renger

[Jarenka]

Hi again,

I have tried to open a *csv file with following commend (trying with different option with delimiter):

Code: Select all

$call gdxdump csv_output.gdx output=Employment_qaeg.csv delim=comma symb=qaegT format=csv

And I always receive an uncompleted csv-file with error - see attachment.

Do I do something wrong, or is there another way to extract a full dimension parameter from a gdx file?

Irena

[Renger]

Hi Jarenka

You show an unrelated figure (showing the number of lines is too big). If you want help on the csv issue, see if you get the error if the parameter would only have a few lines in the csv.
Cheers
Renger