R help - Jan 2010 - Yet another question about importing Excel *.xls files

Six times a year labs submit individual Excel spreadsheets to me. There are
usually around 60 labs and the spreadsheets have 12 columns with 20 rows. 

Chapter 8 of the R Data Import/Export manual recommends converting .xls
files, to a text file. I have been manually converting the individual .xls
files submitted by the 60 labs to a single .csv file. 

There are several steps in the process. First I have to open the raw file,
then copy and paste it into another .xls file and save the second file. This
second file has an additional page that formats the lab submitted data into
rows and columns that can be used by R. Finally, I copy the formatted page
into another Excel spreadsheet that is saved as a .csv file. This file
contains all the data submitted by the labs.

This takes about a day, but my main concern is the chance for errors. I have
to make sure I do not omit a lab, enter the same lab data twice, or
accidentally paste over some existing data.

It looks like odbcConnectExcel in the package RODBC can select rows and
columns from individual spreadsheets. Is it possible to use this on multiple
(60) spreadsheets and have a single spreadsheet automatically generated? If
so, could you please share some tips and examples to get me started.

Thanks,

Jerry Floren
Minnesota Department of Agriculture


-- 
View this message in context:
http://n4.nabble.com/Yet-another-question-about-importing-Excel-xls-files-tp1018699p1018699.html
Sent from the R help mailing list archive at Nabble.com.

1. read.xls in gdata has the capability of

- reading an xls file starting from the first row with a given pattern
- converting an xls file to a csv file
- converting an xls file to a data frame

Would those capabilities be sufficient for the input side?  See
examples in ?read.xls

2. If you do a google search for:  r-wiki excel
and click on Cache for the first one (since the web site sems down at
the moment) you can get some tips on using R with Excel.


On Wed, Jan 20, 2010 at 2:57 PM, Jerry Floren <jerry.floren at
state.mn.us> wrote:
>
> Six times a year labs submit individual Excel spreadsheets to me. There are
> usually around 60 labs and the spreadsheets have 12 columns with 20 rows.
>
> Chapter 8 of the R Data Import/Export manual recommends converting .xls
> files, to a text file. I have been manually converting the individual .xls
> files submitted by the 60 labs to a single .csv file.
>
> There are several steps in the process. First I have to open the raw file,
> then copy and paste it into another .xls file and save the second file.
This
> second file has an additional page that formats the lab submitted data into
> rows and columns that can be used by R. Finally, I copy the formatted page
> into another Excel spreadsheet that is saved as a .csv file. This file
> contains all the data submitted by the labs.
>
> This takes about a day, but my main concern is the chance for errors. I
have
> to make sure I do not omit a lab, enter the same lab data twice, or
> accidentally paste over some existing data.
>
> It looks like odbcConnectExcel in the package RODBC can select rows and
> columns from individual spreadsheets. Is it possible to use this on
multiple
> (60) spreadsheets and have a single spreadsheet automatically generated? If
> so, could you please share some tips and examples to get me started.
>
> Thanks,
>
> Jerry Floren
> Minnesota Department of Agriculture
>
>
> --
> View this message in context:
http://n4.nabble.com/Yet-another-question-about-importing-Excel-xls-files-tp1018699p1018699.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>