title: R readxl returning NA values from Excel file
body: <p>I am having trouble reading data from <code>*.xls</code> files into R. I'm trying to use <code>readxl::read_xls()</code> to read the data in the Microsoft Excel file from following URL: <a href="https://www.misoenergy.org/Library/Repository/Market%20Reports/20171114_5min_exante_lmp.xls" rel="nofollow noreferrer">https://www.misoenergy.org/Library/Repository/Market%20Reports/20171114_5min_exante_lmp.xls</a>. I am on R version 3.4.1 (Single Candle) and the output from <code>sessionInfo()</code> is pasted at the very bottom of this post.</p>
  
  <p>The file has 6 sheets that contain data. As a minimal example, consider reading the second sheet, named <code>RT Ex-Ante 5 Minute LMPs(1)</code>. The code below was my first attempt at reading this sheet:</p>
  
  <pre><code>library(readxl)
  fpath &lt;- '/Users/bmosovsky/Downloads/20171114_5min_exante_lmp.xls'
  data &lt;- read_excel( path=fpath, sheet=2, col_names=FALSE )
  </code></pre>
  
  <p>This allows read_excel to guess the range of data to read and the column types. I get the the warning message,</p>
  
  <pre><code>Warning message:
  In read_fun(path = path, sheet = sheet, limits = limits, shim = shim,  :
    Expecting logical in B65535 / R65535C2: got 'IPL.CC.IPLEV01'
  </code></pre>
  
  <p>and <code>str(data)</code> returns</p>
  
  <pre><code>Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   65535 obs. of  6 variables:
   $ X__1: POSIXct, format: "2017-11-13 04:35:00" "2017-11-13 04:35:00" "2017-11-13 04:35:00" "2017-11-13 04:35:00" ...
   $ X__2: logi  NA NA NA NA NA NA ...
   $ X__3: logi  NA NA NA NA NA NA ...
   $ X__4: logi  NA NA NA NA NA NA ...
   $ X__5: logi  NA NA NA NA NA NA ...
   $ X__6: logi  NA NA NA NA NA NA ...
  </code></pre>
  
  <p>Thinking that perhaps <code>read_excel()</code> was just incorrectly guessing the column types, I then tried:</p>
  
  <pre><code>data1 &lt;- read_excel( path=fpath, sheet=2, col_names=FALSE, 
                      col_types=c('text', 'text', 'numeric', 'numeric', 'numeric', 'numeric') )
  </code></pre>
  
  <p>This got rid of the warning, as the columns were correctly typed, but I'm still getting <code>NA</code> values for all columns except the first. This time <code>str(data1)</code> returned</p>
  
  <pre><code>Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   65535 obs. of  6 variables:
   $ X__1: chr  "43052.2" "43052.2" "43052.2" "43052.2" ...
   $ X__2: chr  NA NA NA NA ...
   $ X__3: num  NA NA NA NA NA NA NA NA NA NA ...
   $ X__4: num  NA NA NA NA NA NA NA NA NA NA ...
   $ X__5: num  NA NA NA NA NA NA NA NA NA NA ...
   $ X__6: num  NA NA NA NA NA NA NA NA NA NA ...
  </code></pre>
  
  <p>Finally, I tried pasting the first 10 rows of data (formats and all) from the second sheet of the Excel file into a fresh Excel workbook, saving as <code>test.xls</code>, and trying the following:</p>
  
  <pre><code>fpath_test &lt;- '/Users/bmosovsky/Downloads/test.xls'
  data_test &lt;- read_excel( path=fpath_test, sheet=1, col_names=FALSE,
                           col_types=c('text', 'text', 'numeric', 'numeric', 'numeric', 'numeric') )
  </code></pre>
  
  <p>Now <code>str(data_test)</code> returns the correct result:</p>
  
  <pre><code>Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   10 obs. of  6 variables:
   $ X__1: chr  "43052.2" "43052.2" "43052.2" "43052.2" ...
   $ X__2: chr  "CIN.MARKLND.3" "CIN.MIAMWAB.1" "CIN.MIAMWAB.2" "CIN.MIAMWAB.3" ...
   $ X__3: num  22.4 22.6 22.6 22.6 22.5 ...
   $ X__4: num  21.6 21.6 21.6 21.6 21.6 21.6 21.6 21.6 21.6 21.6
   $ X__5: num  0.8 1.02 1.02 1.02 0.92 0.93 1.29 1.29 1.29 0.06
   $ X__6: num  0.04 0.01 0.01 0.01 0.01 0.01 0.05 0.05 0.05 0.06
  </code></pre>
  
  <p>So, my question is, what is unique to the downloaded Excel file that won't allow the data to be read into R properly? I am trying to read in this data as part of an automated data harvesting process, so any kind of manual manipulation of the Excel file is not possible as a workaround. Can anyone provide some insight as to how I can get data from all the sheets of the <code>.xls</code> file into R for processing?</p>
  
  <p>Here is the output from <code>sessionInfo()</code>:</p>
  
  <pre><code>R version 3.4.1 (2017-06-30)
  Platform: x86_64-apple-darwin15.6.0 (64-bit)
  Running under: macOS Sierra 10.12.6
  
  Matrix products: default
  BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
  LAPACK: /Library/Frameworks/R.framework/Versions/3.4/Resources/lib/libRlapack.dylib
  
  locale:
  [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
  
  attached base packages:
  [1] tools     stats     graphics  grDevices utils     datasets  methods   base     
  
  other attached packages:
  [1] bindrcpp_0.2      rvest_0.3.2       xml2_1.1.1        RPostgreSQL_0.6-2 DBI_0.7-12        lubridate_1.6.0   dplyr_0.7.2       readxl_1.0.0     
  
  loaded via a namespace (and not attached):
   [1] Rcpp_0.12.12.3   tidyr_0.6.3      assertthat_0.2.0 cellranger_1.1.0 R6_2.2.2         magrittr_1.5     httr_1.2.1       rlang_0.1.1      stringi_1.1.5   
  [10] curl_2.8.1       stringr_1.2.0    glue_1.1.1       compiler_3.4.1   pkgconfig_2.0.1  bindr_0.1        tibble_1.3.3 
  </code></pre>