1## s$Id: xls2sep.R 1639 2013-01-14 20:47:57Z warnes $ 2 3xls2csv <- function(xls, sheet=1, verbose=FALSE, blank.lines.skip=TRUE, 4 ..., perl="perl") 5 xls2sep(xls=xls, sheet=sheet, verbose=verbose, 6 blank.lines.skip=blank.lines.skip, ..., method="csv", 7 perl=perl) 8 9xls2tab <- function(xls, sheet=1, verbose=FALSE, blank.lines.skip=TRUE, 10 ..., perl="perl") 11 xls2sep(xls=xls, sheet=sheet, verbose=verbose, 12 blank.lines.skip=blank.lines.skip, ..., method="tab", 13 perl=perl) 14 15xls2tsv <- function(xls, sheet=1, verbose=FALSE, blank.lines.skip=TRUE, 16 ..., perl="perl") 17 xls2sep(xls=xls, sheet=sheet, verbose=verbose, 18 blank.lines.skip=blank.lines.skip, ..., method="tsv", 19 perl=perl) 20 21xls2sep <- function(xls, 22 sheet=1, 23 verbose=FALSE, 24 blank.lines.skip=TRUE, 25 ..., 26 method=c("csv","tsv","tab"), 27 perl = perl) 28 { 29 30 method <- match.arg(method) 31 32 perl <- if (missing(perl)) 33 findPerl(verbose = verbose) 34 else 35 findPerl(perl, verbose = verbose) 36 37 ## 38 ## directories 39 package.dir <- find.package('gdata') 40 perl.dir <- file.path(package.dir,'perl') 41 ## 42 43 ## 44 ## filesheet 45 tf <- NULL 46 if ( substring(xls, 1, 7) == "http://" || 47 substring(xls, 1, 6) == "ftp://" ) 48 { 49 tf <- paste(tempfile(), "xls", sep = ".") 50 if(verbose) 51 cat("Downloading", 52 dQuote(xls), " to ", 53 dQuote(tf), "...\n") 54 download.file(xls, tf, mode = "wb") 55 if(verbose) cat("Done.\n") 56 xls <- tf 57 } 58 59 if(method=="csv") 60 { 61 script <- file.path(perl.dir,'xls2csv.pl') 62 targetFile <- paste(tempfile(), "csv", sep = ".") 63 } 64 else if(method=="tab") 65 { 66 script <- file.path(perl.dir,'xls2tab.pl') 67 targetFile <- paste(tempfile(), "tab", sep = ".") 68 } 69 else if(method=="tsv") 70 { 71 script <- file.path(perl.dir,'xls2tsv.pl') 72 targetFile <- paste(tempfile(), "tsv", sep = ".") 73 } 74 else 75 { 76 stop("Unknown method", method) 77 } 78 79 ## 80 ## 81 82 ## 83 ## blank.lines.skip 84 ## 85 if (blank.lines.skip) 86 skipBlank="" 87 else 88 skipBlank="-s" 89 90 ## 91 ## execution command 92 cmd <- paste(shQuote(perl), 93 shQuote(script), 94 skipBlank, # flag is not quoted 95 shQuote(xls), 96 shQuote(targetFile), 97 shQuote(sheet), 98 sep=" ") 99 ## 100 ## 101 102 if(verbose) 103 { 104 cat("\n") 105 cat("Converting xls file\n") 106 cat(" ", dQuote(xls), "\n") 107 cat("to", method, " file \n") 108 cat(" ", dQuote(targetFile), "\n") 109 cat("... \n\n") 110 } 111 112 ## 113 ## do the translation 114 if(verbose) cat("Executing '", cmd, "'... \n\n") 115 116 results <- try(system(cmd, intern=!verbose)) 117 118 if(inherits(results, "try-error")) 119 stop( "Unable to read xls file '", xls, "':", results ) 120 121 if(verbose) cat(results,"\n\n") 122 if (verbose) cat("Done.\n\n") 123 124 ## 125 ## check that the target file was created 126 ## 127 if(!file.exists(targetFile)) 128 stop( "Intermediate file '", targetFile, "' missing!" ) 129 130 ## Creae a file object to hand to the next stage.. 131 retval <- try(file(targetFile)) 132 if(inherits(retval, "try-error")) 133 134 stop("Unable to open intermediate file '", targetFile, "':", 135 retval) 136 137 return(retval) 138 139 } 140 141