Dynamically Converting a List of Excel Files to CSV Files in R

Dynamically converting a list of Excel files to csv files in R

You can try this using rio, since it seems like that's what you're already using:

library("rio")
xls <- dir(pattern = "xlsx")
created <- mapply(convert, xls, gsub("xlsx", "csv", xls))
unlink(xls) # delete xlsx files

Convert XLS to CSV - R (Tried Rio Package)

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)

xlsx and tsv file manipulation: convert xlsx file to tsv file with csv (R)

A couple of things are confusing in your question. It is tagged csv but you state that you want a tab separated file. Also, it wasn't clear that you wanted all the word columns combined into a single column. I have assumed this is what you wanted.

# read file in with readxl package
xlsx <- readxl::read_xlsx("book.xlsx")

# Combine all the word columns into a single column, separated by a comma and a space
xlsx$words2 <- paste(xlsx$Words, xlsx$X__1, xlsx$X__2, xlsx$X__3, sep = ", ")
# select just the name and words column
xlsx <- xlsx[, c("Name", "words2")]
# rename the new column as "Words"
names(xlsx)[2] <- "Words"
# write out, specifying the tab separator
write.table(x = xlsx, file = "out.tsv", row.names = FALSE, sep = "\t")

Read excel data dynamically in R

If you want to filter the data while reading and not after you can use read.csv.sql from sqldf which takes SQL queries to filter but it works only with csv data and cannot read excel files.

If you can convert excel to csv you can use this.

library(sqldf)

#Write the csv
#readr::write_csv(mtcars, 'mtcars.csv')

read.csv.sql('mtcars.csv', sql = 'select * from file where cyl = 6')

#   mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#1 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
#2 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
#3 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
#4 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
#5 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
#6 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
#7 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6

---

Related Topics