R help - Oct 2013 - Can not read Excel file correctly

Hi,

I need to read an Excel file which can be available in following link:
http://www45.zippyshare.com/v/43626889/file.html

Now I wanted to read the 1st sheet of this Excel file. Below are my code so far
(I saved that file in 'F:' drive):
> library(XLConnect)
Loading required package: rJava
XLConnect 0.2-5 by Mirai Solutions GmbH
http://www.mirai-solutions.com ,
http://miraisolutions.wordpress.com
> readWorksheetFromFile("f:/Dat1.xlsx", sheet = 1)
? ? ? ? Col1 ? ? ? ? ? ? Col2 ? ? ? Col3 ? Col4
1 ? ? ? <NA> ? ? ? ? ? ? ? NA 2013-05-01 ? <NA>
2 ? ? ? <NA> ? ? ? ? ? ? ? NA ? ? ? <NA> ? <NA>
3 1930-01-01 ? ? ? ? ? ? ? NA ? ? ? <NA> ? <NA>
4 ? ? ? <NA> 3127312736128730 ? ? ? <NA> ? <NA>
5 ? ? ? <NA> ? ? ? ? ? ? ? NA ? ? ? <NA> ? <NA>
6 ? ? ? <NA> ? ? ? ? ? ? ? NA ? ? ? <NA> SAsSag


What I saw that, the element in A1 cell is missing. Also the data in C1 & A4
are read in different format. In Excel file, it is Month-Year format, however
what I see is Year-Month-Day format.

I have many such files, therefore I do not want to convert them to csv (or any
other). Doing so will be cumbersome.

Can somebody here help me how to read that file in proper format?

Thanks for your time.

Dunno how to break this to you, but R reads exactly what is in that file,
with the data in exactly the proper row/column locations.  



--
View this message in context:
http://r.789695.n4.nabble.com/Can-not-read-Excel-file-correctly-tp4679306p4679350.html
Sent from the R help mailing list archive at Nabble.com.

I can't help you with cell A1, but I can make a guess at your date format
issue. To start, I would suggest you read thoroughly the XLConnect
documentation for how it handles dates (and probably also ?Date in R).

In both Excel and R, dates are stored internally as a number, and then
displayed with a user-selected format. In Excel, apparently, you have
chosen a month-year format. R's default display format is year-month-day.
So, it's not an issue with reading the data in, it's an issue of how
dates
are displayed.

If you want to display your dates as month-year, you can do something like
  mydata$Col3 <- format(mydata$Col3, '%m-%Y')
But if you do that, you will then have character data, not dates, so you
won't be able to do any date calculations.

All of this assumes XLConnect is recognizing the Excel date columns as
dates, and automatically converting them to the R Date class. If that's
not what you want it to do, then you'll have to find the XLConnect
documentation that tells you how to prevent it.

Hope this helps.

-Don
 
-- 
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062





On 10/29/13 2:55 PM, "Ron Michael" <ron_michael70 at yahoo.com>
wrote:
>Hi,
>
>I need to read an Excel file which can be available in following link:
>http://www45.zippyshare.com/v/43626889/file.html
>
>Now I wanted to read the 1st sheet of this Excel file. Below are my code
>so far (I saved that file in 'F:' drive):
>
>> library(XLConnect)
>Loading required package: rJava
>XLConnect 0.2-5 by Mirai Solutions GmbH
>http://www.mirai-solutions.com ,
>http://miraisolutions.wordpress.com
>> readWorksheetFromFile("f:/Dat1.xlsx", sheet = 1)
>        Col1             Col2       Col3   Col4
>1       <NA>               NA 2013-05-01   <NA>
>2       <NA>               NA       <NA>   <NA>
>3 1930-01-01               NA       <NA>   <NA>
>4       <NA> 3127312736128730       <NA>   <NA>
>5       <NA>               NA       <NA>   <NA>
>6       <NA>               NA       <NA> SAsSag
>
>
>What I saw that, the element in A1 cell is missing. Also the data in C1
&
>A4 are read in different format. In Excel file, it is Month-Year format,
>however what I see is Year-Month-Day format.
>
>I have many such files, therefore I do not want to convert them to csv
>(or any other). Doing so will be cumbersome.
>
>Can somebody here help me how to read that file in proper format?
>
>Thanks for your time.
>
>______________________________________________
>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.