Gekko User Guide > Data Management User Guide > Databanks and variables

There is a difference between reading a .gbk Gekko databank and importing a data file in a 'foreign' format. A similar distincion can be made between writing a .gbk databank and exporting data to en external foreign format. Import and export of data is characterized by the fact that it the files are in non-gbk formats like csv, xls(x), tsd, px, etc.

The Gekko databank files (gbk) support all Gekko variable types, both series, scalars and collections. Foreign formats do not necessarily support all Gekko data types, but a number of formats can be used for transportation of timeseries data. Examples could be the csv, prn, and tsd formats. These are characterized by having a well-described and open format. In addition, many software packages will be able to import and export timeseries to these formats.

Note that Gekko can also exchange data with Excel and gnuplot. In both cases, Gekko can do more than just send data to and from formats supported by Excel and gnuplot and in both cases perform some of the tasks that user would otherwise have to do after exporting the data. The use of Excel or gnuplot as a backend for Gekko is dealt with in a separate section.

Supported transport formats:

Program/format	File type	Import	Export	Comment
AREMOS	.dmp	x		AREMOS also supports the .tsd format, but .dmp (dump) files have a lot better precision (significant digits).
Apache Arrow	.arrow		x	Supported by many programs, including R and Python. Import will come soon, too.
csv	.csv	x	x	An open text format. Many programs read this format, including Excel.
Excel	.xls(x)	x	x	Regarding Excel, there is also the so-called Gekcel add-in, where Gekko statements can be issued from within Excel, without opening up Gekko at all.
flat	.flat	x	x	A Gekko-specific text format that resembles Gekko series statements, but is more simple and very fast.
gcm	.gcm	x	x	When exporting, the exported timeseries are exported in the form of .gcm statements that can be used directly in Gekko. Not very fast, cf. the 'flat' format.
GAMS	.gdx	x	x	The .gdx format is supported for both import and export. Is often used for array-series, and GAMS sets are interchanged, too.
gnuplot	.dat		x	Gekko can export data in a form suitable for gnuplot.
PCIM	.bnk	x		PCIM has previously been used for modeling. Gekko can import its binary databank format.
prn	.prn	x	x	A csv-like open text format.
PC-Axis	.px	x		Gekko can import PC-Axis px files (these are text-based). See also the DOWNLOAD statement.
Python	.py		x	Gekko can export data as a Python script. Alternatively, use the Apache Arrow format.
R	.r		x	Gekko can export data as a R script. Alternatively, use the Apache Arrow format.
tsd	.tsd	x	x	An open text-based format used by AREMOS and EViews.
TSP	.tsp	x	x	Can import and export TSP records.

Import of data

IMPORT and READ are similar, but READ is meant for Gekko's own .gbk databank format, for instance loading the file data.gbk like this:

read data; //Gekko appends .gbk automatically

Regarding timeseries, READ gets all observations regardless of the global time period, and READ also first clears the first-position databank, before data is loaded.

In contrast, the IMPORT statement restricts import to the global time period (unless otherwise stated), and merges data with existing data in the first-position databank. Therefore note that IMPORT may not import all observations from the data file, and existing values outside of the global time period will remain unchanged. Use IMPORT<all> to import all observations and replace existing timeseries completely. If you need to IMPORT into an empty databank, you can use CLEAR first.

Example:

import <csv> data.csv; //will only import for the global time period

import <csv all> data.csv; //imports all data for all observations in the data file

When reading csv and xls(x) files, one must be aware that Gekko per default reads the time series in rows, and expects the first column to contain series names, and the first row to contain dates. If the time series are in columns (transposed), this must be specified with the <cols> option in IMPORT:

import <csv cols> data.csv; //series running downwards in columns
import <xlsx cols> data.xlsx; //series running downwards in columns
import <prn> data.prn;

Regarding prn, this format is similar to csv, only it uses space as delimiter, rather than semicolon, and the first "cell" (corresponding to A1 in a spreadsheet) inside the prn file contains info on whether the timeseries run outwards in rows, or downwards in cols.

Export of data

