Export large result sets to a pivot table

In the post http://yetanothermathprogrammingconsultant.blogspot.com/2016/04/csv-file-too-large-to-view-or-import.html I discussed how to use a CSV file as an external data source for an Excel pivot table. This trick will allow us to view large data sets that don’t fit easily in Excel. In this post I want to expand a little on this, and show how a large GAMS solution set can be exported to a CSV file and how we can base a pivot table on this. This is all automated using a simple script.

There are actually models around that generate a ton of solution results. In some case, comprehensive reporting asks for mixing in some input data for reference. That means the total amount of data used for reporting can be very large.

In the above example we just have a few assignments. In practice we may have numerous assignments to populate the cube.

First export to a GDX file and then convert to a CSV file.

The resulting Pivot Table looks like:

The complete script looks like:

$ontext

   Example code that creates a pivot table with
   CSV file as external data source.

$offtext

sets c,cty,yr,attr;
parameter psd(c,cty,yr,attr) 'production, supply and distribution';

$gdxin psd_alldata.gdx
$loaddc c=dim1 cty=dim2 yr=dim3 attr=dim4 psd

parameter TotalProduction(c,yr);
TotalProduction(c,yr) = sum(cty,psd(c,cty,yr,'Production'));


alias(Commodities,Countries,Year,Attributes,*);
parameter Cube(Commodities,Countries,Year,Attributes);

Cube(c,cty,yr,attr) = psd(c,cty,yr,attr);
Cube(c,'World',yr,'Production') = TotalProduction(c,yr);

$set gdxfile         cube.gdx
$set csvfile         cube.csv

execute_unload "%gdxfile%",Cube;
execute "gdxdump %gdxfile% output=%csvfile% format=csv symb=Cube";

$set textdriver      {Microsoft Text Driver (*.txt; *.csv)}
$set xlsfile         %system.fp%pivotcube.xlsx
$set scriptname      script.vbs

$onecho > %scriptname%
Wscript.Echo "Creating pivot table...."
Wscript.Echo "This will take a minute."
Wscript.Echo ""
Set xl = CreateObject("Excel.Application")
xl.DisplayAlerts=False
Set wb = xl.Workbooks.Add
Wscript.Echo "Create PivotCache"
Set pc = wb.PivotCaches.Create(2)
pc.Connection = "ODBC;Driver=%textdriver%;Dbq=%system.fp%;"
pc.CommandText = "select * from [%csvfile%]"
Wscript.Echo "SQL:select * from [%csvfile%]"
If wb.Sheets.count = 0 Then
   Set sh = wb.Sheets.Add()
Else
   Set sh = wb.Sheets(1)
End If
Wscript.Echo "Create PivotTable"
Set pt = pc.CreatePivotTable(sh.Range("A1"))
pt.SmallGrid = False
pt.PivotCache.RefreshPeriod = 0
Wscript.Echo "Initial Layout"
pt.PivotFields("Commodities").Orientation=3
pt.PivotFields("Attributes").Orientation=3
pt.PivotFields("Year").Orientation=2
pt.PivotFields("Val").Orientation=4
pt.PivotFields("Countries").Orientation=1
pt.PivotFields("Commodities").ClearAllFilters
pt.PivotFields("Commodities").CurrentPage = "Coffee, Green"
pt.PivotFields("Attributes").ClearAllFilters
pt.PivotFields("Attributes").CurrentPage = "Production"
pt.PivotFields("Countries").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
pt.ColumnGrand = False
pt.RowGrand = False
pt.TableStyle2 = "PivotStyleMedium7"
Wscript.Echo "Saving %xlsfile%"
wb.SaveAs("%xlsfile%")
wb.Close
xl.Quit
$offecho

execute "cscript %scriptname% //Nologo";