Import or export data

In addition to the Genstat spreadsheet (GSH) and book (GWB) formats, data can be imported or exported to files from a wide range of external data formats.

Importing data

Data can be imported into Genstat in several different ways. To import data into a Genstat spreadsheet or book select File | Open. This will prompt you with a standard dialog where you can browse for the file to be opened. Note that if a file is opened that does not include a file extension then Genstat will try to determine the appropriate file format from the file content. When the file is opened the data will appear in a Vector spreadsheet containing variate, text and factor columns. You can then manipulate data in the usual way i.e. convert column types using the right-click menu etc.

If an external data file includes the same number of rows as a currently open spreadsheet within Genstat, then the file can be imported as additional columns within this spreadsheet. To do this select the Spread | Add | Data file item on the menu bar and then browse for the file to be imported.

Data can imported directly into Genstat without displaying it within a spreadsheet, To do this select Data | Load | Data File then browse for the file to be imported.

To import data from a text file, except CSV files, the Load ASCII File menu should be used. This menu can be used to open data that is in column format within a text file directly into Genstat with the option of displaying the data in a spreadsheet. To do this select Data | Load | ASCII file and enter the relevant options on the menu for loading the data.

Data can be imported from databases using ODBC. To import data using ODBC Genstat provides a wizard that takes you through a step by step process that includes the selection of the table and construction of a simple SQL query. The ODBC Data Query wizard can initiated to import data into a spreadsheet by selecting Spread | New | ODBC Data Query. Alternatively, the wizard can be invoked to load data directly into Genstat by selecting Data | Load | ODBC Data Query.

When data are imported that include column names Genstat validates these. If the column names include any illegal characters these will be changed to ensure that the name is a valid Genstat identifier name. The first character of a Genstat identifier name must be a letter (or ‘_’,’%’ character) and any others can be either letters, digits or ‘_’,’%’ characters.

Within the command language data can be imported directly into Genstat using the IMPORT procedure and data can be retrieved from databases using the DBIMPORT procedure.

Exporting data

To export data from a spreadsheet or book select either File | Save or File | Save As. The Save option will save the data using its original format, overwriting the previous contents of the file. If a spreadsheet contains new data, or you select the Save As option, you can choose the file format to save the data to. Data that are exported to a spreadsheet application such as Microsoft Excel or Quattro will included the Genstat column names and data values.

Data can be exported to databases using ODBC. Genstat can create a new table in the database or you can merge or insert values into existing tables in the database. To export data using ODBC Genstat provides several menus that can be accessed on the Spread | Export submenu.

Within the command language data can be exported from Genstat using the EXPORT procedure and data can be exported to databases using the DBEXPORT procedure.

Hints and tips

When data are imported from spreadsheet applications such as Microsoft Excel and Quattro you can append characters to the column names to ensure columns are opened as a particular data type. For example, appending the ‘!’ character to a column name will create the column as a factor, similarly, appending the ‘#’ character will form a variate and the ‘$’ character will produce a text column. When importing from Excel files the ordering of factor levels and labels can be supplied in a comment along with a column description. More information on this can be found in Using Genstat with Excel.

When data are exported to a spreadsheet application any factor columns will have the ‘!’ character appended to the column name to ensure that the next time the spreadsheet is opened in Genstat it will be opened as a factor.

As well as importing and exporting data to files the Clipboard can be used to transfer data between Genstat and other applications. To help import data using the Clipboard, Genstat provides a menu that contains options for controlling how data are transferred from the Clipboard directly into a Genstat spreadsheet. To invoke this menu select Spread | New | From Clipboard.

When importing data from other formats such as INSTAT, SAS v7-9 and Matlab 5 you will be prompted with dialogs where you can specify additional options as to how the data are to be imported.

Opening .XLS files produced by Genstat into Excel 2010

Excel 2010 or later always generates a warning on opening a .XLS file (the Excel 95-2003 file format) that was not saved by Excel. You will get a warning saying: 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 using Excel 2010 or later, it is always best to use the .XLSX file format, and then this will not happen.

See also

• How to load data from files
• How to add data to a spreadsheet
• File New – Spreadsheet
• Creating a new spreadsheet using data on the Clipboard
• Using Genstat with Excel
• Supported data formats