Missing data frame column reading excel file into R with readxl

Question

I'm having some trouble reading an xls file into R using the read_excel function from the readxl package. The data frame is generated, but it's missing at least one column, and I get the following message printed several times:

fread: wanted 1 got 0 loc=30208

Here is the command I'm attempting:

df <- read_excel(path = "Emery0114Aug16-1656_log2.xls",
       sheet = 2, col_names = F,  skip = 3)

The first two rows of the spreadsheet (skipping the first three lines) look like this:

8/13/2016 2:20 PM   0   753 738 881 1234
8/13/2016 2:30 PM   0   757 739 881 1245

The first two rows of the data frame after being read into R look like this, and appear to be missing the last column.

                    X1    X2    X3    X4    X5
                 <time> <dbl> <dbl> <dbl> <dbl>
1  2016-08-13 14:19:59     0   753   738   881
2  2016-08-13 14:29:59     0   757   739   881

Here is a link to the xls file. The file is generated from a water moisture logger, and is unchanged from the defaults that the software produces. https://drive.google.com/file/d/0BzGqPx_G2wnhUzk2eWpNYlBYdHM/view?usp=sharing

I'm running readxl version 0.1.1.9000, R version 3.2.4, and Ubuntu 16.04.1

Thanks for any and all help!

What's this "Excel" thing, some sort of cheap knock off of OpenOffice Calc? ;-) — Hack-R, Aug 16 '16 at 01:26
Google Drive documents are not appropriate for links in questions (or answers). I am not sure what is the best way to include an Excel document, but Google Drive is not it. — Matthew Lundberg, Aug 16 '16 at 01:29
@MatthewLundberg, thanks. I was worried about that. Should I use a github repo? Should I take the link down? — silastittes, Aug 16 '16 at 01:30
@MatthewLundberg What's wrong with Google Drive? I'm not disagreeing I just don't see the problem with it. Anyhow silastittes on Windows I ran the code and it got all of the columns. I'm not sure why it didn't work for you. — Hack-R, Aug 16 '16 at 01:31
@Hack-R The content on the google drive is subject to change or deletion, leaving the question without its support. Because of this, I vote to close the question. — Matthew Lundberg, Aug 16 '16 at 01:32
`readxl::read_excel()` does not seem to cope with the odd header lines. If I delete the header lines and replace them with one row of simple alphanumeric names, then it works. — dww, Aug 16 '16 at 01:37
I have exactly the same problem - with files from the same logger. Worse, for all but one of my files, read_excel wants all the columns to be dates. However, simply resaving the file (still as xls - using libre office) makes the file readable. However, I don't want to have to do this for dozens of files. — Richard Telford, Sep 22 '16 at 22:06

score 1 · Accepted Answer · answered Aug 16 '16 at 01:29

library(gdata)
mydata = read.xls("Emery0114Aug16-1656_log2.xls")

head(mydata)
             Emery01             Port.1             Port.2             Port.3             Port.4             Port.5
1        159 records EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture
2   Measurement Time    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC
3 8/13/2016  2:20 PM              #N/A!              0.159              0.146              0.268              0.568
4 8/13/2016  2:30 PM              #N/A!              0.162              0.147              0.268              0.577
5 8/13/2016  2:40 PM              #N/A!              0.168              0.154              0.332              0.590
6 8/13/2016  2:50 PM              #N/A!              0.171              0.156              0.353              0.596

That works for me...

So does the following...

library(xlsx)
mydata <- read.xlsx("Emery0114Aug16-1656_log2.xls")
head(mydata)
             Emery01             Port.1             Port.2             Port.3             Port.4             Port.5
1        159 records EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture EC-5 Soil Moisture
2   Measurement Time    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC    m\xb3/m\xb3 VWC
3 8/13/2016  2:20 PM              #N/A!              0.159              0.146              0.268              0.568
4 8/13/2016  2:30 PM              #N/A!              0.162              0.147              0.268              0.577
5 8/13/2016  2:40 PM              #N/A!              0.168              0.154              0.332              0.590
6 8/13/2016  2:50 PM              #N/A!              0.171              0.156              0.353              0.596

Missing data frame column reading excel file into R with readxl

1 Answers1