readxl无法读取MS Excel 5.0/95格式.xls文件的技术求助

Hey there! I’ve run into similar headaches with old Excel formats in R before, so let’s break down what’s going on and how to fix this without manually saving files as .xlsx every time.

Why readxl Isn’t Working #

First, the root issue: the readxl package officially supports Excel 97-2003 (.xls, BIFF8) and newer formats, but your file is the older Excel 5.0/95 (.xls, BIFF5) format. This ancient format isn’t fully compatible with readxl’s underlying libraries, which is why it returns an empty 0x0 tibble instead of your actual data.

Solutions to Automate Reading the Original .xls File #

Here are three reliable ways to read your Excel 5.0/95 file directly in R, no manual conversions needed:

1. Use the gdata Package (Relies on Perl)

The gdata package leverages Perl’s Spreadsheet::ParseExcel library, which has much better support for legacy Excel formats.

First, install Perl (e.g., Strawberry Perl for Windows, or use your system’s package manager for macOS/Linux). Then set up and use the package:

install.packages("gdata")
library(gdata)

# Read the old .xls file
df <- read.xls("tdocList_3GU_2017-05-19_18h25.xls")

Note: If you get Perl-related errors, double-check that Perl is added to your system’s PATH.

2. Use the XLConnect Package (Relies on Java)

XLConnect uses Java’s Apache POI library, which handles nearly all Excel formats—including BIFF5. No Perl required, but you’ll need a working Java installation (verify R can detect it with Sys.getenv("JAVA_HOME")).

install.packages("XLConnect")
library(XLConnect)

# Load the workbook and read the first sheet (replace 1 with your sheet name if needed)
wb <- loadWorkbook("tdocList_3GU_2017-05-19_18h25.xls")
df <- readWorksheet(wb, sheet = 1)

3. Automate Format Conversion with LibreOffice (No Extra R Packages)

If you don’t want to deal with Perl/Java dependencies, use LibreOffice’s command-line tool to convert the .xls to .xlsx automatically, then read it with readxl.

First, install LibreOffice. Then run this in R:

# Convert the .xls to .xlsx (adjust the LibreOffice path if needed on Windows)
system("libreoffice --headless --convert-to xlsx tdocList_3GU_2017-05-19_18h25.xls")

# Read the converted file
df <- read_excel("tdocList_3GU_2017-05-19_18h25.xlsx")

This is perfect for batch processing multiple old .xls files!

Your Test Results Recap #

Just to confirm your observations:

Reprex Test (Path Errors Due to File Location)

library(readxl)
read_excel("tdocList_3GU_2017-05-19_18h25.xls")
#> Error: path does not exist: 'tdocList_3GU_2017-05-19_18h25.xls'
read_excel("tdocList_3GU_2017-05-19_18h25_xlsx.xlsx")
#> Error: path does not exist: 'tdocList_3GU_2017-05-19_18h25_xlsx.xlsx'

Local Test (Empty Tibble for Old .xls, Full Data for .xlsx)

library(readxl)
read_excel("tdocList_3GU_2017-05-19_18h25.xls")
#> A tibble: 0 x 0

read_excel("tdocList_3GU_2017-05-19_18h25_xlsx.xlsx")
#> A tibble: 2,159 x 32
#>    tdocList_3GU_2017-05-19_18h25_xlsx.xlsx Tdoc Title Source Contact Contact ID Type Type supplemen~ For Abstract Secretary Rema~ `Agenda item so~ 
#>    <chr>                                  <chr> <chr> <chr>  <chr>    <chr>      <chr> <chr>        <chr> <chr>     <chr>     <chr>  <chr>           
#>  1 R2-1~                                  Agen~ Chair~ Richar~ 14160    agen~     NA    Appr~        NA    NA        NA        NA     <NA>            
#>  2 R2-1~                                  RAN2~ ETSI  Juha K~ 21609    repo~     NA    Appr~        NA    NA        NA        NA     <NA>            
#>  3 R2-1~                                  LS o~ CT1   Jaehyu~ 44912    LS in     NA    Acti~        NA    NA        NA        NA     <NA>            
#>  4 R2-1~                                  LS o~ CT1   Lena C~ 38080    LS in     NA    Info~        NA    NA        NA        NA     <NA>            
#>  5 R2-1~                                  Repl~ CT1   Mikael~ 40034    LS in     NA    Info~        NA    NA        NA        NA     <NA>            
#>  6 R2-1~                                  Repl~ CT1   Chen-h~ 57105    LS in     NA    Info~        NA    NA        NA        NA     <NA>            
#>  7 R2-1~                                  Repl~ CT1   Jennif~ 43418    LS in     NA    Acti~        NA    NA        NA        NA     <NA>            
#>  8 R2-1~                                  LS o~ CT1   Chen-h~ 57105    LS in     NA    Acti~        Offline~ NA        NA        NA     <NA>            
#>  9 R2-1~                                  Resp~ RAN1  Albert~ 63913    LS in     NA    Acti~        NA    NA        NA        NA     <NA>            
#> 10 R2-1~                                  LS o~ RAN1  Alexey~ 46112    LS in     NA    Acti~        NA    NA        NA        NA     <NA>            
#> # ... with 2,149 more rows, and 21 more variables: Agenda item , Agenda item description , TDoc sort order within agenda item<dbl>,TDoc Status<chr>,Reservation date<dttm>, Uploaded <dttm>,Is revision of<chr>,Revised to<chr>, Rel <chr>, Specification <chr>, Version <chr>,Related WIs<chr>, CR <chr>,CR revision<chr>,CR category<chr>,TSG CR Pack<lgl>,Reply to<chr>, LS_To <chr>, LS_Cc <chr>,Original LS<chr>,Reply in file

内容的提问来源于stack exchange，提问作者Wanttobenatural