[R] Importing Headers from excel files

[Gabor Grothendieck]

read.xls in the development version of the gdata package can read xls
and xlsx files and could interpret both header rows by reading the
file twice. Using the ExampleExcelFile.xlsx file that comes with gdata
assume that the first two rows are actually headers (so the row of 1s
is a header in this example rather than data):

> library(gdata)
> xlsxfile <- file.path(.path.package('gdata'),'xls','ExampleExcelFile.xlsx')
> read.xls(xlsxfile, nrow = 3) # show first few rows before we start
  A B  C
1 1 1  1
2 2 4  8
3 3 9 27
> DF1 <- read.xls(xlsxfile, nrows = 1)
> DF <- read.xls(xlsxfile, skip = 2, col.names = paste(names(DF1), DF1))
> head(DF)
  A.1 B.1 C.1
1   3   9  27
2   4  16  64
3   5  25 125
4   6  36 216
5   7  49 343

An alternate method is to use xls2csv in gdata which converts the xlsx
file to a csv and then you use read.table twice on that in a similar
way.

To do this download the development version and build it yourself
noting the INSTALL file.  See the gdata section in:
http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows

Also check out the other packages listed there such as xlsx.

On Tue, Feb 23, 2010 at 4:37 PM, Luis Felipe Parra
<felipe.parra at quantil.com.co> wrote:
> Hello I am trying to import an Excel file but I am loosing the headers, My
> headers are in the first to rows of the EXCEL file. In the following R
> output, the NA are supposed to be the second item in the Header. Is there
> any way to Import more than one row as headers?. Thank you
>
> Felipe Parra
>
>> Data<-odbcConnectExcel('Curva LIBOR.xlsx',readOnly=T,)
>> AbioRep<-sqlFetch(Data,'LIBOR-SWAP')
>> head(AbioRep)
>        Date US0001W Index US0002W Index US0001M Index US0002M Index US0003M
> Index US0004M Index US0005M Index US0006M Index US0007M Index US0008M Index
> 1 2005-01-04       2.32938       2.33563        2.4000
> 2.49000          2.57        2.6400        2.7100        2.7900
> 2.85000       2.91000
> 2 2005-01-05       2.32875       2.33875        2.4000
> 2.50625          2.59        2.6625        2.7425        2.8275
> 2.89063       2.95125
> 3 2005-01-06       2.33000       2.34125        2.4200
> 2.52000          2.61        2.6800        2.7600        2.8400
> 2.90813       2.97000
> 4 2005-01-07       2.32875       2.34000        2.4300
> 2.52000          2.61        2.6800        2.7600        2.8350
> 2.89625       2.95125
> 5 2005-01-10       2.32875       2.34063        2.4400
> 2.53000          2.62        2.6900        2.7700        2.8500
> 2.91125       2.97000
> 6 2005-01-11       2.33000       2.34000        2.4425
> 2.53013          2.63        2.7000        2.7800        2.8600
> 2.92750       2.98625
>  US0009M Index US0010M Index US0011M Index US0012M Index        F16 USSA1
> Curncy USSA2 Curncy USSA3 Curncy
> 1        2.9675       3.01750       3.06375       3.11000
> <NA>           NA           NA           NA
> 2        3.0150       3.07375       3.13000       3.18063 2005-01-03
> 3.4345       3.4345        3.660
> 3        3.0300       3.08875       3.14000       3.19000 2005-01-04
> 3.5355       3.5355        3.757
> 4        3.0100       3.06000       3.11000       3.16000 2005-01-05
> 3.5395       3.5395        3.761
> 5        3.0300       3.08938       3.14000       3.19125 2005-01-06
> 3.5050       3.5050        3.725
> 6        3.0500       3.10625       3.15625       3.21000 2005-01-07
> 3.5450       3.5450        3.760
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>