Writing a csv file with fixed width in r

Question

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.

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
– Paul Gowder, Commented Jul 8, 2015 at 19:03
See write.fwf in library(gdata) inside-r.org/packages/cran/gdata/docs/write.fwf — Vlo
– Vlo, Commented Jul 8, 2015 at 19:03
minimal code is highly encouraged. this question seems more about adjusting column widths in excel spreadsheets anyway — rawr
– rawr, Commented Jul 8, 2015 at 19:12
@rawr completely agree. Have trimmed the code down to the bare minimum — Nick Kennedy
– Nick Kennedy, Commented Jul 8, 2015 at 19:42

Nick Kennedy · Accepted Answer · 2015-07-08 19:58:09Z

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:

Accept this behaviour.
Resave the file from Excel as something else (.xls or .xlsx)
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)

edited Jul 8, 2015 at 19:58

answered Jul 8, 2015 at 19:37

Nick Kennedy

12.6k2 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

A.J Over a year ago

Thank you, but for some reason it gives me an empty file. I added the code that I tried to my original question.

Nick Kennedy Over a year ago

@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?

A.J Over a year ago

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?

Nick Kennedy Over a year ago

@Nik just specify a full name to the loadWorkbook function, e.g. (on Windows) "c:/folder/subfolder/file.xlsx")

Community · Accepted Answer · 2017-05-23 12:14:06Z

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.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jul 8, 2015 at 19:01

theoldfather

315 bronze badges

Collectives™ on Stack Overflow

Writing a csv file with fixed width in r

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related