Notes: Before importing a spreadsheet, ensure that the data form a rectangle where each column is the same length. If the spreadsheet contains empty rows or columns Genstat will remove these automatically.
Column names must start with a letter (A-Z, a-z or %), and can only contain letters, numbers, a percentage symbol % or an underscore. Genstat will automatically modify any column names that include invalid characters.
Dates copied from Excel will become text columns if the date format contains letters or separators (e.g. 21-Mar-2005 or 21/3/2005)
The Clipboard import method lets you import a single spreadsheet or a selected range of cells.
- Open the spreadsheet you want to import. This can be another Genstat spreadsheet or one created with another program such as Excel.
- Place the mouse cursor into the spreadsheet then drag the mouse to highlight all the cells you want to import. Use the Windows shortcut keys Ctrl+C to copy the cells.
If you are copying columns from another Genstat spreadsheet, the column names are not copied – only the data cells will be copied to the clipboard.
- From the menu, select Spread | New | From Clipboard.
- The next dialog allows you to set options for importing the spreadsheet data. You can select options as required (see Options below) or leave all settings at their defaults and click OK.
The spreadsheet opens to display your imported data.
Options
| Sort factor levels | If your data contains factor columns this option will sort the numeric levels (or text labels) into ascending order. |
| Suggest columns to be factors | Genstat will prompt you to convert columns to factors when the column contains x number of repeated values. You can set the number of repeated values in the Tools | Spreadsheet Options Conversions tab. Select Suggest converting columns with <=x unique items to factors and set the number as required. |
| Remove empty rows | Remove any rows that contain no data. |
| Remove empty columns | Remove any columns that contain no data. |
| Data contains variates & factors only | Any column containing text will be converted to either a variate or factor. If the column contains less than 20% text cells, the column will be made into a variate, otherwise the column will be made into a factor. |
| Column descriptions in row |
The cells in the specified row number will be used for column descriptions. The image shows a column called Treatment, which contains a description Chemical Applied. The description is inserted as a second line in the column header, rather than being placed on the next row
|
| Ignore type markers (!#$) in column names | When reading data into Genstat you can append special characters (markers) to the column headers to tell Genstat what data types the columns contain. (The marker ! denotes a factor column, # is a variate and $ denotes a text column.) When you select this option Genstat will ignore the markers and use the contents of the column to decide the column type: columns that contain only numbers will be variates, otherwise they will be designated as text columns. |
| Column names in first row | Specifies where to take the column names from. Yes if all labels – If all cells in the first row contain only text or missing values use those cells for column names. Genstat will generate default column names for any missing values (see option No below for a description of how default names are generated). Yes – Use the cells in the first row for column names. If the clipboard item contains a number, this is prefixed with a ‘_’ to make it a valid Genstat name. No – Genstat will generate default column names such as C1, C2…C10, etc. You can set the characters used to generate the default names by selecting Tools | Spreadsheet Options then click the Columns tab. Change the option Default column names as required |
| Commas | This controls how to treat commas in your clipboard data. Leave – Do not remove or change commas. Change to decimal – Change commas to decimal places. This may be required to convert data that is represented in European numeric formatting where a comma is used for a decimal place. For example, 3,14 will be converted to 3.14. Remove – Remove the commas from the text. This may be required if commas have been used when representing numbers, for example 21,000. |
| Missing value text | This lets you supply your own text to be inserted into empty cells (the default values are an asterisk * for a variate column or a blank cell for factors and texts). For example, you could use N/A to represent a missing value |
| Check columns for date values | When selected, Genstat checks all text columns to see if they contain data in date format. If columns appear to contain data in date format you will be prompted to convert these to dates. The options on the prompt are as follows: Use this response for all subsequent columns – Use the response Yes or No for the other columns in the spreadsheet that look like dates. Yes – Convert the text column to a variate column containing dates. No – Leave the text column as is. |
| Set as active sheet | This sets the new spreadsheet that is created as the active spreadsheet. Setting an active spreadsheet provides a method of avoiding multiple data updates to the server when more than one spreadsheet is open within Genstat. When a spreadsheet is set as the active spreadsheet only data from that spreadsheet is automatically updated to the Genstat server. Another advantage of specifying an active spreadsheet is that the Spread menu options will always be enabled even if your spreadsheet does not have the cursor focus. |
| Add to book | Any open spreadsheets will be displayed in this dropdown list. Select a spreadsheet to append your data to or leave it at the default setting New Book to create a new workbook. |
| Date format | This lets you specify a date format for any columns that are read in as dates (a column will be read as a date if the data type marker :D has been appended to the column name). Clicking this button opens a dialog where you can select a date format from the displayed dropdown list or create a custom date format. |
