[vsni.co.uk] Contact us
Author Message
Post new topic

  GenStat  ~  spreadsheet pointers

Linc Schmitt
Posted: Tue Aug 14, 2018 3:11 am Reply with quote
Joined: 24 Jun 2011 Posts: 536
Hi David, thanks- curiosity will kill this cat one day. Cheers, linc

From: GENSTAT-Request <GENSTAT@jiscmail.ac.uk> On Behalf Of David Baird
Sent: Tuesday, 14 August 2018 10:05 AM
To: GENSTAT@JISCMAIL.AC.UK
Subject: Re: spreadsheet pointers



Dear Linc,

These are there mainly for some odd options that are for trouble-shooting/debugging
and a few features not supported by the procedure, and sometimes so a user
can use a new version of the DataLoad.dll with an older version of Genstat.
This gives me the flexibility of adding options to fix problems users find
without having to update the procedure library.
The full list of INOPTIONS and OUTOPTIONS are below.

Regards, David.
______________________________________________
Dr David Baird Statistical Consultant and GenStat Developer
VSN (NZ) Limited (David@VSN.CO.NZ (David@VSN.CO.NZ))
8 Mariposa Crescent, Aidanfield, Christchurch 8025, New Zealand
Ph +64 3 3350588 Cell +64 21 1160803


From: GENSTAT-Request <GENSTAT@jiscmail.ac.uk (GENSTAT@jiscmail.ac.uk)> On Behalf Of Linc Schmitt
Sent: 14 August 2018 1:10 PM
To: GENSTAT@JISCMAIL.AC.UK (GENSTAT@JISCMAIL.AC.UK)
Subject: Re: spreadsheet pointers



Hi David,

Thanks for this – and especially pointing out the different behaviour according to how the spreadsheet is accessed. Apologies for the last word in my original enquiry, which must have been cryptic to any reader - “replaced” should have been “reloaded”!

I noticed, when trying to solve this problem, the OUTOPTIONS option of EXPORT and IMPORT. Is there more documentation on these other than what is given in the description of IMPORT?

Regards,
Linc

DataLoad.dll:

Supported File Types: Excel 2-5,95,97,2000,XP, Lotus WK1, Quattro WQ1, WB*,
QPW dBase 2-5, Paradox 3-9, Genstat GSH, SAS PC 6.03-12, Minitab 8-13, Minitab
Portable, Systat, MStat, Instat, Epi-Info, SPSS/Win/PC+/Portable,
Gauss Data/Matrix (PC/Win/Unix), MatLab, S+ (PC/Unix/Text), Stata 4-7,
SAS Transport, Logbook Excel format, Statistica 5 & 6, Visualist files
(binary only), SigmaPlot, ArcView/Info Shapefiles, MapInfo Interchange (MIF),
Weka ARFF files (Standard & Sparse), Visualist (VTB), CSV/Tab/space delimited
text files, Windows BMP bit mapped graphics files and Windows WAV sound files,
NMR Binary Phase file (.bin) and Affymetrix CEL,CHP & CDF files, and Windows
standard image files (JPG, GIF, TIFF, PNG, ICO, WMF, EMF).

The file type is worked out from the 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+ Text (.SDD) and Paradox (.DB).
Any files not containing a unique file signature, but ending in these
extensions classified as above.

When writing an image file (BMP, JPG, GIF, TIFF, PNG), the input must either
be a matrix of 24 bit RGB values or a 3 column vector sheet of X,Y,RGB values
or a 5 column vector sheet of X,Y,Red,Green,Blue values. DataLoad can be used
to convert between image file formats e.g. Dataload Image.BMP Image.JPG

DataLoad can save the output file in various formats. The format is taken
from the output file extension:

.GSH - a Genstat spreadsheet file.
.GWB - a Genstat workbook file.
.GEN - a Genstat command file.
.XLS - an Excel 95 spreadsheet file.
.WQ1 - a Quattro spreadsheet file.
.DBF - a dBase III worksheet file.
.WOR - an Instat+ worksheet file.
.FMT - a Gauss 86 matrix file.
.MAT - a Matlab 4 matrix file.
.SDD - a SPlus data frame file.
.TPT - a SAS Transport file.
.TXT - a space delimited text file.
.CSV - a comma delimited text file.
.TAB - a tab delimited text file.
.RTF - a rich text format file (Word).
.HTM - a hypertext markup format file (Internet viewable).
.HTML - a hypertext markup format file (Internet viewable).
.ARFF - a Weka Attribute-Relation Format file.
.VTB - a Visualist Tablet file.
.RDA - a R Data Version 2 file (Binary version default - use /t for ASCII output)
.BMP - a Windows Bitmap image file
.JPG - a JPG image file
.GIF - a GIF image file
.TIF - a TIFF image file
.TIFF - a TIFF image file
.PNG - a PNG image file
.ODS - a Open Office document
.XML - Triple S survey document (needs accompanying .asc file).

