0

I need to convert multiple .xlsx file into csv files but the contents of the original .xlsx is modified during conversion.

For example, the starting excel file will look like this

   A  B  C  D
1 Foo
2
3           Foo

After conversion, the .csv file will contain this

   A  B  C  D
1 X1  X2 X3 X4
2 Foo NA NA NA
3 NA  NA NA NA          
4 Na  Na NA Foo

How can I convert the .xlsx without any changes made to the contents? I've also read that read.xlsx may have issues with dates. Is there a simple way to convert .xlsx to csv?

Here's the code that I'm using.

 PathOut<-"C:/Users/Desktop/New folder/"

  require(openxlsx)
  file_list<-list.files(path = PathOut, pattern='\\.xlsx$')

  x=0
  for (file in file_list) {
    setwd("C:/Users/Desktop/New folder/")
    x<-x+1
    file.xl <- read.xlsx(file,skipEmptyRows = FALSE,skipEmptyCols = FALSE,colNames = FALSE,rowNames = FALSE)
    newname<-paste0(tools::file_path_sans_ext(file),'_',LETTERS[x],'.csv')
    write.csv(file.xl, paste(PathOut,newname),row.names = FALSE)
  }

Thank you

Chumbs
  • 188
  • 1
  • 1
  • 10

1 Answers1

0

Swap your use of write.csv to write.table so that you can use the col.names = FALSE argument. This will stop the X1 X2 X3 X4 row from appearing. See this SO question for more help.

require(openxlsx)

PathOut <- getwd()

file_list <- list.files(path = PathOut, pattern = '\\.xlsx$')

x <- 0
for (file in file_list) {
    x <- x+1
    file.xl <- read.xlsx(file, skipEmptyRows = FALSE, skipEmptyCols = FALSE, colNames = FALSE, rowNames = FALSE)
    newname <- paste0(tools::file_path_sans_ext(file),'_',LETTERS[x],'.csv')
    write.table(file.xl, paste0(PathOut, "/", newname), row.names = FALSE, col.names = FALSE)
}
meenaparam
  • 1,949
  • 2
  • 17
  • 29
  • Thanks. This removes the column names. I also added na='' to remove the NAs but it does not solve the issue. The data contents are still being modified and quotation marks are added to the .csv if the contents of the excel cell is formatted. I read somewhere that it isn't a good idea to use R to convert the xlsx file into .csv because of this issue. – Chumbs Jan 10 '20 at 17:20
  • Ah yes, I see the problem now. I had previously only been viewing the file in RStudio, where such problems did not happen. There is a `quote = FALSE` parameter that can also be added to the `write.table` function but I don't think this is giving you what you want yet. I'll think on it a bit more. – meenaparam Jan 10 '20 at 17:36