EXPORT and WRITE are similar, but WRITE is meant for Gekko's own .gbk databank format, for instance writing the file data.gbk like this:

write data; //Gekko appends .gbk automatically

Regarding timeseries, WRITE writes all observations regardless of the global time period. In contrast, the EXPORT statement restricts export to the global time period (unless otherwise stated). Therefore note that EXPORT may not export all observations from the first-position databank. Use EXPORT<all> to export all observations. It is also possible to store one or more selected variables in the .gbk databank file:

write x1, x2, x3 file = data; //Three series, Gekko appends .gbk automatically

The EXPORT statement is very similar, for instance (here, the csv format is used: the file is indicated with extension .csv for clarity, even though Gekko would add the extension automatically):

export <csv> data.csv; //All series, global period
export <csv all> data.csv; //All series, all observations
export <csv> x1, x2, x3 file = data.csv; //Three series, global period
export <csv all> x1, x2, x3 file = data.csv; //Three series, all observations

The EXPORT statement supports Excel xls(x) files, but export of data to Excel can also be done using the SHEET statement, where you can control the sheet name, starting cell, and the orientation (columns or rows) of the timeseries. Regarding Excel, the difference between EXPORT and SHEET can summed up like this:

•EXPORT<xlsx> exports timeseries to a matrix of cells starting in cell A1, where the first row is dates, and the first column is series names. Only raw values are exported, no transformation is possible, and EXPORT<xlsx> is very similar to EXPORT<csv>.

•SHEET is essentially a print or plot statement in disguise, allowing all the same transformations and expressions used in prints or plots, for instance the use of operators, mathematical expressions, functions, etc. SHEET also allows choosing the sheet name, starting cell name, and it can suppress period and/or name labels to only show raw data, etc.

The SHEET statement is therefore a flexible way of transferring data to Excel. Excel must be installed for SHEET to show the results, and the statement does not work with e.g. LibreOffice Calc or Google Sheets. For this, it is recommended to use EXPORT<csv>, since the csv format is supported by most spreadsheet programs. Alternatively, it is possible to use the CLIP statement, which is equivalent to SHEET, but places the result on the clipboard for later pasting with Ctrl-V.

As a minimum, the SHEET statement must specify which variables are to be transferred to Excel, e.g.:

sheet x1, x2, x3;

The above statement puts the timeseries x1, x2, and x3 into Excel (in rows). No file is saved, and the Excel sheet containing the data is automatically opened by Excel and shown. As mentioned, SHEET is a PRT/PLOT statement in disguise, so it allows all kinds of transformation, for instance:

sheet x1/(x1+x2), x3 <p>;
sheet log(x1/x2) 'lx1x2' file = logdata.xlsx; //changing the label

In the first statement, x3 is shown as percentage growth due to the operator <p>. In the second statement, the result is not shown in Excel, but instead the file logdata.xlsx is written to disk. The label 'lx1x2' is set, because a label (name) like 'log(x1/x2)' is not useable if the file is being re-imported into Gekko from this spreadsheet (with IMPORT<xlsx>).

In the following example, several series are exported from the bank hist1115.gbk to Excel:

read hist1115;
time 2010 2020;
sheet fY, fM, fCp, fCo, fI, fE;

After issuing a SHEET statement, Excel should start up, showing the data. (Note that IMPORT<xlsx> and EXPORT<xlsx> work regardless of Excel being installed on the pc).

Piping

A special kind of export is using a pipe. The PIPE statement is generally used to store Gekko output in a file by directing (piping) Gekko's output into the file, instead of on screen. In the following example, the output is saved in a the text file data.prn:

pipe prt.txt;
prt <n> x1, x2, x3;
pipe <stop>;

The <n> operator suppresses percentage growth, which is normally shown in PRT, together with levels. After pipe<stop>; the file prt.txt contains the print, and no output is shown in the Gekko output window. You can use TELL to add text to such a pipe file. Instead of text format, the PIPE statement can also generate output in html format, and with the use of PIPE<html> in combination with PLOT and TABLE, quite advanced "reports" can be produced. Use pipe<echo> to both print on screen and pipe to file.