Saves data structures in Genstat, Excel, R, Quattro, dBase, SPlus, Gauss, MatLab, SAS, Instat, Image or text files (D.B. Baird).
|What to print (
||Data file to be written|
||Action to take if the file already exists (
||Whether to leave the column names in the file in plain form rather than decorating them with the column type information i.e.
||Name of new sheet to be added to an existing Excel file|
||Whether to suppress column names in output to spreadsheet or text file (
||Description for spreadsheet|
||Whether to define the complete sheet as read only (
||Genstat commands to analyse columns in the spreadsheet|
||Genstat commands to be run once before the analysis of any columns in the spreadsheet|
||The name of the dummy (if any) used the
||Options for CSV files (
||Options for HTML files (
||How to match columns when appending (
||Identifier for the factor, or text containing the name of the factor, to identify appended sections in the output file|
||Labels for the
||Up to four
||Columns in the file to use as keys when
||Whether to use columns with matching names to replace existing columns when concatenating or merging (
||Optional output file arguments to be passed to the
||The factor to be used for colouring the rows (the factor must have colours defined by the
||The format to use when displaying tables with two or more classifying factors (
||String to represent a numerical missing value when writing to a text file (
||Whether to delete sheets if you are overwriting a multiple paged file with a single page (
Specifies how to output non-ASCII characters to text files (
Whether to include the column descriptions in spreadsheet files (
Number of seconds to wait when a file is open in another process; default 10
Private option for QTL files, not for general use
||The data structures to be written to the file, these must be compatible (i.e. of the same length)|
||Names for the columns to be saved|
||Whether the column is to be defined as read only when option
||Specifies background colours for factor columns|
||Specifies foreground colours for columns|
||Specifies background colours for columns|
||Specifies numbers of decimals for the columns|
EXPORT saves the data structures specified by the
DATA parameter to a disk file specified by the
OUTFILE option in a foreign data format specified by the extension of the file name. The available extensions are:
.XLS for Excel,
.XLSX for Excel 2007-13,
.WQ1 for Quattro,
.ODS for Open Office Spreadsheet,
.DBF for dBase,
.FMT for Gauss,
.SDD for SPlus,
.RDA for R,
.TPT for SAS transport,
.WOR for Instat,
.MAT for MatLab,
.ARFF for Weka Attribute,
.TXT for plain ASCII text,
.CSV for comma delimited text,
.TAB for tab delimited text,
.HTM for a HTML table,
.RTF for Word Rich text format,
.GSH for Genstat spreadsheet,
.GWB for Genstat work book, and
.PSD for an image file. An image file can be created either from single matrix containing RGB colour values, or three columns of variates or factors columns (specifying x-coordinates, y-coordinates and RGB colour values), or five columns of variates or factors columns (specifying x-coordinates, y-coordinates and red, green and blue colour values). The coordinate (0, 0) corresponds to the top left corner of the image, and the y-values increase as you move down the image.
Note that, if you save a file in .XLS format (Excel 2- 2003 file format) from Genstat and then open it in Excel 2010 or later versions, you will get a warning. Excel 2010 or later versions always do this when asked to open a file in .XLS format that was not saved by Excel. The warning will say
Office has detected a problem with this file. Editing it may harm your computer. Click for more details.
If you click this warning and then click the
Edit Anyway button, the file will open as expected with no further issues. Saving the file with Excel will stop this happening in the future. However, if you are using Excel 2010 or later versions, it is always best to use the Excel 2007-13 .XLSX file format, and then this will not happen.
SHEETNAME option lets you specify the name of the sheet to add to an Excel file, rather than using the default
'Genstat data'. The name should only contain letters, numbers and spaces.
METHOD option controls how
EXPORT behaves when asked to overwrite an existing file. The available settings are
replace, with a default of
prompt in interactive mode, and
fail in batch mode. The following example shows how
METHOD=add can be used to build up pages in a Genstat workbook file:
TEXT OutFile; VALUE='Results.GWB'
EXPORT [OUTFILE=OutFile; METHOD=overwrite; SHEET='Maximums']\
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Parameters']\
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Components']\
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Lanes']\
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Warping'] eX,W
append setting lets you append new values to an existing page. The
GROUPS option can define a factor column in the output file to identify the blocks of values that are appended; this can be set either to an existing factor (whose identifier will then be used) or a text containing the name to be used for that column in the file. The
GLABEL option can supply labels for the appended blocks. On the first append, this may be set to two values, where the first value identifies the new (appended) block, and the second identifies the original block of values. The
COLMATCH option controls whether the columns are matched by name or position. For example:
CALCULATE X1,Y1,Z1 = GRNORMAL(1000; 0; 1)
EXPORT [OUTFILE='Test.gsh'; METHOD=overwrite] X1,Y1,Z1
CALCULATE X2,Y2,Z2 = GRNORMAL(100; 3; 4)
EXPORT [OUTFILE='Test.gsh'; METHOD=append; GROUPS=Source;\
METHOD=concatenate, the new data are added as new columns on the right-hand side of an existing page. The new data can also be added as new columns on the right-hand side the page by setting
DATA variables are now merged with the original rows using up to four key columns specified by the
WITH options (for the new and original rows, respectively). If
MATCH is not specified, the first
DATA variable is used. If
WITH is not specified, the
MATCH variables are matched with the same number of initial columns in the page. If a column with the same name already exists in the page when concatenating or merging, the default action is to rename the new column by adding a number to the end of the name to make it unique. Alternatively, if you set option
UPDATE=yes, the new column will replace the existing column.
METHOD=replace, then for Genstat spreadsheet files or Excel .XLSX files, the sheet may replace an existing sheet within the file. The name of the sheet to be replaced must be supplied by the
SHEETNAME option. If no matching sheet is found, the sheet will be added to the file.
DELETESHEETS option controls what happens when you are adding data to a file, containing multiple sheets, that is in a format does not support the updating of one page (e.g. older Excel .XLS, Quattro or Open Office files). The settings are:
||always delete the sheets that are not being updated,|
||give a fault if the file contains multiple sheets,|
||in an interactive run, prompt to check whether the sheets should be deleted, or a fault should be given and the file left unchanged.|
In an interactive run the default is
prompt, and in a batch it is
CSVOPTIONS option controls aspects of the output to CSV files:
||suppresses the use of quotes around text,|
||removes any spaces around the columns to give a more compact but less readable file,|
||rounds numerical values to 6 significant figures,|
||writes the numerical values without using scientific notation, and|
||adds spaces to align the columns to make the file more readable.|
HTMLOPTIONS option controls the format of a table written to an HTML file:
||interprets HTML format characters in cells (/& etc) as formats rather than including them as as literal text,|
||suppresses the grid between cells,|
||centres the information within each cell, and|
||right-justifies the information.|
right are selected, the information in each cell will be left
OUTOPTION option allows extra options to be passed to
IMPORT for details.
TABLEFORMAT option controls how tables with two or more classifying factors are stored in spreadsheet files, with settings:
||to put each table onto a separate page, with the last classifying factor displayed across the columns, and|
||to put each table into a single column, so that several tables are displayed on a single page.|
The default is
NONASCII option specifies how to output non-ASCII characters to a text file: either in UTF-8 format (default), or in Unicode.
TIMEOUT option specifies the number of seconds to wait when a file that needs to be deleted or replaced is open in another process; default 10. This allows time for anti-virus and disk synchronization programs to finish their processing.
COLUMNS parameter can specify names for the columns to be saved. The setting is a text with a single line except for a matrix, where it should have a line for each column and also an extra initial line if the matrix has row labels.
PLAINNAMES option lets you suppress the additional type information that Genstat adds by default to the column names (
! for factors,
:D for dates etc). Alternatively, you can set option
NONAMES=yes to suppress the names altogether.
You can set option
EXTRAROW=yes to include the column descriptions in a spreadsheet. (These correspond to the extra information of the variates, factors or texts in the columns.) They will be in the second row of the spreadsheet if the sheet contains the column names, or in the first row if there are no column names.
MISSING option lets you specify the string to use to represent a numerical missing value when writing to a text file (
.CSV) or a spreadsheet file (Excel, Quattro or Open Office). If
MISSING is not set, the string
'*' is used in
.TAB files, while in spreadsheet and
.CSV files the cell is left empty. Missing text values are always output as empty strings.
TITLE option can supply a text containing a title or description of the spreadsheet. This will be saved in a GSH or GWB file, and will be the heading of an HTML file.
You can set option
READONLY=yes to make the entire spreadsheet read-only (so that its contents cannot be changed). Alternatively, you can use the
PROTECT parameter to protect any individual column (by making it read-only). Settings of the
PROTECT parameter override the setting of the
ANALYSIS option can supply a text containing Genstat commands to analyse columns in the spreadsheet. The
ASETUP option can similarly define commands that should be to be run once before the analysis of the columns, and the
ADUMMY option can be used to define the name of the dummy (if any) used in the
The colours displayed in the cells of a spreadsheet can be controlled by using the
BACKGROUND parameters to specify the foreground and background colours of the cells in each column. The setting can contain either colour names or RGB values (see the
PEN directive for details). You can specify a scalar or a text of length one if all the cells in a column have the same colour. You can specify a variate or text with several values to define different a colour for each cell. Finally you can specify a single pointer to a set of variates or texts if the corresponding
DATA setting will need several columns in the spreadsheet. Alternatively, you can specify the background columns for factor columns using the
FACCOLOURS parameter. This should be set to a variate or text with the same number of values as the number of levels of the factor. You can apply the colours defined for background of each cell of a factor to the cell’s complete row by setting the
ROWCOLOURS option to the identifier of the factor. A missing value, empty string or undefined setting for any of these parameters will retain the default colour for the foreground or background.
DECIMALS parameter lets you specify the number of decimal places to use for columns. When saving a Genstat or Excel file the columns will be displayed with that precision, but saved with full precision. However, with a text file (
.CSV), the values will be rounded to that number of decimal places, and precision will be thus be lost. The default for text files, when
DECIMALS is unset, is to save the data with full precision (15 significant figures).
DATA contains a pointer, the corresponding
COLUMNS setting should be a text of the same length as the pointer. The
DECIMALS setting can be either a scalar, or a variate of the same length as the
DATA pointer. Similarly, the
BACKGROUND settings can be either a single variate, scalar or text, or a pointer containing the same number of variates, scalars and/or texts as the length of the
DATA pointer. For example:
EXPORT [OUT='Test.xls'] !P(U,V),X,!P(Y,Z);\
EXPORT replaces the procedure
%DSAVE from earlier editions of Genstat.)
The procedure calls the
FSPREADSHEET procedure to create a temporary GSH file, which is translated to the required file type using the
Any restrictions are ignored. However, if the restrictions on the structures are not consistent, a fault will occur.
CAPTION 'EXPORT example'; STYLE=major FDELETE 'XYData.xlsx','XYData.rda','XYData.csv' VARIATE X; !(1...8) CALC [SEED=5827] Y1,Y2 = 1,2 + X*(1,1.5 - X/3,4)/2 + GRNORMAL(8;0;0.4) FACTOR [LABELS=!T('None','Some')] Group; !((1,2)4) TEXT Unit; VALUES = !T(A,B,C,D,E,F,G,H) TABULATE [CLASS=Group] Y1,Y2; MEANS=M1,M2 "Write data to an Excel file, overwriting an existing file" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Time 1'; METHOD=overwrite] Unit,Group,X,Y1 "Fit a linear regression model to the data and save the fitted values and residuals" MODEL Y1; FITTEDVALUES=Fit; RESIDUALS=Residuals FIT [PRINT=*] Group*X "Add extra columns to the end of the sheet" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Time 1'; METHOD=concatenate] Fit,Residuals "Add a second page to the Excel file, colouring the rows by group" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Time 2'; METHOD=add; ROWCOLOURS=Group] \ Unit,Group,X,Y2; FACCOLOURS=!T('mistyrose','azure'); DECIMALS=2 "Add another page to the Excel file for the combined data" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Time 1 + 2'; METHOD=add] Unit,Group,X,Y1 "Append the second time in the same page and add factor time to index these" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Time 1 + 2'; METHOD=append; GROUPS='Time'] \ Unit,Group,X,Y2 "Add sheet containing tables of means" EXPORT [OUTFILE='XYData.xlsx'; SHEET='Means'; METHOD=add] M1,M2 "Write data to a R file" EXPORT [OUTFILE='XYData.rda'; METHOD=overwrite] Unit,Group,X,Y1,Y2 "Write data to a CSV file" EXPORT [OUTFILE='XYData.csv'; CSV=noquotes,pack,round] Unit,Group,X,Y1,Y2