[R] Exporting a data.frame to excel using sqlSave - adds a character ' to values

[Juliette Fabre]

Hello, 

Sorry for not providing my data:

> dput(data_col)
structure(list(Parameter_Name = c("Cd Cadmium", "Cd Cadmium", 
"Cd Cadmium", "Cd Cadmium", "Cd Cadmium", "Cd Cadmium", "Cd Cadmium", 
"Cd Cadmium", "Cd Cadmium", "Cd Cadmium", "Cd Cadmium", "Cd Cadmium", 
"Cd Cadmium", "Cd Cadmium", "Cd Cadmium"), Unit = c("ppb ", "ppb ", 
"ppb ", "ppb ", "ppb ", "ppb ", "ppb ", "ppb ", "ppb ", "ppb ", 
"ppb ", "ppb ", "ppb ", "ppb ", "ppb "), date = structure(c(12851, 
12872, 12906, 12956, 12993, 13025, 13041, 13070, 13094, 13118, 
13126, 13161, 13180, 13193, 13223), class = "Date"), `S5 piezometre` =
c(6.41, 
7.58, NA, 34.151, 11.83, 8.17, 5.86, 130.4886, 179.5569, 180.9052, 
131.6477, 81.3172, NA, 97.8383, 88.5979), `S1 Source Reigous ` = c(96.74, 
104.97, 76.842, 122.85, 102.29, 100.63, 79.81, 110.2931, 88.6012, 
NA, 48.4062, 80.8052, 98.7633, 93.452, 83.5394), COWG = c(86.03, 
87.67, 61.503, 96.359, NA, 90.44, 61.16, 100.6402, 67.2333, NA, 
39.5782, 62.3266, 56.2315, 60.829, 79.3607), GAL = c(64.07, 56.33, 
43.15, 79.538, 57.77, 61.81, 33.35, 79.0689, 20.6995, NA, 30.051, 
40.5197, 8.2002, 32.4497, 52.7988), Confluence = c(46.774, 40.813, 
86.655, 74.219, 19.19, 9.468, 80.124, 83.3786, 36.4044, NA, 77.4461, 
69.923, NA, 0.0665, 82.5202), `Amous plus 1200` = c(0.2025, 0.1851, 
0.745, 0.1023, 0.0756, 0.0816, 2.1559, 0.3747, 1.1879, NA, 1.7184, 
2.1946, NA, 1.7998, 1.9063)), .Names = c("Parameter_Name", "Unit", 
"date", "S5 piezometre", "S1 Source Reigous ", "COWG", "GAL", 
"Confluence", "Amous plus 1200"), class = "data.frame", row.names = c(NA, 
15L))

> channel <- odbcConnectExcel(xls.file = nom_fichierXls, readOnly = FALSE)
>   sqlSave(channel, data_col, tablename ="Table1", rownames = F, colnames =
> T)
> odbcClose(channel)

The situation I was referring to is this one : 




Tal Galili wrote
> 
> Hi all,
> 
> I am using RODB to export multiple data.frames into one excel file with
> multiple sheets.
> One thing I can't seem to unserdatand is way the command adds the
> character:
> '
> To the beginning of each of my (non numeric) values.
> 
> Here is an example code:
> 
> 
> library(RODBC)
> library(MASS)
> data(anorexia)
> save2excel <- function(x, t.name) sqlSave(xlsFile, x, tablename = t.name,
> rownames = FALSE)
> xlsFile <- odbcConnectExcel("C:\\output-table.xls", readOnly = FALSE)
> save2excel(anorexia, "temp")
> odbcCloseAll()
> 
> 
> So the output file has
> *'*Treat
> instead of:
> Treat
> In cell - A1
> 
> Thanks,
> Tal 
> 





Juliette Fabre wrote
> 
> Hello, 
> 
> I encountered a situation similar as the one described by Tal above :
> 
> I use the RODBC library to export multiple dataframes into different
> sheets of an Excel file.
> My dataframes contain Character, Date and Numeric columns.
> 
> library("RODBC")
> channel <- odbcConnectExcel(xls.file = myXlsFile, readOnly = FALSE)
> sqlSave(channel, data, tablename = "Table1", rownames = F, colnames = T)
> odbcClose(channel)
> 
> When exported into Excel, *all * of my cells start with the ' character
> (which is different from Tal's situation where *only * non-numeric cells
> started with ' character).
> I need the columns that contain numeric data or dates to be imported into
> the appropriate format so that they can be manipulated (graphics etc).
> 
> I found a macro that formats all the sheets in the appropriate way, but I
> would like to discover why even my numeric data (type Numeric in R)  are
> imported as text.
> 
> Regards, 
> 
> Juliette
> 


--
View this message in context: http://r.789695.n4.nabble.com/Exporting-a-data-frame-to-excel-using-sqlSave-adds-a-character-to-values-tp1016523p4511540.html
Sent from the R help mailing list archive at Nabble.com.