Friday, April 15, 2016

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.

image

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

image

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

image

The resulting Pivot Table looks like:

image

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";