Select menu: Spread | New | From Clipboard
This dialog will also appear when you import a .csv file.
This dialog provides additional options when a spreadsheet is created either from the clipboard or from opening a .csv file.
- From the menu select Spread | New | From Clipboard.
The display of this dialog can be controlled for when data are copied from the clipboard by selecting the Tools | Spreadsheet Options | Sheets tab and selecting the option Show new from clipboard options. When data are read from the clipboard or a .csv file, if the first row contains text values these will be used as column names in the Genstat spreadsheet, otherwise default column names will be generated (this behaviour can be modified by the options outlined below).
Column names ending in an exclamation mark (!) will have this character removed and the column will be loaded as a factor. Likewise names ending with a dollar ($) or a hash (#) will be loaded as text or variates respectively. Characters in column names that would be invalid as a Genstat identifier name will be converted to underscore (_).
Sort factor levels
When selected, columns loaded as factors will have their levels (or labels for a text column) sorted into ascending order.
Suggest columns to be factors
Prompts to convert columns to factors where the columns have repeated values, and fewer unique values than the specified number given in the Suggest converting columns with <= N unique items option within the Tools | Spreadsheet Options | Conversions tab.
Remove empty rows
Remove any rows from the spreadsheet that contain no data.
Remove empty columns
When selected any columns in the spreadsheet that contain no data will be removed.
Data contains variates & factors only
Any column containing text will be converted to either a variate or factor. If the number of labels within the spreadsheet column is less than 20%, the column will be made into a variate, otherwise the column will be made into a factor. When reading the column as a variate, common slips such as entering a letter O for the number 0 (or I for 1) will be fixed.
Column descriptions in row
When selected, the cells in the specified row number will be used for column descriptions (the EXTRA keyword for Genstat structures).
Ignore type markers (!#$) in column names
When selected, the markers (! for a factor, # for a variate and $ for a text) will be ignored and the contents of the column used to decide its column type (either a variate for numbers only, otherwise a text), otherwise if one of the type markers is present in the column name, then the column will be read as the specified type.
Column names in first row
Where to take the columns names from.
| Yes if all labels | If all the cells in the first row contain only labels or are missing, the column name will be from the first row. |
| 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 | Do not use the first row for column names and generate default names. |
Commas
This controls how to treat commas in the text on the clipboard.
| Leave | Interpret the text with commas as normal. |
| Change to decimal | Change the commas to decimal places. This may be required to convert data if they are 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
Lets you supply an alternative text to ‘*’ for missing values, so for example ‘NA’ could be used 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 are prompted to convert these to dates. The default setting for the check for dates values option can be set on the Tools | Spreadsheet Options | Conversions tab.
Set as active sheet
This sets the new spreadsheet that is created as the active spreadsheet.
Add to book
This provides a list of books that the new spreadsheet can be added to. The default book that spreadsheets are added to can be specified using the Tools | Spreadsheet Options | Books menu.
Date format
This specifies the default date format and base date for columns read in as dates, through being marked with a :D on the end of the column name. This opens the Date Format dialog.