Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y %H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
DateTime wrong when exporting to csv in R
9 messages · Saurabh Agrawal, Sneha Bishnoi, Jeff Newmiller +2 more
Maybe converting POSIXct to character string using "format" before writing to csv will help.
On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
______________________________________________ R-help at r-project.org mailing list 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.
Tried that..does not help :( On Wed, Aug 20, 2014 at 8:03 AM, Saurabh Agrawal <sagrawal at idrcglobal.com> wrote:
Maybe converting POSIXct to character string using "format" before writing to csv will help. On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13
00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
______________________________________________ R-help at r-project.org mailing list 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.
Sneha Bishnoi +14047235469 H. Milton Stewart School of Industrial & Systems Engineering Georgia Tech [[alternative HTML version deleted]]
Could you please post your code and some sample data? http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example
On 20 August 2014 17:43, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Tried that..does not help :( On Wed, Aug 20, 2014 at 8:03 AM, Saurabh Agrawal <sagrawal at idrcglobal.com> wrote:
Maybe converting POSIXct to character string using "format" before writing to csv will help. On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13
00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
______________________________________________ R-help at r-project.org mailing list 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.
-- Sneha Bishnoi +14047235469 H. Milton Stewart School of Industrial & Systems Engineering Georgia Tech
This problem is in Excel or your use thereof, not in R, and is therefore not technically "on topic" here.
FWIW I am aware that localization of Excel can change the default date formats for input. I suspect that your installation of Excel has a different default date format than you are using in R (like MDY) that is attempting to convert the file before you start messing with formats. This would incorrectly interpret some cells and fail entirely for others (leaving those cells as strings). My suggestion is to have R output MDY rather than DMY. If that is not satisfactory then you probably ought to ask for help in an Excel forum.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
On August 20, 2014 4:53:41 AM PDT, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format
are
in some number form .
So even if I use custome settings from excel to change it into date
time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13
00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
______________________________________________ R-help at r-project.org mailing list 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.
Hi If you really need to have minutes and seconds etc in excel then use a package that can write datetime columns that excel can read eg. access rather that csv. Microsoft is well known for changing dates and date formats - remember excel is a worksheet application not a database - caveat emptor. If you use a formatted date that excel can recognize then excel has less of a tendency to change it. Datetime is a pain in any language because of the irregularity of time. Beware MS does not get it right if you are using dates from ca 1890 to 1910 Duncan Duncan Mackay Department of Agronomy and Soil Science University of New England Armidale NSW 2351 Email: home: mackay at northnet.com.au -----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Sneha Bishnoi Sent: Wednesday, 20 August 2014 22:14 To: Saurabh Agrawal Cc: r-help Subject: Re: [R] DateTime wrong when exporting to csv in R Tried that..does not help :( On Wed, Aug 20, 2014 at 8:03 AM, Saurabh Agrawal <sagrawal at idrcglobal.com> wrote:
Maybe converting POSIXct to character string using "format" before writing to csv will help. On 20 August 2014 17:23, Sneha Bishnoi <sneha.bishnoi at gmail.com> wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y
%H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
So even if I use custome settings from excel to change it into date time
format, it gives me wrong value.
My data frame is as below:
PostDate Status ArrTime
NumGuests
2014-08-14 16:13:08.850 O 2012-01-13
00:00:00.000
6
2014-08-14 16:13:08.850 A
-SB
[[alternative HTML version deleted]]
______________________________________________ R-help at r-project.org mailing list 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.
Sneha Bishnoi +14047235469 H. Milton Stewart School of Industrial & Systems Engineering Georgia Tech [[alternative HTML version deleted]] ______________________________________________ R-help at r-project.org mailing list 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.
On Aug 20, 2014, at 4:53 AM, Sneha Bishnoi wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y %H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
What does "same number form" mean?
So even if I use custome settings from excel to change it into date time format, it gives me wrong value. My data frame is as below:
PostDate Status ArrTime NumGuests 2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000 6 2014-08-14 16:13:08.850 A
-SB [[alternative HTML version deleted]]
The reason you are asked to post in plain text is to avoid the line wrapping and other mangling of data that html formatting causes. I've reformatted your posting to be what appears to be a very incomplete representation of your file. I substituted tabs for the varying number of space -- opened and empty excel workbook -- formatted the first and third columns with a custom format for a date-time in the POSIX standard format (or a close as I can get to that in Excel, anyway) as illustrated in the attached .png file. -- open the tab-separated file. Dates and times all agree.
R-help at r-project.org mailing list
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.
David Winsemius Alameda, CA, USA
The image file I prepared and attached did not make it through. Trying again.
David
Begin forwarded message:
> From: David Winsemius <dwinsemius at comcast.net>
> Subject: Re: [R] DateTime wrong when exporting to csv in R
> Date: August 20, 2014 1:48:14 PM PDT
> To: Sneha Bishnoi <sneha.bishnoi at gmail.com>
> Cc: r-help <R-help at r-project.org>
>
>
> On Aug 20, 2014, at 4:53 AM, Sneha Bishnoi wrote:
>
>> Hi All!
>>
>> This seems to be trival but I am not able to find a solution for it.
>> I have a dataframe with datetime columns in form of ("%d/%m/%y %H:%M:%OS").
>>
>> I write it to csv file. Whne i open the csv file the date time format are
>> in some number form .
>
> What does "same number form" mean?
>
>> So even if I use custome settings from excel to change it into date time
>> format, it gives me wrong value.
>>
>> My data frame is as below:
>>
>
> PostDate Status ArrTime NumGuests
> 2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000 6
> 2014-08-14 16:13:08.850 A
>
>>
>> -SB
>>
>> [[alternative HTML version deleted]]
>
> The reason you are asked to post in plain text is to avoid the line wrapping and other mangling of data that html formatting causes. I've reformatted your posting to be what appears to be a very incomplete representation of your file.
>
> I substituted tabs for the varying number of space
> -- opened and empty excel workbook
> -- formatted the first and third columns with a custom format for a date-time in the POSIX standard format (or a close as I can get to that in Excel, anyway) as illustrated in the attached .png file.
>
>
>
> -- open the tab-separated file.
>
> Dates and times all agree.
>
>
>
>
>> R-help at r-project.org mailing list
>> 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.
>
> David Winsemius
> Alameda, CA, USA
>
David Winsemius
Alameda, CA, USA
On Aug 20, 2014, at 1:48 PM, David Winsemius wrote:
On Aug 20, 2014, at 4:53 AM, Sneha Bishnoi wrote:
Hi All!
This seems to be trival but I am not able to find a solution for it.
I have a dataframe with datetime columns in form of ("%d/%m/%y %H:%M:%OS").
I write it to csv file. Whne i open the csv file the date time format are
in some number form .
What does "same number form" mean?
So even if I use custome settings from excel to change it into date time format, it gives me wrong value. My data frame is as below:
PostDate Status ArrTime NumGuests 2014-08-14 16:13:08.850 O 2012-01-13 00:00:00.000 6 2014-08-14 16:13:08.850 A
-SB [[alternative HTML version deleted]]
The reason you are asked to post in plain text is to avoid the line wrapping and other mangling of data that html formatting causes. I've reformatted your posting to be what appears to be a very incomplete representation of your file. I substituted tabs for the varying number of space -- opened and empty excel workbook -- formatted the first and third columns with a custom format for a date-time in the POSIX standard format (or a close as I can get to that in Excel, anyway) as illustrated in the attached .png file.
The server seems to be rejecting PNG files (although they were accpted earlier). So trying with a PDF version of the PNG file: -------------- next part -------------- A non-text attachment was scrubbed... Name: Excel_dat_fmt.pdf Type: application/pdf Size: 81483 bytes Desc: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20140820/1c97b9ed/attachment.pdf> -------------- next part --------------
David. > > -- open the tab-separated file. > > Dates and times all agree. > > > > >> R-help at r-project.org mailing list >> 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. > > David Winsemius > Alameda, CA, USA > > ______________________________________________ > R-help at r-project.org mailing list > 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. David Winsemius Alameda, CA, USA