| read.gnumeric.sheet {gnumeric} | R Documentation |
Read data from a gnumeric (or MS Excel, Openoffice Calc, Xbase, Quatro Pro, Paradox, HTML, etc) spreadsheet or database file using ssconvert from the gnumeric distribution
Description
Read data from a sheet of a gnumeric (or other common spreadsheet or database) file to a data.frame.
Requires an external program, ‘ssconvert’ (normally installed with gnumeric) in ‘PATH’. See vignette ‘install-ssconvert.html’ for details.
Calls ‘ssconvert’ to convert the input to CSV. ‘ssconvert’ can read several file formats (see Details below).
Note: During conversion to CSV ‘ssconvert’ also evaluates formulas (e.g. ‘=sum(A1:A3)’) in cells, and emits the result instead of the formula.
‘read.gnumeric.range’ just calls ‘read.gnumeric.sheet’, but uses different default values for its arguments: by default drops no rows or columns and requires at least the bottom left corner of requested gnumeric cell range to be provided.
Usage
read.gnumeric.sheet(file,
head=FALSE,
sheet.name='Sheet1',
top.left='A1',
bottom.right=NA,
drop.empty.rows="bottom",
drop.empty.columns="right",
colnames.as.sheet=FALSE,
rownames.as.sheet=colnames.as.sheet,
quiet=TRUE,
LANG='C',
locale='C',
import.encoding=NA,
field.format='automatic',
...
);
read.gnumeric.range(file,
head=FALSE,
sheet.name='Sheet1',
top.left='A1',
bottom.right,
drop.empty.rows="none",
drop.empty.columns="none",
colnames.as.sheet=FALSE,
rownames.as.sheet=colnames.as.sheet,
quiet=TRUE,
LANG='C',
locale='C',
import.encoding=NA,
field.format='automatic',
...
);
Arguments
file |
Name of gnumeric file (or other file type readable by gnumeric) to read from. This may also be an URL, i.e. like |
head |
When TRUE, use first row of requested gnumeric sheet
range as column names in the resulting |
sheet.name |
Name of sheet as appears in gnumeric.
|
top.left |
Top left corner of requested gnumeric sheet
range, e.g. |
bottom.right |
Bottom right corner of requested gnumeric sheet
range. The default for If Use |
drop.empty.rows |
One of
|
drop.empty.columns |
One of
|
colnames.as.sheet |
Rename columns to |
rownames.as.sheet |
Rename rows to |
quiet |
When TRUE, do not print command executed, and (on unix platforms) also redirect stderr of the external program ‘ssconvert’ to /dev/null |
LANG |
Under unix, passed to |
locale |
Passed to |
import.encoding |
If not NA, passed to |
field.format |
Passed to "raw" emits date and datetime values as number of days since an
(unspecified) epoch. |
... |
Extra arguments, passed to |
Details
Data from the gnumeric file is dumped as .csv using the ‘ssconvert’ program provided with gnumeric.
‘ssconvert’ supports several input formats, thus the input file does not have to be a gnumeric file. The formats supported may be listed with
ssconvert --list-importers
from a shell prompt.
For me this prints (with ssconvert version '1.8.4')
ID | Description
Gnumeric_xbase:xbase | Xbase (*.dbf) file format
Gnumeric_Excel:excel | MS Excel (tm) (*.xls)
Gnumeric_Excel:xlsx | MS Excel (tm) 2007
Gnumeric_html:html | HTML (*.html, *.htm)
Gnumeric_oleo:oleo | GNU Oleo (*.oleo)
Gnumeric_applix:applix | Applix (*.as)
Gnumeric_QPro:qpro | Quattro Pro (*.wb1, *.wb2, *.wb3)
Gnumeric_paradox:paradox | Paradox database or
| primary index file
Gnumeric_sc:sc | SC/xspread
Gnumeric_XmlIO:sax | Gnumeric XML (*.gnumeric)
Gnumeric_lotus:lotus | Lotus 123 (*.wk1, *.wks, *.123)
Gnumeric_XmlIO:dom | Gnumeric XML (*.gnumeric) Old
| slow importer
Gnumeric_dif:dif | Data Interchange Format (*.dif)
Gnumeric_Excel:excel_xml | MS Excel (tm) 2003 SpreadsheetML
Gnumeric_OpenCalc:openoffice | Open/Star Calc (*.sxc, *.ods)
Gnumeric_plan_perfect:pln | Plan Perfect Format (PLN) import
Gnumeric_sylk:sylk | MultiPlan (SYLK)
Gnumeric_mps:mps | Linear and integer program (*.mps)
| file format
Gnumeric_stf:stf_csvtab | Comma or tab separated
| values (CSV/TSV)
Gnumeric_stf:stf_assistant | Text import (configurable)
But the actual list may depend on which import plugins are installed for gnumeric.
| Format | Source | Status |
| .gnumeric | gnumeric | works |
| .xls | gnumeric | works |
| .html | gnumeric ‘[Save as / HTML 4.0]’ | works |
| .html | Openoffice Calc ‘[Save as/HTML Document]’ | works |
| .ods | Openoffice Calc | works |
| Other formats | not tested |
See Also
read.gnumeric.range for a variant with default
arguments more suited for reading an exact cell range of a sheet.
read.gnumeric.sheet.info to read actual
bottom.right cell name from a gnumeric file (but not other formats).
read.gnumeric.sheets to read all sheets
from a gnumeric file (but not other formats).
read.xlsx, read_xlsx and
read_xls for reading Microsoft Excel files
read.DIF for reading Data Interchange Format (DIF)
files.
read.dbf for Xbase (.dbf) files.
Examples
## Read all data from 'Sheet1'
## Not run:
df <- read.gnumeric.sheet( file="file.gnumeric" );
df <- read.gnumeric.sheet( file="file.gnumeric",
sheet.name='Sheet1' );
## Read from Excel sheet named 'Sheet3' the range C3:D50,
## rename columns to 'C' and 'D', rows to '3' ... '50',
## then drop all empty rows.
##
df<-read.gnumeric.sheet( "file.xls",
sheet.name='Sheet3',
top.left='C3',
bottom.right='D50',
drop.empty.rows="all",
drop.empty.columns="none",
colnames.as.sheet=TRUE
)
## Read from "file.gnumeric", 'Sheet1' data in 'A1:E100',
## Use first row (of selected range) as column names.
## Drop empty rows and columns from bottom and right.
df<-read.gnumeric.sheet("file.gnumeric", head=TRUE,
bottom.right='E100')
## Why does it not work? Set quiet=FALSE to see
## the command executed (and on unix, diagnostic
## messages from ssconvert).
df<-read.gnumeric.sheet( "file.ods", quiet=FALSE )
## End(Not run)