1

I cannot export an R matrix to Excel with the values of the R-matrix exported in separate cells in Excel.

When I use the write.csv or write.table functions and try to open the output in Excel, the values of each row are merged into one cell in Excel. So each random cell in Excel looks something like this: [0.33,0.24,0.23,02] while I would like to have each value in separate cells like this: [0.33] [0.24] [0.23] etc.

          [,1]      [,2]      [,3]      [,4]      [,5]      [,6]      [,7]      [,8]
[1,] 1.0000000 0.8662618 0.9990604 0.9966197 0.9995365 0.9958028 0.9475572 0.9977010
[2,] 0.8662618 1.0000000 0.8568601 0.8846999 0.8524181 0.8930027 0.8778416 0 .8630592
[3,] 0.9990604 0.8568601 1.0000000 0.9965658 0.9994639 0.9947839 0.9501188 0.9977547
[4,] 0.9966197 0.8846999 0.9965658 1.0000000 0.9952404 0.9969191 0.9614838 0.9950432
[5,] 0.9995365 0.8524181 0.9994639 0.9952404 1.0000000 0.9941026 0.9432181 0.9974717
[6,] 0.9958028 0.8930027 0.9947839 0.9969191 0.9941026 1.0000000 0.9587064 0.9972479
[7,] 0.9475572 0.8778416 0.9501188 0.9614838 0.9432181 0.9587064 1.0000000 0.9581845
[8,] 0.9977010 0.8630592 0.9977547 0.9950432 0.9974717 0.9972479 0.9581845 1.0000000

I used these functions:

write.csv(cosmatBB, "2005cossims.csv")
write.table(cosmatBB, "cossimssTabelPoging.csv", sep=",")

But i did not get the desired output in Excel.

Thanks in advance.

niko
  • 5,253
  • 1
  • 12
  • 32
Duwes Schravezooie
  • 11
  • 3
  • 1
    Hm. Not sure how you did that! From what you have here, it's not clear if it's an R issue or an Excel issue. We can help you better if you provide a simpler example that we can try to reproduce. In this case, start with a smaller version of your data set, say `sm <- cosmatBB[1:4,1:4]` and then give us a version we can input by running `dput(sm)` and copying the output into your question. Then run your `write.csv` command on `sm` and copy the results of that file into your question as well. Perhaps after doing that the answer will even be clear to you! – Aaron left Stack Overflow May 25 '19 at 16:24
  • For general hints on how to write a good reproducible example, see this question: https://stackoverflow.com/q/5963269/210673 – Aaron left Stack Overflow May 25 '19 at 16:26
  • 1
    Might be caused by Windows regional settings: the problem you describe is common on a French computer, where by default CSV files are expected by Excel to have a semicolon separator instead of a comma. Check what Excel needs (export a CSV file from Excel and check its contents with notepad), and adapt the export from R (you can change the separator and the decimal separator as well, or you can use `write.csv2`). –  May 25 '19 at 16:42

2 Answers2

1

This sounds like an Excel problem more than an R problem as noted by Aaron.

In my experience there is a button in Excel 'Text to Columns' that allow you to convert one column with several variables to multiple columns. There is a manual here.

bwb
  • 163
  • 1
  • 5
0

There are several ways to do this.

First you can use write.csv2 or read.table with ; as seperator

write.table(cosmatBB, "2005cossims.csv", sep = ';', dec = '.')

Or use one of the many excel-packages

library('openxlsx')
write.xlsx(cosmatBB, "2005cossims.xlsx")
niko
  • 5,253
  • 1
  • 12
  • 32