CODEWITHSUNDEEP
rexcelcsv
A.J
A.J

Reputation: 1180

Writing a csv file with fixed width in r

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.

Upvotes: 1

Views: 2064

Answers (2)

Nick Kennedy
Nick Kennedy

Reputation: 12640

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)

Upvotes: 1

theoldfather
theoldfather

Reputation: 31

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.

Upvotes: 0

Related Questions