Convert XLS to CSV - R (Tried Rio Package)

Question

I have a list of files in a directory which I'm trying to convert to csv, had tried rio package and solutions as suggested here

The output is list of empty CSV files with no content. It could be because the first 8 rows of the xls files have an image and few emtpy lines with couple couple of cells filled with text.

Is there any way I could skip those first 8 lines in all of xls files before converting.

Tried exploring options from openxlsx or readxls packages, any suggestions or guidance will be helpful.

Please do not mark as duplicate since I have a different problem than the one that was already answered

I'm not terribly familiar with rio, but almost all packages that read in Excel files have some argument that allows you to skip several rows. It might be called something like `startRow` or `skip`. — joran, Jul 10 '18 at 21:20
You should be able to do `rio::convert("file.xls", "file.csv", in_opts(skip = 8))`. — Thomas, Jul 10 '18 at 21:23
Sorry, that should actually be `rio::convert("file.xls", "file.csv", in_opts = list(skip = 8))` — Thomas, Jul 10 '18 at 21:41

ekstroem · Answer 1 · 2018-07-11T14:31:57.947

4

Maybe the following will work. At least it does for my own mock-up of an excel file with a picture in the top

library("readxl")  # To read xlsx
library("readr")   # Fast csv write

indata <- read_excel("~/cowexcel.xlsx", skip=8)
write_csv(indata, path="cow.csv")

If you are running this for several files then combine it into a function. Note that the function below does no checking and might overwrite existing csv files

convert_excel_to_csv <- function(name) {
    indata <- read_excel(name, skip=8)    
    write_csv(indata, path=paste0(tools::file_path_sans_ext(name), ".csv"))
}

edited Jul 11 '18 at 14:31

answered Jul 11 '18 at 13:15

ekstroem

5,957
3
22
48

Thanks @ekstroem , I was able to do it using xlsx package – Mr Rj Jul 11 '18 at 15:53

score 0 · Accepted Answer · answered Jul 11 '18 at 15:56

Although I was not able to do it with rio to convert, I read it as xls and wrote it back as csv using below code. Testing worked fine, Hope it works without glitch in implementation.

files <- list.files(pattern = '*.xls')    

y=NULL

for(i in files ) {
  x <- read.xlsx(i, sheetIndex = 1, header=TRUE, startRow=9)
  y= rbind(y,x)
}

dt <- Sys.Date()
fn<- paste("path/",dt,".csv",sep="")
write.csv(y,fn,row.names = FALSE)

Convert XLS to CSV - R (Tried Rio Package)

2 Answers2