Jim Holtman
2016-Jan-14 17:17 UTC
[R] read.xlsx - write.xlsx: reading/writing numbers as character
Write it out as a csv from excel and see what the data looks like. ?That may
help ?in seeing what the problem is.
Sent from my Verizon Wireless 4G LTE Smartphone
<div>-------- Original message --------</div><div>From: Mohsen
Jafarikia <jafarikia at gmail.com> </div><div>Date:01/14/2016
11:36 (GMT-05:00) </div><div>To: jim holtman <jholtman at
gmail.com> </div><div>Cc: r-help <r-help at
stat.math.ethz.ch> </div><div>Subject: Re: [R] read.xlsx -
write.xlsx: reading/writing numbers as character </div><div>
</div>Thanks for the comment Jim. There is no blank cell. All have
numbers.
Mohsen
On Thu, Jan 14, 2016 at 11:29 AM, jim holtman <jholtman at gmail.com>
wrote:
Take a look at the data coming in since you may have something that looks like
characters (maybe 'blanks'). What you think is numeric in EXCEL might
not be.?
Jim Holtman
Data Munger Guru
What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.
On Thu, Jan 14, 2016 at 11:20 AM, Mohsen Jafarikia <jafarikia at
gmail.com> wrote:
Hello:
I am reading some excel files (each with one sheet) and trying to write
them all in one file. I am not sure if read.xlsx reads some of the columns
as character or write.xlsx writes them as character where they are not
characters. I have 12 columns (2 character and 10 numbers). From 10 number
columns, with float and integer numbers, only 3 of them are recognized
correctly. I was wondering how can I define the the column format for
read.xlsx - write.xlsx.
Here comes a simple example of my code:
ifn1 <- "A.xlsx"
dat1 <- read.xlsx(ifn1, sheetName="A.csv", header = TRUE)
ifn2 <- "F.xlsx"
dat2 <- read.xlsx(ifn2, sheetName="F.csv",header = TRUE)
write.xlsx(dat1, file="AF.xlsx", sheetName="A",
showNA=FALSE,
row.names=FALSE, append=FALSE)
write.xlsx(dat2, file="AF.xlsx", sheetName="F",
showNA=FALSE,
row.names=FALSE, append= TRUE)
Thanks in advance!
Mohsen
[[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[[alternative HTML version deleted]]
Mohsen Jafarikia
2016-Jan-14 17:59 UTC
[R] read.xlsx - write.xlsx: reading/writing numbers as character
It looks okay on CSV too. On Thu, Jan 14, 2016 at 12:17 PM, Jim Holtman <jholtman at gmail.com> wrote:> Write it out as a csv from excel and see what the data looks like. That > may help in seeing what the problem is. > > > Sent from my Verizon Wireless 4G LTE Smartphone > > > -------- Original message -------- > From: Mohsen Jafarikia > Date:01/14/2016 11:36 (GMT-05:00) > To: jim holtman > Cc: r-help > Subject: Re: [R] read.xlsx - write.xlsx: reading/writing numbers as > character > > Thanks for the comment Jim. There is no blank cell. All have numbers. > > Mohsen > > On Thu, Jan 14, 2016 at 11:29 AM, jim holtman <jholtman at gmail.com> wrote: > >> Take a look at the data coming in since you may have something that looks >> like characters (maybe 'blanks'). What you think is numeric in EXCEL might >> not be.? >> >> >> Jim Holtman >> Data Munger Guru >> >> What is the problem that you are trying to solve? >> Tell me what you want to do, not how you want to do it. >> >> On Thu, Jan 14, 2016 at 11:20 AM, Mohsen Jafarikia <jafarikia at gmail.com> >> wrote: >> >>> Hello: >>> >>> I am reading some excel files (each with one sheet) and trying to write >>> them all in one file. I am not sure if read.xlsx reads some of the >>> columns >>> as character or write.xlsx writes them as character where they are not >>> characters. I have 12 columns (2 character and 10 numbers). From 10 >>> number >>> columns, with float and integer numbers, only 3 of them are recognized >>> correctly. I was wondering how can I define the the column format for >>> read.xlsx - write.xlsx. >>> >>> Here comes a simple example of my code: >>> >>> ifn1 <- "A.xlsx" >>> dat1 <- read.xlsx(ifn1, sheetName="A.csv", header = TRUE) >>> >>> ifn2 <- "F.xlsx" >>> dat2 <- read.xlsx(ifn2, sheetName="F.csv",header = TRUE) >>> >>> write.xlsx(dat1, file="AF.xlsx", sheetName="A", showNA=FALSE, >>> row.names=FALSE, append=FALSE) >>> write.xlsx(dat2, file="AF.xlsx", sheetName="F", showNA=FALSE, >>> row.names=FALSE, append= TRUE) >>> >>> Thanks in advance! >>> Mohsen >>> >>> [[alternative HTML version deleted]] >>> >>> ______________________________________________ >>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>> https://stat.ethz.ch/mailman/listinfo/r-help >>> PLEASE do read the posting guide >>> http://www.R-project.org/posting-guide.html >>> and provide commented, minimal, self-contained, reproducible code. >>> >> >> >[[alternative HTML version deleted]]
Mohsen Jafarikia
2016-Jan-14 19:30 UTC
[R] read.xlsx - write.xlsx: reading/writing numbers as character
Hi Jim, I found where the problem is. I had some characters in my list. Thanks very much for your attention and help! Mohsen On Thu, Jan 14, 2016 at 12:17 PM, Jim Holtman <jholtman at gmail.com> wrote:> Write it out as a csv from excel and see what the data looks like. That > may help in seeing what the problem is. > > > Sent from my Verizon Wireless 4G LTE Smartphone > > > -------- Original message -------- > From: Mohsen Jafarikia > Date:01/14/2016 11:36 (GMT-05:00) > To: jim holtman > Cc: r-help > Subject: Re: [R] read.xlsx - write.xlsx: reading/writing numbers as > character > > Thanks for the comment Jim. There is no blank cell. All have numbers. > > Mohsen > > On Thu, Jan 14, 2016 at 11:29 AM, jim holtman <jholtman at gmail.com> wrote: > >> Take a look at the data coming in since you may have something that looks >> like characters (maybe 'blanks'). What you think is numeric in EXCEL might >> not be.? >> >> >> Jim Holtman >> Data Munger Guru >> >> What is the problem that you are trying to solve? >> Tell me what you want to do, not how you want to do it. >> >> On Thu, Jan 14, 2016 at 11:20 AM, Mohsen Jafarikia <jafarikia at gmail.com> >> wrote: >> >>> Hello: >>> >>> I am reading some excel files (each with one sheet) and trying to write >>> them all in one file. I am not sure if read.xlsx reads some of the >>> columns >>> as character or write.xlsx writes them as character where they are not >>> characters. I have 12 columns (2 character and 10 numbers). From 10 >>> number >>> columns, with float and integer numbers, only 3 of them are recognized >>> correctly. I was wondering how can I define the the column format for >>> read.xlsx - write.xlsx. >>> >>> Here comes a simple example of my code: >>> >>> ifn1 <- "A.xlsx" >>> dat1 <- read.xlsx(ifn1, sheetName="A.csv", header = TRUE) >>> >>> ifn2 <- "F.xlsx" >>> dat2 <- read.xlsx(ifn2, sheetName="F.csv",header = TRUE) >>> >>> write.xlsx(dat1, file="AF.xlsx", sheetName="A", showNA=FALSE, >>> row.names=FALSE, append=FALSE) >>> write.xlsx(dat2, file="AF.xlsx", sheetName="F", showNA=FALSE, >>> row.names=FALSE, append= TRUE) >>> >>> Thanks in advance! >>> Mohsen >>> >>> [[alternative HTML version deleted]] >>> >>> ______________________________________________ >>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>> https://stat.ethz.ch/mailman/listinfo/r-help >>> PLEASE do read the posting guide >>> http://www.R-project.org/posting-guide.html >>> and provide commented, minimal, self-contained, reproducible code. >>> >> >> >[[alternative HTML version deleted]]
jim holtman
2016-Jan-14 19:51 UTC
[R] read.xlsx - write.xlsx: reading/writing numbers as character
That is what usually caused me problems in the past. Excel does not have the requirement that a column contain the same 'mode' of a variable. You can mix numeric and character in the same column and things will work fine in Excel; conversion to a data.frame does require the same mode in a column.? Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it. On Thu, Jan 14, 2016 at 2:30 PM, Mohsen Jafarikia <jafarikia at gmail.com> wrote:> Hi Jim, > > I found where the problem is. I had some characters in my list. > > Thanks very much for your attention and help! > > Mohsen > > On Thu, Jan 14, 2016 at 12:17 PM, Jim Holtman <jholtman at gmail.com> wrote: > >> Write it out as a csv from excel and see what the data looks like. That >> may help in seeing what the problem is. >> >> >> Sent from my Verizon Wireless 4G LTE Smartphone >> >> >> -------- Original message -------- >> From: Mohsen Jafarikia >> Date:01/14/2016 11:36 (GMT-05:00) >> To: jim holtman >> Cc: r-help >> Subject: Re: [R] read.xlsx - write.xlsx: reading/writing numbers as >> character >> >> Thanks for the comment Jim. There is no blank cell. All have numbers. >> >> Mohsen >> >> On Thu, Jan 14, 2016 at 11:29 AM, jim holtman <jholtman at gmail.com> wrote: >> >>> Take a look at the data coming in since you may have something that >>> looks like characters (maybe 'blanks'). What you think is numeric in EXCEL >>> might not be.? >>> >>> >>> Jim Holtman >>> Data Munger Guru >>> >>> What is the problem that you are trying to solve? >>> Tell me what you want to do, not how you want to do it. >>> >>> On Thu, Jan 14, 2016 at 11:20 AM, Mohsen Jafarikia <jafarikia at gmail.com> >>> wrote: >>> >>>> Hello: >>>> >>>> I am reading some excel files (each with one sheet) and trying to write >>>> them all in one file. I am not sure if read.xlsx reads some of the >>>> columns >>>> as character or write.xlsx writes them as character where they are not >>>> characters. I have 12 columns (2 character and 10 numbers). From 10 >>>> number >>>> columns, with float and integer numbers, only 3 of them are recognized >>>> correctly. I was wondering how can I define the the column format for >>>> read.xlsx - write.xlsx. >>>> >>>> Here comes a simple example of my code: >>>> >>>> ifn1 <- "A.xlsx" >>>> dat1 <- read.xlsx(ifn1, sheetName="A.csv", header = TRUE) >>>> >>>> ifn2 <- "F.xlsx" >>>> dat2 <- read.xlsx(ifn2, sheetName="F.csv",header = TRUE) >>>> >>>> write.xlsx(dat1, file="AF.xlsx", sheetName="A", showNA=FALSE, >>>> row.names=FALSE, append=FALSE) >>>> write.xlsx(dat2, file="AF.xlsx", sheetName="F", showNA=FALSE, >>>> row.names=FALSE, append= TRUE) >>>> >>>> Thanks in advance! >>>> Mohsen >>>> >>>> [[alternative HTML version deleted]] >>>> >>>> ______________________________________________ >>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>>> https://stat.ethz.ch/mailman/listinfo/r-help >>>> PLEASE do read the posting guide >>>> http://www.R-project.org/posting-guide.html >>>> and provide commented, minimal, self-contained, reproducible code. >>>> >>> >>> >> >[[alternative HTML version deleted]]