0

I read a file, change its content, and then I want to write the dataframe into a new file. The thing that bugs me is that the width of the columns isn't adjustable within Excel (it does not save changes). I was wondering if it is possible to write the csv file with column width that fits the longest value.

dat <- read.csv("Input.csv")

# Do some processing

#Write the new file
write.csv(dat, "Output.csv", row.names=FALSE)

Edit 1:

dat <- read.csv("Input.csv")
createSheet(wb, "test")
writeWorksheet(wb, dat, "test")
colWidths <- sapply(1:ncol(dat), function(i) max(c(8, nchar(c(names(dat)[i], as.character(dat[, i]))))))
setColumnWidth(wb, "test", 1:ncol(dat), colWidths * 256)
saveWorkbook(wb)

what did I do wrong? It writes an empty file.

A.J
  • 1,140
  • 5
  • 23
  • 58
  • CSV is a plain text format, I can't see how there would be a way to directly do this. I suppose you could store a vector of desired column widths as the last row of the csv, then write some kind of excel macro to apply those fields to an imported CSV. But that seems like overkill... Or, dunno, maybe try appending a whitespace row at the bottom of the desired length/column? No idea if that would work. – Paul Gowder Jul 08 '15 at 19:03
  • 1
    See `write.fwf` in `library(gdata)` http://www.inside-r.org/packages/cran/gdata/docs/write.fwf – Vlo Jul 08 '15 at 19:03
  • *minimal* code is highly encouraged. this question seems more about adjusting column widths in excel spreadsheets anyway – rawr Jul 08 '15 at 19:12
  • @rawr completely agree. Have trimmed the code down to the bare minimum – Nick Kennedy Jul 08 '15 at 19:42

2 Answers2

1

It doesn't matter what widths you write for your csv; Excel will always have its default column width when you open it.

Your options are:

  1. Accept this behaviour.
  2. Resave the file from Excel as something else (.xls or .xlsx)
  3. Write the file from R using a package that directly exports Excel files. XLConnect will do this and even has a setColumnWidth function to set the column widths within R.

e.g.

dat <- data.frame(x = 1:24, `Long Column Name` = 25:48, `Wide Column` = paste(LETTERS, collapse = " "))
library("XLConnect")
wb <- loadWorkbook("Output.xlsx", create = TRUE)
createSheet(wb, "Output")
writeWorksheet(wb, dat, "Output")
colWidths <- sapply(1:ncol(dat), function(i) max(c(8, nchar(c(names(dat)[i], as.character(dat[, i])))))
setColumnWidth(wb, "Output", 1:ncol(dat), colWidths * 256)
saveWorkbook(wb)
Nick Kennedy
  • 12,510
  • 2
  • 30
  • 52
  • Thank you, but for some reason it gives me an empty file. I added the code that I tried to my original question. – A.J Jul 08 '15 at 20:35
  • @Nik you're missing the `loadWorkbook` line, but I wouldn't have thought that was the issue. Is the .xlsx file a valid spreadsheet? Does it have any worksheets at all? What are you using to open it? – Nick Kennedy Jul 08 '15 at 22:00
  • I am so stupid that it's funny (to me). The missing line fixed it. Thank you! It works great now! Do you know if I can choose the directory of the file I am saving though? – A.J Jul 08 '15 at 23:36
  • @Nik just specify a full name to the `loadWorkbook` function, e.g. (on Windows) "c:/folder/subfolder/file.xlsx") – Nick Kennedy Jul 09 '15 at 06:23
0

You may need to close the .csv in Excel before you run write.csv in R because Excel locks the file.

It is also possible to pad the columns like this if that is what you want.

Community
  • 1
  • 1
theoldfather
  • 31
  • 5