Reads data from a foreign file format, and loads it or converts it to a spreadsheet file (D.B. Baird).
Options
PRINT = string token |
What information to print (catalogue); default cata |
|---|---|
OUTTYPE = string token |
Output file type (GEN, GSH, GWB, XLS, XLSX, TXT, CSV, SHEETS); default GWB |
METHOD = string token |
Whether to load data into the Genstat server after creating the file, or whether merely to create the file (create, load); default load |
IMETHOD = string token |
How identifiers are to be specified for the columns (read, supply, none); default supply if COLUMNS is set (and specifies names rather than just types), otherwise read |
ENDSTATEMENT = string token |
Ending statement for a type GEN output file (return, endbreak); default retu |
SPSSMV = string token |
What to do with SPSS missing value codes (ignore, convert); default conv |
MISSING = text |
What labels represent missing values in Excel, Quattro or Lotus files; default '*' |
FORDER = string token |
The order in which to define the labels or levels of a factor read in (sorted, unsorted); default sort |
TEXTCONVERSION = string token |
How to convert text to numbers for the columns (strict, single, common, standard, lax); default stan |
KEEPEMPTY = string tokens |
Whether to retain any empty rows or columns found in the data (rows, columns, none); default none |
NAMEROW = scalar |
The row number within an Excel or Quattro spreadsheet which contains the column names (IMETHOD must be unset or set to read); default, the first row in CELLRANGE |
EMETHOD = string token |
Whether to read column descriptions/extra from Excel, SigmaPlot or Quattro spreadsheets (read, none); default none |
EXTRAROW = scalar |
The row number within an Excel or Quattro spreadsheet which contains the column descriptions (EMETHOD must be set to read); default, the second row in CELLRANGE |
PREFIX = text |
The string with which to prefix numerical column names; default '%' |
TEMPMISSING = string token |
Whether to read in temporarily missing values as missing (yes, no); default no |
INOPTIONS = text |
Optional input file arguments to be passed to the Dataload.dll |
OUTOPTIONS = text |
Optional output file arguments to be passed to the Dataload.dll |
RGBMETHOD = string token |
How to read colour values (combined, separate, matrix); default sepa |
SEPARATORS = text |
Alternative separators to use in text or csv files |
SCOPE = string token |
Whether to create the data locally in a procedure that is using IMPORT, or globally in the whole program (local, global); default loca |
IPREFIX = text |
Prefix to use with unnamed columns, default 'C' |
TRANSPOSE = string token |
Whether to transpose the rows and columns of the input file (yes, no); default no |
UNICODE = string token |
What to do with Unicode characters found e.g. in an Excel XLSX input file (typeset, ascii, remove); default type |
Parameters
FILE = texts |
Input file or URL to be read |
|---|---|
OUTFILE = texts |
Name of the output file to be created; if this is not provided a temporary file will be created, and then deleted if the data is loaded |
SHEETNAME = texts or scalars |
Name of a spreadsheet worksheet or named range, or number of a worksheet within the file; default is the first sheet in the file |
CELLRANGE = texts |
Cell range within a worksheet, giving the top left and bottom right cell in the format XXNN:XXNN where XX = A – IV, NN = 1 – 64384; default * requests all data on the sheet |
COLUMNS = texts |
Names and/or type codes for the columns read (the type of column can be forced by ending the column name, if supplied, with the code ! for a factor, # for a variate, and $ for a text), using a name of '*' will cause a column to be dropped |
ISAVE = pointers |
Saves the identifiers of the columns |
START = texts |
Contents of a cell in a spreadsheet file or a line in a text file from which to start reading |
END = texts |
Contents of a cell in a spreadsheet file or a line in a text file at which to end reading |
ANCILLARY = texts |
Extra information returned by some file formats (currently only population type from QTL location files) |
ROWSELECTION = variates |
Numbers of the rows to import; if unset, all rows are imported |
COLSELECTION = variates or texts |
Numbers or names of the columns to import; if unset, all the columns are imported |
Description
The name of the file, containing the data values to be imported, is specified by the FILE parameter. This can also be an internet URL prefixed with http://, https:// ftp:// or file://. The data source is then downloaded and imported.
Data in the supported file formats are extracted and saved in the specified file format, depending on the extension of OUTFILE. If this is not provided, the type is indicated by the OUTTYPE option, as either GEN (Genstat Command file), GSH (Genstat Spreadsheet), GWB (Genstat Spreadsheet Book), XLS (Excel 5 Spreadsheet), XLSX (Excel 2007 Spreadsheet), TXT (ASCII Text file) or CSV (comma-delimited file); the default is GSH. Setting OUTTYPE=SHEETS reads in the worksheet names in a spreadsheet file (Excel/Quattro/Sigmaplot or SAS Transport) into a text named Worksheets. If PRINT=catalogue, the worksheets names are printed in the output window. The ENDSTATEMENT option specifies the ending statement type for a type GEN output file: either RETURN (the default) or ENDBREAK). You can set ENDSTATEMENT=* if you do not want an ending statement.
If METHOD=load, the resulting file is read in to Genstat data structures. When IMPORT is used within a procedure, the SCOPE option controls whether the structures are created locally in the procedure (default), or globally in the main program.
In spreadsheet files (Excel, Quattro, 123, SigmaPlot), the SHEETNAME and CELLRANGE parameters can be used to read in just a specified section of the data in the file. If CELLRANGE specifies only the starting cell, IMPORT reads all columns the from the given column onwards, and all rows from the given row downwards. For example, CELLRANGE='C8' reads columns C, D… onwards, and rows 8, 9… downwards, until the end of the data in the sheet. The COLUMNS parameter can be used to set the names and types of the structures (see below).
In spreadsheet files, the data that are extracted are labels, numerical values and the results from formulae. A label of * in an otherwise numerical column is taken as a missing value, unless a different missing value marker is specified with the MISSING option. Empty cells are taken as missing values. Empty rows at the start, middle and end of a block are removed. Empty columns are ignored by defaul; you can set the option KEEPEMPTY=rows or KEEPEMPTY=columns to retain empty rows or columns respectively, or KEEPEMPTY=rows,columns to keep both.
The IMETHOD option indicates how identifiers are to be specified for the columns, with the following settings:
read |
assumes that the names are in the first non-empty row of data; |
|---|---|
supply |
assumes that the names are supplied by the COLUMNS parameter, but uses default names if they are not; and |
none |
uses default names. |
If IMETHOD=read, and a column name cell contains a numerical value rather than a label, the column name is set to the numerical value prefixed with a % character. The prefix can be changed using the PREFIX option: a column named '15' is given the name %15 by default but, if PREFIX='X', the name would be X15.
The default for IMETHOD is to take the names from the COLUMNS parameter, if this is set and it contains names. Otherwise IMPORT looks for names in the data file (as with the read setting).
The default column names have the prefix C and an integer number (i.e. C1, C2 etc.), but you can supply your own prefix using the IPREFIX option.
Using the COLUMNS parameter, the type of a column can be forced by providing a !, # or $ character on the end of the text items provided for the column names. A string '*' can be given as a name in COLUMNS, to remove a column from the data read in. If only a single type character is given, only the types of the columns (and not their names) are changed. The extension :D on a column name specifies that the values are to be read as dates. Similarly, when the column names are being read from a spreadsheet, their types can be specified by using ! for a factor, # for a variate, $ for a text and :D for a date.
The option FORDER controls the order in which that the labels or levels of a factors are stored. with the default, FORDER=sorted, the levels are stored in ascending numerical order, and the labels are stored in alphabetical order. Alternatively, if FORDER=unsorted the levels and labels are stored in the order in which they are first met in the column.
The TEXTCONVERSION option controls how labels are converted to numbers in a column marked as a variate:
strict |
only labels that contain numeric data only are converted (e.g. '10' becomes 10; '1O' becomes *) |
|---|---|
single |
a single character substitution is read as a number (o or O become 0; i, I, l or L become 1; s or S become 2; z or Z become 5; comma becomes decimal point) |
common |
multiple substitutions as in single are made (e.g. 'Io' becomes 10; '23X' becomes *) |
standard |
as in common but extra text is ignored at the end of the number (e.g. '23X' becomes 23; 'A2X3' becomes *) |
lax |
any digits are read from the text (e.g. 'A2X3' becomes 23). |
You can set option EMETHOD=read, to read a row of column descriptions/extra from a spreadsheet file. By default, this row is taken as the second row in CELLRANGE. The EXTRAROW option can be used to modify the row form which the description is read. The row number is relative to the start of the cell range, unless a negative row number is provided; the descriptions are then read from the row in the spreadsheet, corresponding to the absolute value of the specified row number. If EXTRAROW=1, the column names are read from the second row.
The START parameter can supply a text to indicate where to start reading within a spreadsheet or text file. In a spreadsheet file (Excel, Quattro, Lotus), the cells from A1 are searched row by row, until a label is found that matches the text. Only cells below and to the right of this cell are then imported. The text could thus be the name of the first variable to be read. Note that the text must not contain spaces or the division symbol (/). Similarly the END parameter can supply a text to indicate where to stop reading a spreadsheet or text file.
The INOPTIONS and OUTOPTIONS options allow extra options to be passed to Dataload.dll. For example: setting INOPTIONS='/k' keeps leading and trailing and doubled blanks in strings, OUTOPTIONS='/u' creates undecorated names in a CSV file (i.e. 'Factor', rather than 'Factor!'), OUTOPTIONS='/c' combines the three columns Red, Green and Blue in a BMP file into a single column RGB, and INOPTIONS='/m' loads the data as a matrix rather than as separate columns.
The RGBMETHOD option controls how to represent colour values from image files (JPG, GIF, TIF or PNG). The default setting, combined, stores an RGB value in a single column in the same form as generated by the RGB function. The separate setting creates three columns containing the red, green and blue values, respectively. Finally, the matrix setting puts the RGB values into a matrix.
The ROWSELECTION and COLSELECTION parameters allow you to import only a subset of the rows or columns, respectively, in the file. They can be set to a variate containing the numbers of the rows or columns. With COLSELECTION, you can also supply a text containing column names. So, for example, to import only rows where the variate X is greater than zero, you could put
ROWSELECTION = WHERE(X.GT.0)
(the WHERE function gives the unit numbers where a logical expression has the value one i.e. true). Note that the variate X must already have been imported into Genstat, but you could import this column on its own using COLSELECTION. If ROWSELECTION (or COLSELECTION) are unset, all the rows (or columns) are imported.
When reading an Excel XLSX file, the UNICODE option controls what happens to Unicode characters that are not supported by the extended ASCII character set. The remove setting removes these from the input. The ascii option converts them to the nearest matching ASCII character. The typeset option converts those that can be represented by Genstat typesetting strings by these strings: for example, α would be converted to ~{alpha}, and √ would be converted to ~{sqrt}. The correspondence between the Greek, ASCII and type-setting commands is shown in the table below. The capitals have the same correspondence. Extended Greek and Latin letters have their accents removed, as there are no type-setting commands for these. Some symbols like ♂ and ♀ are converted to their text equivalent (male and female).
|
α |
a |
~{alpha} |
ι |
I |
~{iota} |
ρ |
r |
~{rho} |
|
β |
b |
~{beta} |
κ |
k |
~{kappa} |
σ |
s |
~{sigma} |
|
γ |
g |
~{gamma} |
λ |
l |
~{lambda} |
τ |
t |
~{tau} |
|
δ |
d |
~{delta} |
μ |
m |
~{mu} |
υ |
u |
~{upsilon} |
|
ε |
e |
~{epsilon} |
ν |
n |
~{nu} |
φ |
f |
~{phi} |
|
ζ |
z |
~{zeta} |
ξ |
c |
~{xi} |
χ |
x |
~{chi} |
|
η |
h |
~{eta} |
ο |
o |
~{omicron} |
ψ |
y |
~{psi} |
|
θ |
q |
~{theta} |
π |
p |
~{pi} |
ω |
w |
~{omega} |
(Note: IMPORT replaces the procedure DATALOAD from earlier editions of Genstat.)
Options: PRINT, OUTTYPE, METHOD, IMETHOD, ENDSTATEMENT, SPSSMV, MISSING, FORDER, TEXTCONVERSION, KEEPEMPTY, NAMEROW, EMETHOD, EXTRAROW, PREFIX, TEMPMISSING, INOPTIONS, OUTOPTIONS, RGBMETHOD, SEPARATORS, SCOPE, IPREFIX, TRANSPOSE, UNICODE.
Parameters: FILE, OUTFILE, SHEETNAME, CELLRANGE, COLUMNS, ISAVE, START, END, ANCILLARY ROWSELECTION, COLSELECTION.
Method
The request is passed to the DATALOAD.DLL library which reads the foreign file and returns any valid data found in a temporary GEN or GSH file. The following file types are supported: Excel 2-5, 95, 97, 2000, XP, 2003, 2007-2013, Open Office, Lotus WK1, Quattro (WQ1, WB*, QPW), dBase 2-5, Paradox 3-9, Genstat GSH and GWB, SAS PC 6.03-12, 7-9, SAS Transport, SAS JMP, Minitab 8-17, Statistica 5 and 6, Systat, MStat, Instat, Epi-Info, SPSS/Win, Gauss Data/Matrix (PC/Win/Unix), MatLab, S+ (PC/Unix), Stata 4-8, StatGraphics, R data frames, Weka Attribute files, SigmaPlot 7-9, OSIRIS, Limdep, Comma delimited text files (*.CSV), Cornell Ecology format, MapQTL trait files (.QUA), ArcView/Info Shapefiles, MapInfo Exchange files, Windows Bitmap (*.BMP), Windows Sound (*.WAV), NMR Binary files and image files (JPG, GIF, TIF, PNG). The file type is worked out from the file contents, so the usual extension need not be used with the exception of the following file types which do not contain a unique signature: Epi-Info (.REC), S+ (.SDD) and Paradox (.DB). Any files not containing a unique file signature, but ending in these extensions will be classified as above. Any other file extensions will attempted to be read as a comma, space or tab delimited text file.
There is a known problem that using the OUTTYPE=GEN inside a FOR loop (or another other procedure) ties up input channels until exiting the FOR loop. Thus it may exhaust the available input channels. Either use the OUTTYPE=GSH or set LOAD=no and write code to input the files created outside the loop (you will need to provide an output file name to do this).
Action with RESTRICT
Restrictions are not applicable to any of the parameters.
See also
Directive: SPLOAD.
Procedure: EXPORT.
Commands for: Input and output.
Example
" Example 1:6.5.1 " " Get location of image file for use by IMPORT " SCALAR _chan OPEN '%GENDIR%/Examples/GuidePart1/CapeWagtail.jpg'; _chan; FILE=input ENQUIRE CHANNEL=_chan; FILE=in; NAME=file CLOSE _chan; FILE=input IMPORT [PRINT=*; RGBMETHOD=matrix] file; COLUMNS='RGB' " resize the window to match the dimensions of the bit map " CALCULATE Nr = NROWS(RGB) & Nc = NCOLUMNS(RGB) IF Nr < Nc FRAME 3; YUPPER=Nr/Nc ELSE FRAME 3; XUPPER=Nc/Nr ENDIF DBITMAP [WINDOW=3] RGB