Structuring of Excel output-file in GAMS

Problems with syntax of GAMS
Jarenka
User
User
Posts: 55
Joined: 1 year ago

Re: Structuring of Excel output-file in GAMS

Post by Jarenka » 3 months ago

Hi Renger,

Yes, it works with a few lines for CSV-format.
So if I understand it correctly, csv-format has also limited number of rows. Right?

Jarenka

User avatar
Renger
Posts: 406
Joined: 3 years ago

Re: Structuring of Excel output-file in GAMS

Post by Renger » 3 months ago

Not as far as I know. If I run the following Gams code, which generates 1000000 rows with 6 dimensions:

Code: Select all

set t /1*10/;
alias(t,t2,t3,t4,t5,t6)
parameter test(t,t2,t3,t4,t5,t6);
test(t,t2,t3,t4,t5,t6) = uniform(0,10);
execute_unload 'results.gdx', test;
(be sure to use execute_unload otherwise the parameter test will not be saved in the gdx file):
And then I write this away in a csv file using the following code

Code: Select all

parameter test;
$gdxin results.gdx
$load test
$call gdxdump results.gdx output=results.csv delim=comma symb=test format=csv
I get a csv file with 1000000 rows + 1 row for the heading.

So, no limit.

Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

Jarenka
User
User
Posts: 55
Joined: 1 year ago

Re: Structuring of Excel output-file in GAMS

Post by Jarenka » 3 months ago

Hi Renger,

1,000,000 rows is not a problem. The problem with csv starts when I have over 1,048,576 rows.
Can you try to extend you test-parameter to have over 1,048,576 rows?

Jarenka

User avatar
Renger
Posts: 406
Joined: 3 years ago

Re: Structuring of Excel output-file in GAMS

Post by Renger » 3 months ago

Hi Jarenka
On my machine, I tried with 15^6 (> 11 million lines) and it works fine. Produces a CSV file of 450Mb.
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

Jarenka
User
User
Posts: 55
Joined: 1 year ago

Re: Structuring of Excel output-file in GAMS

Post by Jarenka » 3 months ago

Hi Renger,

I tried your code first and seems I have a problem with gdx-file. See attachment:

Jarenka
Attachments
code.JPG

User avatar
Renger
Posts: 406
Joined: 3 years ago

Re: Structuring of Excel output-file in GAMS

Post by Renger » 3 months ago

Hi Jarenka

I used two separate gams files. If you put it all in one file, you don't need to load test (and if you would do, you should use execute_load as the gdx file is produced in execution time and not available in compile time).
Just run:

Code: Select all

set t /1*15/;
alias(t,t2,t3,t4,t5,t6)
parameter test(t,t2,t3,t4,t5,t6);

test(t,t2,t3,t4,t5,t6) = uniform(0,10);

execute_unload 'results.gdx', test;
$call gdxdump results.gdx output=test.csv delim=comma symb=test format=csv

Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

Jarenka
User
User
Posts: 55
Joined: 1 year ago

Re: Structuring of Excel output-file in GAMS

Post by Jarenka » 3 months ago

Yes. I corrected the code. There are no errors.
But the problems appears when I want to look at results.csv file. It shows only 1,048,576 rows (I increased dimensions in test-parameter) and no more.
Attachments
code1.JPG
code.JPG

User avatar
Renger
Posts: 406
Joined: 3 years ago

Re: Structuring of Excel output-file in GAMS

Post by Renger » 3 months ago

Yes, by the limit of excel. Now you should use powerpivot to import the data into excel. Then you will have the possibility to make a pivot table of your data (I haven't used this, but checking powerpivot, I imported the data and produced a pivot table in 2 minutes)
image.png
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

Jarenka
User
User
Posts: 55
Joined: 1 year ago

Re: Structuring of Excel output-file in GAMS

Post by Jarenka » 3 months ago

Everything would be perfect if I had PowerPivot :-)

User avatar
Renger
Posts: 406
Joined: 3 years ago

Re: Structuring of Excel output-file in GAMS

Post by Renger » 3 months ago

Office 365 comes with PowerPivot. If you don't have a current version, you could always import the data in Access (or MySQL, which is free).
You can, also in earlier versions of Excel, connect to a database, do queries and import these (so you don't have to import all the data). Once you have imported your query /table you can make graphs, tables, etc. The main point is here that you still are bound by the around 1 million rows, but you probably will want to aggregate or filter this data anyway.
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist

Post Reply