Save Excel spreadsheet as .csv with R?

Question

What is the easiest way to convert a large Excel spreadsheet with multiple worksheets to. CSV files in R?

Notice that I tested XLConnect and XLSX and found that my Excel worksheets were crashing. Therefore, I am specifically looking for a solution that does not use the XLConnect or XLSX package.

Answer 1

http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows

EDIT: to access the read.xlsx option:

If you use Perl, you need the current version of gdata p>

require(gdata) installXLSXsupport() #now the example from help(read.xls) # load the third worksheet, skipping the first two non-data lines... if( 'XLSX' %in% xlsFormats() ) # if XLSX is supported.. data <- read.xls(exampleFile2007, sheet="Sheet with initial text", skip=2) data #----------------------- X X.1 D E. FG Factor 1 NA FirstRow 1 NA NA NA Red 2 NA SecondRow 2 1 NA NA Green 3 NA ThirdRow 3 2 1 NA Red 4 NA FourthRow 4 3 2 1 Black #------------------------ write.csv(data) 

This was done on a Mac, and before this question I always stumbled upon the installXLSXsupport () step, since I always had an error. This time I started Perl from the Terminal command line and got success after the first setup of my personal configuration, defining CPAN mirrors on my continent, and I quit perl.

Answer 2

Here's a loop to write all sheets:

 require(gdata) ## install support for xlsx files installXLSXsupport() excelFile <- ("/full/path/to/excelFile.xlsx") ## note that the perl scripts that gdata uses do not cope well will tilde expansion ## on *nix machines. So use the full path. numSheets <- sheetCount(excelFile, verbose=TRUE) for ( i in 1:numSheets) { mySheet <- read.xls(excelFile, sheet=i) write.csv(mySheet, file=paste(i, "csv", sep="."), row.names=FALSE) } 

Answer 3

Updated answer based on readxl package.

 library("readxl") #function to read all sheets of a workbook read_excel_allsheets <- function(filename) { sheets <- readxl::excel_sheets(filename) x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X)) names(x) <- sheets x } sheetnames <- read_excel_allsheets("excelFile.xlsx") names(sheetnames)