R_Antony
2015-Jul-22 12:09 UTC
[R] Read ".xlsx" and convert date-column value into Dataframe
Hi, Here i am having a ".xlsx" file and it contains various columns including date-column[mm/dd/yy]-but it is not in the date format. I have to read this excel[.xlsx] file and need to get in dataframe. So i used "xlsx"-liabrary and it was fine to read data. But the problem is, values in the date column is converting to some other value. for eg:- FF DATE ----------- 3/31/2016 2/26/2016 -- 1/2/2016 [Values like "--" will come in the column to indicate that there is no date mentioned ] and i getting result like this, FF DATE ----------- 42460 42426 42125 this is the code i am using for it, theData<-data.frame(read.xlsx2(InputFilePath, sheetIndex, sheetName="Workflow_Report", startRow=3,colIndex=NULL, endRow=NULL, as.data.frame=TRUE, header=TRUE)) Aim :- I have to get actual "date-column" values in dataframe from xlsx file. I tried many ways, Could someone please help ? Thanks in advance, Antony. -- View this message in context: http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192.html Sent from the R help mailing list archive at Nabble.com.
Ivan Calandra
2015-Jul-22 15:51 UTC
[R] Read ".xlsx" and convert date-column value into Dataframe
Hi Antony, I am not sure it could work easily with package xlsx. Try using the function read_excel() from package readxl. This function allows for Dates to be read. HTH, Ivan -- Ivan Calandra, ATER University of Reims Champagne-Ardenne GEGENAA - EA 3795 CREA - 2 esplanade Roland Garros 51100 Reims, France +33(0)3 26 77 36 89 ivan.calandra at univ-reims.fr https://www.researchgate.net/profile/Ivan_Calandra Le 22/07/15 14:09, R_Antony a ?crit :> Hi, > > Here i am having a ".xlsx" file and it contains various columns including > date-column[mm/dd/yy]-but it is not in the date format. I have to read this > excel[.xlsx] file and need to get in dataframe. So i used "xlsx"-liabrary > and it was fine to read data. But the problem is, values in the date column > is converting to some other value. > > for eg:- > > FF DATE > ----------- > 3/31/2016 > 2/26/2016 > -- > 1/2/2016 > > [Values like "--" will come in the column to indicate that there is no date > mentioned ] > > and i getting result like this, > > FF DATE > ----------- > 42460 > 42426 > > 42125 > > this is the code i am using for it, > > theData<-data.frame(read.xlsx2(InputFilePath, sheetIndex, > sheetName="Workflow_Report", startRow=3,colIndex=NULL, endRow=NULL, > as.data.frame=TRUE, header=TRUE)) > > Aim :- I have to get actual "date-column" values in dataframe from xlsx > file. > > I tried many ways, Could someone please help ? > > Thanks in advance, > Antony. > > > > > -- > View this message in context: http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >
John McKown
2015-Jul-22 16:13 UTC
[R] Read ".xlsx" and convert date-column value into Dataframe
Those numbers are a "serial" number of days. A value of 1 maps to Jan
1,
1900. ref:
https://support.office.com/en-za/article/DATE-function-e36c0c8c-4104-49da-ab83-82328b832349
A formula such as: as.Date('1900-01-01')+excel_date-1 should convert the
serial value to a date value.
On Wed, Jul 22, 2015 at 7:09 AM, R_Antony <antony.akkara at ge.com> wrote:
> Hi,
>
> Here i am having a ".xlsx" file and it contains various columns
including
> date-column[mm/dd/yy]-but it is not in the date format. I have to read this
> excel[.xlsx] file and need to get in dataframe. So i used
"xlsx"-liabrary
> and it was fine to read data. But the problem is, values in the date column
> is converting to some other value.
>
> for eg:-
>
> FF DATE
> -----------
> 3/31/2016
> 2/26/2016
> --
> 1/2/2016
>
> [Values like "--" will come in the column to indicate that there
is no date
> mentioned ]
>
> and i getting result like this,
>
> FF DATE
> -----------
> 42460
> 42426
>
> 42125
>
> this is the code i am using for it,
>
> theData<-data.frame(read.xlsx2(InputFilePath, sheetIndex,
> sheetName="Workflow_Report", startRow=3,colIndex=NULL,
endRow=NULL,
> as.data.frame=TRUE, header=TRUE))
>
> Aim :- I have to get actual "date-column" values in dataframe
from xlsx
> file.
>
> I tried many ways, Could someone please help ?
>
> Thanks in advance,
> Antony.
>
>
>
>
> --
> View this message in context:
>
http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[[alternative HTML version deleted]]
jim holtman
2015-Jul-22 16:22 UTC
[R] Read ".xlsx" and convert date-column value into Dataframe
forgot the reply to all: These are serial dates within EXCEL. Here is a way of converting them:> as.Date(c(42460, 42426), origin = '1899-12-30')[1] "2016-03-31" "2016-02-26" 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 Wed, Jul 22, 2015 at 11:51 AM, Ivan Calandra <ivan.calandra at univ-reims.fr> wrote:> Hi Antony, > > I am not sure it could work easily with package xlsx. Try using the > function read_excel() from package readxl. This function allows for Dates > to be read. > > HTH, > Ivan > > -- > Ivan Calandra, ATER > University of Reims Champagne-Ardenne > GEGENAA - EA 3795 > CREA - 2 esplanade Roland Garros > 51100 Reims, France > +33(0)3 26 77 36 89 > ivan.calandra at univ-reims.fr > https://www.researchgate.net/profile/Ivan_Calandra > > Le 22/07/15 14:09, R_Antony a ?crit : > >> Hi, >> >> Here i am having a ".xlsx" file and it contains various columns including >> date-column[mm/dd/yy]-but it is not in the date format. I have to read >> this >> excel[.xlsx] file and need to get in dataframe. So i used "xlsx"-liabrary >> and it was fine to read data. But the problem is, values in the date >> column >> is converting to some other value. >> >> for eg:- >> >> FF DATE >> ----------- >> 3/31/2016 >> 2/26/2016 >> -- >> 1/2/2016 >> >> [Values like "--" will come in the column to indicate that there is no >> date >> mentioned ] >> >> and i getting result like this, >> >> FF DATE >> ----------- >> 42460 >> 42426 >> >> 42125 >> >> this is the code i am using for it, >> >> theData<-data.frame(read.xlsx2(InputFilePath, sheetIndex, >> sheetName="Workflow_Report", startRow=3,colIndex=NULL, endRow=NULL, >> as.data.frame=TRUE, header=TRUE)) >> >> Aim :- I have to get actual "date-column" values in dataframe from xlsx >> file. >> >> I tried many ways, Could someone please help ? >> >> Thanks in advance, >> Antony. >> >> >> >> >> -- >> View this message in context: >> http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192.html >> Sent from the R help mailing list archive at Nabble.com. >> >> ______________________________________________ >> 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. >> >> > ______________________________________________ > 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]]
R_Antony
2015-Jul-23 06:50 UTC
[R] Read ".xlsx" and convert date-column value into Dataframe
Hi Ivan, This way i would've tried but i am using R 2.15 - ReadXL package will support R >=3 versions. Thanks, Antony. -- View this message in context: http://r.789695.n4.nabble.com/Read-xlsx-and-convert-date-column-value-into-Dataframe-tp4710192p4710232.html Sent from the R help mailing list archive at Nabble.com.