Saves data structures in Genstat, Excel, R, Quattro, dBase, SPlus, Gauss, MatLab, SAS, Instat, Image or text files (D.B. Baird).
Options
PRINT = string token |
What to print (summary); default summ |
|---|---|
OUTFILE = text |
Data file to be written |
METHOD = string token |
Action to take if the file already exists (add, append, concatenate, merge, overwrite, prompt, fail, replace); default prompt in interactive mode, fail in batch mode |
PLAINNAMES = string token |
Whether to leave the column names in the file in plain form rather than decorating them with the column type information i.e. ! for factors, :D for dates etc (yes, no) default no |
SHEETNAME = text |
Name of new sheet to be added to an existing Excel file |
NONAMES = string token |
Whether to suppress column names in output to spreadsheet or text file (yes, no); default no |
TITLE = text |
Description for spreadsheet |
READONLY = string token |
Whether to define the complete sheet as read only (yes, no); default no |
ANALYSIS = text |
Genstat commands to analyse columns in the spreadsheet |
ASETUP = text |
Genstat commands to be run once before the analysis of any columns in the spreadsheet |
ADUMMY = text |
The name of the dummy (if any) used the ANALYSIS commands |
CSVOPTIONS = string tokens |
Options for CSV files (noquotes, pack, round, fixed, align); default pack |
HTMLOPTIONS = string token |
Options for HTML files (allowformats, nogrid, centre, rightjustify); default * i.e. none |
COLMATCH = string token |
How to match columns when appending (name, position); default posi |
GROUPS = factor or text |
Identifier for the factor, or text containing the name of the factor, to identify appended sections in the output file |
GLABEL = texts |
Labels for the GROUPS factor for the current appended section, and also for the original section if no previous sections have been appended |
MATCH = texts, variates or pointers |
Up to four DATA variables to use as keys when METHOD=merge; default * uses the first DATA variable |
WITH = texts, variates or pointers |
Columns in the file to use as keys when METHOD=merge; default * uses as many columns of the initial columns in the file as are needed to give a column for each MATCH column |
UPDATE = string token |
Whether to use columns with matching names to replace existing columns when concatenating or merging (yes, no); default no changes the names of columns with the same name as existing columns so that they become unique |
OUTOPTIONS = text |
Optional output file arguments to be passed to the Dataload.dll |
ROWCOLOURS = factor |
The factor to be used for colouring the rows (the factor must have colours defined by the FACCOLOURS parameter) |
TABLEFORMAT = string token |
The format to use when displaying tables with two or more classifying factors (page, column); default page |
MISSING = text |
String to represent a numerical missing value when writing to a text file (.TXT, .TAB or .CSV) or a spreadsheet file (Excel, Quattro or Open Office); default is to use '*' in .TXT or .TAB files, and leave cells with missing values empty in .CSV or spreadsheet files |
DELETESHEETS = string token |
Whether to delete sheets if you are overwriting a multiple paged file with a single page (always, never, prompt); default prom when running interactively and neve when running in batch |
NONASCII = string token |
Specifies how to output non-ASCII characters to text files (
utf8, unicode); default utf8 |
†EXTRAROW = string token |
Whether to include the column descriptions in spreadsheet files (
yes, no); default no |
TIMEOUT = scalar |
Number of seconds to wait when a file is open in another process; default 10
|
QTL = text |
Private option for QTL files, not for general use
|
Parameters
DATA = identifiers |
The data structures to be written to the file, these must be compatible (i.e. of the same length) |
|---|---|
COLUMNS = texts |
Names for the columns to be saved |
PROTECT = scalars |
Whether the column is to be defined as read only when option READONLY=no (yes, no); default no |
FACCOLOURS = variates, texts or pointers |
Specifies background colours for factor columns |
FOREGROUND = variates, texts, scalars or pointer |
Specifies foreground colours for columns |
BACKGROUND = variates, texts, scalars or pointer |
Specifies background colours for columns |
DECIMALS = variates or scalars |
Specifies numbers of decimals for the columns |
Description
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 .BMP, .EMF, .GIF, .JPG, .TIF, .PNG or .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.
The 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.
The METHOD option controls how EXPORT behaves when asked to overwrite an existing file. The available settings are add, append, concatenate, merge, overwrite, prompt, fail and 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']\
MaxLane,MaxLoc,WarpLoc,MaxAmp
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Parameters']\
ParLane,ParLoc,ParSig,ParAmp
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Components']\
PeakLane,PeakLoc,PeakSig,PeakAmp,PeakHt
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Lanes']\
eX,eLane[]
EXPORT [OUTFILE=OutFile; METHOD=add; SHEET='Warping'] eX,W[]
The 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;\
GLABEL=!T('Contaminated','Standard')] X2,Y2,Z2
If 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 METHOD=merge. The DATA variables are now merged with the original rows using up to four key columns specified by the MATCH and 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.
If 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.
The 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 |
always delete the sheets that are not being updated, |
|---|---|
never |
give a fault if the file contains multiple sheets, |
prompt |
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 never.
The CSVOPTIONS option controls aspects of the output to CSV files:
noquotes |
suppresses the use of quotes around text, |
|---|---|
pack |
removes any spaces around the columns to give a more compact but less readable file, |
round |
rounds numerical values to 6 significant figures, |
fixed |
writes the numerical values without using scientific notation, and |
align |
adds spaces to align the columns to make the file more readable. |
The HTMLOPTIONS option controls the format of a table written to an HTML file:
allowformats |
interprets HTML format characters in cells (/& etc) as formats rather than including them as as literal text, |
|---|---|
nogrid |
suppresses the grid between cells, |
centre |
centres the information within each cell, and |
right |
right-justifies the information. |
If neither centre or right are selected, the information in each cell will be left
justified.
The OUTOPTION option allows extra options to be passed to Dataload.dll. See IMPORT for details.
The TABLEFORMAT option controls how tables with two or more classifying factors are stored in spreadsheet files, with settings:
page |
to put each table onto a separate page, with the last classifying factor displayed across the columns, and |
|---|---|
column |
to put each table into a single column, so that several tables are displayed on a single page. |
The default is TABLEFORMAT=page.
The NONASCII option specifies how to output non-ASCII characters to a text file: either in UTF-8 format (default), or in Unicode.
The 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.
The 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.
The 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.
The MISSING option lets you specify the string to use to represent a numerical missing value when writing to a text file (.TXT, .TAB or .CSV) or a spreadsheet file (Excel, Quattro or Open Office). If MISSING is not set, the string '*' is used in .TXT and .TAB files, while in spreadsheet and .CSV files the cell is left empty. Missing text values are always output as empty strings.
The 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 READONLY option.
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 ANALYSIS commands.
The colours displayed in the cells of a spreadsheet can be controlled by using the FOREGROUND and 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.
The 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 (.TXT, .TAB or .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).
When 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 FACCOLOURS, FOREGROUND and 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);\
COLUMNS=!T(A,B),'C',!T(D,E); DECIMALS=!(2,2),3,!(4,5)
(Note: EXPORT replaces the procedure %DSAVE from earlier editions of Genstat.)
Options: PRINT, OUTFILE, METHOD, PLAINNAMES, SHEETNAME, NONAMES, TITLE, READONLY, ANALYSIS, ASETUP, ADUMMY, CSVOPTIONS, HTMLOPTIONS, COLMATCH, GROUPS, GLABEL, MATCH, WITH, UPDATE, OUTOPTIONS, ROWCOLOURS, TABLEFORMAT, MISSING, DELETESHEETS, NONASCII, EXTRAROW, TIMEOUT, QTL.
Parameters: DATA, COLUMNS, PROTECT, FACCOLOURS, FOREGROUND, BACKGROUND, DECIMALS.
Method
The procedure calls the FSPREADSHEET procedure to create a temporary GSH file, which is translated to the required file type using the DATALOAD.DLL library.
Action with RESTRICT
Any restrictions are ignored. However, if the restrictions on the structures are not consistent, a fault will occur.
See also
Procedures: FSPREADSHEET, IMPORT.
Commands for: Input and output.
Example
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