[R] collumn error when exporting to Excel

Marc Schwartz marc_schwartz at me.com
Thu Nov 5 15:00:03 CET 2009


On Nov 5, 2009, at 3:56 AM, Hayes, Daniel wrote:

> Dear all,
>
> I am attempting to export my results (data.frame) created with the  
> help of a number of you to Excel.
> In the procedure my column structure is however lost and all results  
> are placed together into the first Excel column.
> I have tried: write(), write.table(), write.matrix(), export() and  
> have the same results. I Have checked the import/export FAQ and did  
> a Google search to no avail.
> Any advice would be greatly appreciated.
>
> Kind greetings,
> Daniel
>
>> dput(Bolivia.selected.ms.lat.m[1:10,])
> structure(list(age = c(0, 0.0833333333333333, 0.166666666666667,
> 0.25, 0.333333333333333, 0.416666666666667, 0.5, 0.583333333333333,
> 0.666666666666667, 0.75), country = structure(c(1L, 1L, 1L, 1L,
> 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("Bolivia", "Brazil", "Colombia",
> "Dominican Rep.", "El Salvador", "Guatemala", "Guyana", "Haiti",
> "Honduras", "Nicaragua", "Paraguay", "Peru", "Suriname"), class =  
> "factor"),
>    mu = c(11.4216795790532, 11.336249852927, 11.2841715916567,
>    11.2112464680493, 11.1163747056146, 11.0042010015542,  
> 10.8814601111044,
>    10.7555483953614, 10.6326412246687, 10.5168465177670), sigma =  
> c(0.101487190832973,
>    0.105383747354763, 0.107059434066487, 0.108387198864101,
>    0.109530496229634, 0.110541869698550, 0.111444696116090,
>    0.112248590819971, 0.112951348056450, 0.113550216614654)), .Names  
> = c("age",
> "country", "mu", "sigma"), row.names = c("1", "2", "3", "4",
> "5", "6", "7", "8", "9", "10"), class = "data.frame")
>> write.matrix(Bolivia.selected.ms.lat.m, file="C:\\Documents and  
>> Settings\\Dohyedan\\My Documents\\bolivia.selected.ms.lat.m", sep="  
>> ")
>>

Try using write.table() with the following arguments:

   write.table(DataFrameName, file = "YourFileName.csv", sep = ",",  
quote = TRUE, row.names = FALSE)

So, using the data you have above, with output to the console:

 > DF
           age country       mu     sigma
1  0.00000000 Bolivia 11.42168 0.1014872
2  0.08333333 Bolivia 11.33625 0.1053837
3  0.16666667 Bolivia 11.28417 0.1070594
4  0.25000000 Bolivia 11.21125 0.1083872
5  0.33333333 Bolivia 11.11637 0.1095305
6  0.41666667 Bolivia 11.00420 0.1105419
7  0.50000000 Bolivia 10.88146 0.1114447
8  0.58333333 Bolivia 10.75555 0.1122486
9  0.66666667 Bolivia 10.63264 0.1129513
10 0.75000000 Bolivia 10.51685 0.1135502


 > write.table(DF, file = "", sep = ",", quote = TRUE, row.names =  
FALSE)
"age","country","mu","sigma"
0,"Bolivia",11.4216795790532,0.101487190832973
0.0833333333333333,"Bolivia",11.336249852927,0.105383747354763
0.166666666666667,"Bolivia",11.2841715916567,0.107059434066487
0.25,"Bolivia",11.2112464680493,0.108387198864101
0.333333333333333,"Bolivia",11.1163747056146,0.109530496229634
0.416666666666667,"Bolivia",11.0042010015542,0.11054186969855
0.5,"Bolivia",10.8814601111044,0.11144469611609
0.583333333333333,"Bolivia",10.7555483953614,0.112248590819971
0.666666666666667,"Bolivia",10.6326412246687,0.11295134805645
0.75,"Bolivia",10.516846517767,0.113550216614654


An alternative would be to use the WriteXLS package on CRAN, though  
you will need to install Perl to use it. That would allow you to write  
directly to an Excel file, rather than a CSV file and then import to  
Excel.  See the 'Installation' information on the CRAN page for the  
package here:

   http://cran.r-project.org/web/packages/WriteXLS/index.html

HTH,

Marc Schwartz




More information about the R-help mailing list