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)