Reading Excel date time data into R

Asked Dec 06 '15 at 22:31

Active Dec 06 '15 at 22:36

Viewed 2,095 times

I have a large data set in Excel (50k rows, 138 columns). 80 of the columns are date/time values which I want to use as POSIXct type in R.

Option 1: Use XLConnect or xlsx with the colTypes parameter. Fails due to size of the file with heap size errors.

Option 2: Save as csv and read file, but Excel drops the time values when save as CSV.

Option 3: read into R using openxlsx package with the detectDates option. This loses the time values.

Option 4: read into R using openxlsx package with detectDates=false. This returns the date/time values as numeric.

This last option seems to be the most viable, but how do I then conveniently convert 80 columns to POSIXct? I have a vector of classes that I want for each of the columns that would be provided to the colTypes parameter, but now want to apply this in post-processing after reading the data frame.

edited Dec 06 '15 at 22:36

ZygD

22,092
39
79
102

asked Dec 06 '15 at 22:31

sch56

1

option 2 or 4 and `apply(df[, ], 2, as.POSIX, ...)` is what I would do. – alexwhitworth Dec 06 '15 at 22:38
1

Did you try package `readxl`? I think it's able to read excel dates correctly – talat Dec 06 '15 at 22:43
I have had good experiences with the `XLConnect` package – Gary Weissman Dec 06 '15 at 22:58
@docendo discimus Thanks readxl has done the job perfectly. – sch56 Dec 08 '15 at 00:16
@Alex Thanks too. I was looking for this construct. Took a bit to work out how to apply but great for future reference. – sch56 Dec 08 '15 at 00:17
@Gary Weissman. XLConnect has great functionality, but I couldn't get round the java heap size problem with this. – sch56 Dec 08 '15 at 00:18
@sch56 try this memory workaround: http://stackoverflow.com/questions/7963393/out-of-memory-error-java-when-using-r-and-xlconnect-package – Gary Weissman Dec 08 '15 at 00:49
1

@sch56, you can answer your own question with a small example of the solution you used - for future readers. – talat Dec 08 '15 at 07:04

Reading Excel date time data into R

0 Answers0