2

I am trying to read in an excel file into R. The data is formatted as follows: enter image description here

As you can see, there are cells with multi line information. I am taking this excel file and putting it up on R shiny as a question. (As radio buttons) Now if I read it using read.xlsx, my lines are squished together into one single line. How do I read the file into R with the multi line formatting preserved?
If I have a single line as my question, it is difficult to read and understand.

Swastik Mohapatra
  • 77
  • 7

2 Answers2

4

While it may look like R has missed the line breaks, it hasn't, they're still there. R notates them as \n, or in the case of the example you give (where the person entering those sentences has pressed enter twice, R would have \n\n)

Try writing the data with:

write.xlsx(yourdata, "test.xlsx", sheetName="sheet1", row.names=FALSE)

The line breaks will be there!

Extra note: there are additional things to be wary of. For example, when you write an excel file, the default row height is the excel default of 14.5 (so you'll only see the first line of text - remember to increase the size to see more text). You could also set the row height when writing the excel file

stevec
  • 41,291
  • 27
  • 223
  • 311
  • 1
    Additionally, I think if you display the contents of the cell using the `cat()` rather than `print()` function, the `\n`s will be interpreted correctly. – JonMinton Jul 26 '18 at 14:44
  • Thanks for the inputs guys. So I read the file using `read.xlsx`, kept `allowEscapes = T` and then tried to display them on the shiny app. Shiny was not printing out the line breaks. I wanted the app to display the questions with line breaks. – Swastik Mohapatra Jul 26 '18 at 15:41
  • @SwastikMohapatra I wonder if you can gsub the line breaks for some html, like swapping all “\n” for “” (the html equivalent) ? Might be worth a shot – stevec Jul 26 '18 at 15:51
0

Are you are using formula in cell? if yes then you need to open and close the file in excel to complete the calculations. if you are using windows then you can write a vbs script for that, as well.

makarand kulkarni
  • 193
  • 2
  • 11