1

I have a LaTeX table generated by Excel. I want to place this table but not as a LaTeX code, but rather as a xtable. It is possible to convert LaTeX to xtable?

6
  • If the table was generated by Excel, wouldn't it be better to use R to read the Excel file directly? Is this really a LaTeX question?
    – Alan Munn
    Commented Oct 10, 2013 at 22:50
  • 1
    What mean "not as LaTeX code but rather as a xtable"? A xtable is just LaTeX code generated by R, instead of an ouput in plain text. Your problem is read the Excel in R directly or after export to .csv text, assign to an object as myexeceldata, load the xtable library and then run xtable(myexeceldata) to obtain the LateX code, or simply run myexeceldata to obtain the table in plain text.
    – Fran
    Commented Oct 11, 2013 at 2:07
  • @Fran your comment has benn very helpfully. Now, I have both tables (generated in excel and also using the xtable package) running in my tex document. Commented Oct 29, 2013 at 17:06
  • @MarcoA.González, Glad to help. To leave the question as answered you could answer your own question to tell us your final procedure, so others could learn from it (or improve, if possible). By the way, do you know about literate programming with Sweave or kintr? I think it might be very useful in this task.
    – Fran
    Commented Oct 29, 2013 at 23:08
  • @Fran Can you provide an answer to get if off the unanswered list? I don't even understand what the real problem is.
    – Johannes_B
    Commented Mar 10, 2015 at 21:56

2 Answers 2

4

Set by step: Suppose that you have a file.xlsx that look like:

Excel file

To convert to R, first export to file.csv:

,apples,tomatoes,babanas
Jinks,3,12,15
Pixie,4,5,6
Dixie,2,8,9

Then import as a data frame mytable in R:

> mytable <- read.table("file.csv", header=TRUE, sep=",") 
> mytable

      X apples tomatoes babanas
1 Jinks      3       12      15
2 Pixie      4        5       6
3 Dixie      2        8       9

Or import directly from the file.xlsx (need Perl installed on your system):

> library(gdata) 
> mytable  <- read.xls("file.xlsx")  

The data frame obtained must be the same in any case. Just in case that first column are really row names as above:

> rownames(mytable) <- mytable[,1]
> mytable[,1] <- NULL
> mytable

      apples tomatoes babanas
Jinks      3       12      15
Pixie      4        5       6
Dixie      2        8       9

For convert the dataframe mytable as a xtable (that is, a chunk of LaTeX code):

> library(xtable) 
> xtable(mytable)

% latex table generated in R 3.1.1 by xtable 1.7-4 package
% Wed Mar 11 06:11:44 2015
\begin{table}[ht]
\centering
\begin{tabular}{rrrr}
  \hline
 & apples & tomatoes & babanas \\ 
  \hline
Jinks &   3 &  12 &  15 \\ 
  Pixie &   4 &   5 &   6 \\ 
  Dixie &   2 &   8 &   9 \\ 
   \hline
\end{tabular}
\end{table}

So you can copy & paste the ouput of R in your LaTeX file to obtain:

MWE2

But the good thing of LaTeX+R is that you can make all the above automatically inserting a R chunk in your file.tex document (now is R noweb document, so save it as file.Rnw) and compile it using Sweave or knitr from the command line (see How to build Knitr document from the command line ) or simply making in Rstudio:

File > New file > R Sweave > Write some like:

\documentclass[twocolumn]{article}
\usepackage{titlecaps}
\usepackage{lipsum}
\title{Owners {\it \&} Fruits}
\author{Fran}
\begin{document}
\maketitle
<<mytable,echo=FALSE,warning=FALSE,message=FALSE,results='hide'>>=
library(gdata) 
mytable  <- read.xls("file.xlsx")
rownames(mytable) <- mytable[,1]
mytable[,1] <- NULL
library(xtable) 
@
\section*{Introduction}
The cartoons (\Sexpr{row.names(mytable)}) are shooting fruits (\Sexpr{names(mytable)}). 
\lipsum[2] 
\section*{Cartoons have \Sexpr{sum(mytable$apples)} apples}
\lipsum[3]
<<xtable,echo=FALSE,warning=FALSE,message=FALSE,results='asis'>>=
print(xtable(mytable, caption="My Excel table"), caption.placement = "top")
@
\lipsum[4-15]
\end{document}

and finally, click on Compile PDF and you will obtain this file.pdf:

MWE

0
0

More generally, if you have a latex table my_file.tex such as:

% latex table generated in R 3.1.1 by xtable 1.7-4 package
% Wed Mar 11 06:11:44 2015
\begin{table}[ht]
\centering
\begin{tabular}{rrrr}
  \hline
 & apples & tomatoes & bananas \\ 
  \hline
Jinks & 3 & 1 & 15 \\ 
  Pixie & 4 & 5 & 6  \\ 
  Dixie & 2 & 8 & 9  \\ 
  \hline
\end{tabular}
\end{table}

You can import it into R directly, such as:

library("data.table") # loads the smart "fast" fread function

#import data into R
data <- fread("my_file.tex", skip=8, sep="&", data.table=F) # skips in compatible lines
names <- read.table("my_file.tex", skip=6, nrow=1, sep="&", stringsAsFactors=F)

#remove latex symbols from header
names<-gsub("\\\\", "", as.character(names))
names<-gsub(" ", "", as.character(names))
colnames(data) <- names

remove latex end of line from last column
data[,ncol(data)]<-gsub("\\\\", "", as.character(data[,ncol(data)]))
data[,ncol(data)]<-gsub(" ", "", as.character(data[,ncol(data)]))
data[,ncol(data)] <- as.numeric(data[,ncol(data)]) #change type as desired

This returns the following from calling data in R:

     NA apples tomatoes bananas
1 Jinks      3        1      15
2 Pixie      4        5      6
3 Dixie      2        8      9

Note: requires data.table 1.10.0 or higher

You can also export this from R to LaTeX:

library("xtable")    
#function to export (filename defaults to object in R environment)
export_latex <- function(x, file) print(xtable(data.frame(x), type="latex", file=paste0(deparse(substitute(x)), ".tex"))
export_latex(data, file="my_file.tex")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .