|Import Data from Excel|
One disadvantage of importing the data using the clipboard, is that the precision of the any numbers may be lost if they have been displayed to a reduced number of decimal places within Excel. For example, if the number 3.14159 is displayed to 1 decimal place within Excel (3.1) then this will be copied into GenStat as 3.1. Also any dates will be copied as text columns if the date format contains letters or separators (e.g. 21-Mar-2005 or 21/3/2005).
When data are opened directly from an Excel file you can include comments and append codes to column names within Excel to specify how the data is to be imported. The following codes can be appended to column names to format columns:
|!||Imports column as a factor|
|$||Imports column as a text|
|#||Imports column as a variate|
|:0, :1, :2 ... :9||The fixed number of decimal places to display values within column|
|:E||The column will be displayed using scientific format.|
|:D||The column will be displayed using the default GenStat date format.|
|:T||The column will be displayed using a time format.|
You can supply factor levels or labels and their order by supplying them within a comment to the column name cell in Excel. Within the comment the factor labels or levels must be contained within the characters !( ) or !T( ) on a new line in the comment. For example, to supply the levels 100, 90, 50 and 10, enter the following format !(100,90,50,10). To supply labels Control, A and B you can use either !T(Control,A,B) or !t('Control','A','B'). The order of the items in the comment will define the order of the levels or labels in the factor. If a column in Excel just contains ordinal values (i.e. 1...n), the comment can still be used to assign labels or levels to these groups. In this case the first item in the comment will define the level or label for group 1 in the factor, and so on.
The ! marker can be applied in conjunction with the Date :D or Time :T markers, so that !:D would indicate a factor with levels displayed as dates.
Factor labels should be supplied in the usual GenStat form, starting with !T( with the entries separated by commas, and terminated with a closing bracket ')'. If any of the labels contain one or more of the characters '!', ',' or ')' - exclamation mark, comma or left bracket, then the label must be contained in single quotes. Note that if a label contains a single quote this must be doubled up. For example, the string (Pierre David's) should be supplied as '(Pierre David''s)'. If labels do not include any of the reserved characters then you do not need to use single quote's. For example, the following could be used !T(A,B,C,'(D-E)'). Any numbers within the list will be interpreted as a string provided the !T is given at the start of the label list.
Factor levels should start with !( where the numbers are separated by commas and terminated with a closing bracket ). If any of the entries in the list is not a valid number, then the list will be interpreted as the labels of the factor, equivalent to starting the list with !T(.
Note the type marker ! is redundant if the labels or levels are given.
The column extra description can be given as a plain string of text. This should not start with one of the reserved characters '!', '$', '#' or ':'.
You can combine the factor label/level specification and an extra description into a single comment.
These must be specified in the following order:r: factor levels or labels,
followed by a new line (use Shift+Enter to enter this into the comment in Excel), followed
by the column type/display symbols, followed by the column description.
The following gives an example of a factor being given labels, but with the levels in date format
with a description:
:D Seasonal Summary
If factor values are supplied as ordinals (1,2,3...), then levels or labels can be used to label these within GenStat. For example, a column called Sex could have the levels (1,2) and labels !T(Male,Female) applied to it. Also, if the data for the factor are specified as levels e.g. (10,20,40,100), then the comment can be used to apply labels to the levels, so a comment of !T('Half','Standard','Double','High') could give these labels to the 4 groups.
If the factor has levels or labels in the column data, the cell comment can be used to specify the order that these given in the factor, so that a comment of !T(Nil,Wild,AR1,AR37) can be used to change the label ordering from the default alphabetical ordering of AR1,AR37,Nil,Wild. Similarly if levels are provided with the column, the a redorering such as!(10,5,2,1,0.5) could be used to specify the levels should be stored in decreasing order rather than ascending order as by default.
An example of an Excel cell comment is shown below.
and another below shows two comments that have been entered into Excel
(using the Comment item from the Insert menu within Excel).
More information on this can be found in Using GenStat with Excel.
The options for controlling how the data are imported in are much more limited using a DDE link as opposed to opening the file directly or copying using the clipboard. However, if a DDE link is saved to a GenStat DDE link file (*.GDE), then the latest version of the data will always be read, avoiding problems with maintaining two versions of the data.