R help - Aug 2017 - Converting character to numeric using the package "XLConnect"

Hello,
>From an Excel file imported into R where each cell contains characters, I
would like to convert some characters to numeric. However, my code doesn?t work.
When I write data to worksheets in an Excel file, some numbers in the cells are
stored as text (instead of numeric). Here is my code (the Excel file is
attached):
rm(list=ls(all=TRUE))
library(XLConnect)
tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create = TRUE)
set <- readWorksheet(tab, sheet = "settings")
setNum <- readWorksheet(tab, sheet = "settings",
colTypes="numeric")
index <- which((!is.na(setNum)), arr.ind=TRUE)
if(length(index)!=0){
  set[index] <- unlist(lapply(set[index], function(x)
as.numeric(as.character(x))))
}
## to check
set[10,1] ## Problem: must be numeric !!!!

writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set,
sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")

How can I convert the numbers which are stored as characters to numeric?

Thanks so much for your time.

Have a nice day

Nell

> On Aug 30, 2017, at 12:24 PM, Nelly Reduan <nell.redu at hotmail.fr>
wrote:
> 
> Hello,
> 
> From an Excel file imported into R where each cell contains characters, I
would like to convert some characters to numeric. However, my code doesn?t work.
When I write data to worksheets in an Excel file, some numbers in the cells are
stored as text (instead of numeric). Here is my code (the Excel file is
attached):
Nope. No file was attached in what was circulated. The list-server scrubs
potentially malicious files (including zip, csv, or xls files). You could send a
"csv" file with extension of ".txt".
> 
> rm(list=ls(all=TRUE))
> library(XLConnect)
> tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create =
TRUE)
> set <- readWorksheet(tab, sheet = "settings")
> setNum <- readWorksheet(tab, sheet = "settings",
colTypes="numeric")
Presumably you could also send the results of 

dput(setNum)

.. either as a .txt attachment or embedded in an email.
> index <- which((!is.na(setNum)), arr.ind=TRUE)
> if(length(index)!=0){
>  set[index] <- unlist(lapply(set[index], function(x)
as.numeric(as.character(x))))
> }
> ## to check
> set[10,1] ## Problem: must be numeric !!!!
> 
> writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set,
sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")
> 
> How can I convert the numbers which are stored as characters to numeric?
Have you tried using the formatting features of Excel to change the default
"auto" sttings of black columns? You could select an entire column and
set its format to numeric. I know this sometimes fixes the annoying habit of
Excel of changing texts items withn interior dashes to dates.

-- 

David Winsemius
Alameda, CA, USA

'Any technology distinguishable from magic is insufficiently advanced.' 
-Gehm's Corollary to Clarke's Third Law

The Excel file can be found from this link:
https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh

[https://r1.res.office365.com/owa/prem/images/dc-xlsx_40.png]<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
File.xlsx<https://1drv.ms/x/s!Apkg2VlgfYyDgQ_mcJ8F4CaXV_Nh>
Partag? via OneDrive





Thanks very much for your help.

Nell

________________________________
De : David Winsemius <dwinsemius at comcast.net>
Envoy? : mercredi 30 ao?t 2017 12:59:57
? : Nelly Reduan
Cc : r-help at r-project.org
Objet : Re: [R] Converting character to numeric using the package
"XLConnect"

> On Aug 30, 2017, at 12:24 PM, Nelly Reduan <nell.redu at hotmail.fr>
wrote:
>
> Hello,
>
> From an Excel file imported into R where each cell contains characters, I
would like to convert some characters to numeric. However, my code doesn?t work.
When I write data to worksheets in an Excel file, some numbers in the cells are
stored as text (instead of numeric). Here is my code (the Excel file is
attached):
Nope. No file was attached in what was circulated. The list-server scrubs
potentially malicious files (including zip, csv, or xls files). You could send a
"csv" file with extension of ".txt".
>
> rm(list=ls(all=TRUE))
> library(XLConnect)
> tab <- loadWorkbook("C:/Users/Downloads/File.xlsx", create =
TRUE)
> set <- readWorksheet(tab, sheet = "settings")
> setNum <- readWorksheet(tab, sheet = "settings",
colTypes="numeric")
Presumably you could also send the results of

dput(setNum)

.. either as a .txt attachment or embedded in an email.
> index <- which((!is.na(setNum)), arr.ind=TRUE)
> if(length(index)!=0){
>  set[index] <- unlist(lapply(set[index], function(x)
as.numeric(as.character(x))))
> }
> ## to check
[[elided Hotmail spam]]
>
> writeWorksheetToFile("C:/Users/Downloads/Test.xlsx", data=set,
sheet="settings", styleAction = XLC$"STYLE_ACTION.NONE")
>
> How can I convert the numbers which are stored as characters to numeric?
Have you tried using the formatting features of Excel to change the default
"auto" sttings of black columns? You could select an entire column and
set its format to numeric. I know this sometimes fixes the annoying habit of
Excel of changing texts items withn interior dashes to dates.

--

David Winsemius
Alameda, CA, USA

'Any technology distinguishable from magic is insufficiently advanced.' 
-Gehm's Corollary to Clarke's Third Law






	[[alternative HTML version deleted]]