Options must each be preceded with a / (i.e. /iv should be /i/v) and must
not be separated from the filename by spaces (i.e. file.xls/w not file.xls /w).

INOPTIONS:

/rewrite Rewrite CSV file

/a Drop unneeded columns from Affymetrix CEL and CDF files
and read masked and outliers as missing in CEL files.

/b Keep blanks in strings. Default behaviour is to strip
surrounding blanks and double blanks from strings.

/c=s:f Columns to keep from read in data (starting number and
ending number. E.g. /c=2:9 will keep columns 2 to 9.

/csv=n Number of lines per record in a CSV file. E.g. /csv=2
will read two lines per record from the CSV file.

/d An option of /d can be added to Excel/Quattro/SigmaPlot
filenames to read in a row of column descriptions in the
second row of the data range. A row number can be added
to the /d option (i.e. /d=n, and if this is given, the row
n in the data range will be taken. If the row number is
prefixed with a $ (i.e. /d=$4) then that absolute row number
in the sheet will be used. Also if a rownumber of 1 is given,
the description will be the first row in the data range and
the column names will be taken from the second row.

/db Run in debug mode.

/e= Ending text in Excel/Text file.

/f An option of /f can be added to Instat filenames to not output
factors with more than 223 levels. Otherwise the if the factor
has labels, levels greater than 233 will be set to missing, or
else the factor will be written as a variate.

/g= Genstat version information passed as an integer
(=10 x version, so 17.1 = 171).

/h= An option of /h can be added to filenames to specify the up to
8 letter string used to prefix numerical column names. The
default string is %, so that a column with name '25' will be
named '%25'. If /h=NUM, then column would be named 'NUM25'.

/i An option of /i can be added to SPSS/Win filenames to ignore
SPSS missing value codes.

/k An option of /k can be added to keep empty columns in the data.
The default behaviour is to drop empty columns from the input file.

/l An option of /l on an Excel file specifies that the Logbook
conventions should be used in reading the data. The column_row
range is found and used to extract the data. If multiple
column_row ranges are contained in the spreadsheet, the data
can be extracted from subsequent ranges by adding the range
number, i.e. /l=<n>, eg /l=3 for the third column_row range.

/m= An option of /m can be added to filenames to specify the three
Instat missing value codes, which are supplied in a comma
delimited list. A asterisk can be used for the generic missing
value indicator. E.g. /m=*,-99,-9999 specifies the three
Instat missing values will be set as -1e309, -99, -999 if
a Instat work sheet is saved, and if an Instat worksheet is
being read, that the three Instat missing values are converted
to these values

/n No column name row

/o= Default column prefix name for unnamed columns

/p= For .GEN output a /p=<Pointer Name> can be added to create
a pointer with the given name pointing to all columns read
in. /v and /p= are mutually exclusive, but /i can be used
with either.

/r Retain SPSS empty factor groups

/s= Starting column name in Excel/Text file.

/t= Strictness of text conversions to numerical values:
1 = Strict (label must be an exact representation of a number)
2 = Single (a single letter commonly entered as a number will
be converted to a digit (o,O = 0, i,I,l,L = 1,
z,Z = 2 s,S = 5, , = .)
3 = Common (any of the letters commonly entered as a number will
be converted to a digit (as above)
4 = Standard a numerical value, after making common substitutions
will be read from the label up to the first non numerical character.
5 = Lax - Any numerical values will be read from the string
so that 'A1B2' would be read as 12.

A file TxtToNum.ERR will be produced that list all text to numerical
conversions made.

/u For an Excel or Quattro file, the option /u produces a unsorted
factor levels or labels. The factor labels/levels are put into
the order that they are first met in the data.

/ua, /ur
For an Excel XLSX file, the option /ua converts unicode to
ASCII characters and /un removes any unicode characters.
The default is to translate these to Genstat typsetting symbols.

/v An option of /v can be added to take variable names from a
.VAR file, i.e. for test.xls, a file test.var will contain the
variable names, one per line. The column type can be forced
by providing a single character code (!,#,$ for factor,
variate and text respectively) at the end of the supplied name.
If this file does not exist it will be created.

/w For an Excel or Quattro file, the option /w produces a print out
of the sheet names and named ranges and stops, producing no output
file. When this option is set, the second output file name is
used for storing the list of sheets and named ranges if it
ends with the extension ".OUT".

/x= An option of /x=string can be added to interpret the string as
as missing value in Excel. The default string is *. For example
/x=M will interpret a label of M in a cell as a missing value.

/y Use the first row as column names even if numerical.

/y=n Use the first specified absolute row <n> as column names even
if it contains a numerical cell.

/z= Separators for Txt/CSV file. If / is to be used as a separator
use double quotes "" around the separators. e.g. s=~ or s = "/|"

Outputfile - name of file to write data to - extension determines file type:
.GSH - Genstat spreadsheet file loaded with SPLOAD in a batch
file or File>Open in the Windows interface.
.GWB - Genstat workbook file loaded with SPLOAD in a batch
file or File>Open in the Windows interface.
.GEN - Genstat command file loaded with INPUT.
.FMT - Gauss Matrix File (no text columns output).
.XLS - Excel 5 Spreadsheet file or Logbook file.
.WQ1 - Quattro Spreadsheet file.
.DBF - dBase 3 database file.
.MAT - Matlab matrix file.
.WOR - Instat+ worksheet.
.SDD - SPlus transport file.
.TPT - SAS transport file.
.TXT - ASCII Text file (space delimited).
.CSV - ASCII CSV comma delimited text file.
.TAB - ASCII tab delimited text file.
.RTF - a rich text format file (Word).
.HTM - a hypertext markup format file (Internet viewable).
.HTML - a hypertext markup format file (Internet viewable).
.ARFF - a Weka Attribute-Relation Format file.
.VTB - a Visualist Tablet file.
.BMP - a Windows Bitmap image file
.JPG - a JPG image file
.GIF - a GIF image file
.TIF - a TIFF image file
.TIFF - a TIFF image file
.PNG - a PNG image file
.ODS - a Open Office document

OUTOPTIONS:

/* For a GEN output file, the option /* stops the standard RETURN
statement being added at the end of the file.

/a For a Excel (*.XLS) output file, the option /a adds the data
as a new sheet to the Excel file it already exists. If the
Excel file does not exist. The /a option can have an Excel sheet
name added after an equals sign - /a=Data. Enclose the sheet name
with double quotes if in contains spaces - /a="My Data".
The maximum length of the sheet name that will be used is 12
characters.

For a Genstat Workbook (GWB), this option adds the data as a new
sheet at the end of the workbook. The page name for the sheet
can be set with /a=Name or a="Page Name", with a maximum length
of 16 characters.

/b The option /b produces an ENDBREAK statement at the end of a GEN
output file rather than the standard RETURN statement.
Note filenames containing the text G42D are reserved for temporary
file type in conjunction with the Genstat procedure DATALOAD.

/c The option /c produces combines colours from an Image file into
a single RGB value (red + 256*green + 256*256*blue) or
stereo channels in a WAV sound file into a average mono channel.

/d Output numerical values to text at full precision

/e An option of /e can be added to keep empty rows in the
resulting output file.

/f An option of /f can be added to keep fixed numerical format
rather than scientific format (i.e. 2700000 rather than 2.7e+6)
in resulting output file.

/g Suppress grid in HTML & RTF files

/h Allow formatting strings in HTML files (special characters
Quote:
< & will not be treated as literals)
or pack CSV file (no spaces).

/jc Justify RTF/HTML Table cells centred (left is default)
/jl Justify RTF/HTML Table cells left
/jr Justify RTF/HTML Table cells right

/l= The number of layout factors in the logbook format.
Eg /l=3 specifies the first two factors in the data are layout
factors. Subsequent factors will be marked as treatments.

/m Read in columns as a matrix rather than vectors.

/n No column name row output to text/CSV files.

/o In a Genstat GSH/GWB file, temporarily missing values are output
as missing.

/p The option /p is reserved for use by the Genstat procedure.

/q No data summaries produced in running *.GEN file

/s An option of /s can be added to produce suffixed identifiers
i.e. C[1] C[2] etc where columns are not named.

/t An option of /t can be added to R data output file (.RDA)
to write the file in ASCII Text mode rather than binary mode
(the default).

/u Don't decorate the column names with ! or :D etc

/v= An option of /v=1 or /v=2 or /v=3 can be added to define
the version of GSH spreadsheet file produced. (Default version 3).

/x This option can also be used to set the Excel or Genstat sheetname
as with /a, but when the sheet is not being added to the output
file.

/- Don't quote text in CSV file.

/@ Append input file to output file (or just create output if it
doesn't exist). Add =n (ie /@=n) to append columns matching names
rather than positions. You cannot append to a multi-page GWB file.
To add a factor indexing the append add ,Factor name and to
use labels apart from the default 1,2 add ,'New Label','Old Label',
e.g, /@,File or /@,File,'Added' or /@,File,'Added','Base'. The
Old Label is only applied on the first append when the factor is
added. If the factor already exists, it will just have the new label
appended to it's labels and 'Old Label' will be ignored. If it doesn't
exist it will be added to the end of data set with the existing rows
having level 1 with label specified in 'Old Label'.


To unsubscribe from the GENSTAT list, click the following link:
https://www.jiscmail.ac.uk/cgi-bin/webadmin?SUBED1=GENSTAT&A=1

Post generated using Mail2Forum (http://www.mail2forum.com)
View user's profile Send private message

Display posts from previous:  

All times are GMT
Page 1 of 1
Post new topic

Jump to:  

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum