Page 2 of 3
Re: Structuring of Excel output-file in GAMS
Posted: Mon Aug 05, 2019 12:57 pm
by Jarenka
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
Re: Structuring of Excel output-file in GAMS
Posted: Mon Aug 05, 2019 6:00 pm
by Renger
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
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 8:19 am
by Jarenka
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
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 8:29 am
by Renger
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
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 8:40 am
by Jarenka
Hi Renger,
I tried your code first and seems I have a problem with gdx-file. See attachment:
Jarenka
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 9:39 am
by Renger
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
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 9:57 am
by Jarenka
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.
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 10:16 am
by Renger
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)
Cheers
Renger
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 11:03 am
by Jarenka
Everything would be perfect if I had PowerPivot
Re: Structuring of Excel output-file in GAMS
Posted: Tue Aug 06, 2019 11:18 am
by Renger
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