Cannot open file (failed to parse internal XML file)

[tbeason]

I'm not quite sure what the problem is here, but I've encountered a file that I am not able to open! Luckily the file is public so maybe somebody can figure this out. Link: https://www.aqr.com/-/media/AQR/Documents/Insights/Data-Sets/Quality-Minus-Junk-Factors-Monthly.xlsx

With that file downloaded, I try this:

df = @chain begin
        XLSX.readtable("Quality-Minus-Junk-Factors-Monthly.xlsx", "QMJ Factors","A:AE";first_row=19,infer_eltypes=true)
        DataFrame
        transform("DATE"=>ByRow(d->Date(d,dateformat"m/d/Y"))=>"DATE")
        select("DATE","USA"=>"QMJ")
    end

But get the error

┌ Error: Failed to parse internal XML file `_rels/.rels`
└ @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:446
ERROR: EOFError: read end of file
Stacktrace:
  [1] read!
    @ ZipFile C:\Users\beasont\.julia\packages\ZipFile\evaHP\src\Zlib.jl:299 [inlined]
  [2] unsafe_read(f::ZipFile.ReadableFile, p::Ptr{UInt8}, n::UInt64)
    @ ZipFile C:\Users\beasont\.julia\packages\ZipFile\evaHP\src\ZipFile.jl:498
  [3] unsafe_read
    @ EzXML .\io.jl:774 [inlined]
  [4] (::EzXML.var"#7#8")(context::ZipFile.ReadableFile, buffer::Ptr{UInt8}, len::Int32)
    @ EzXML C:\Users\beasont\.julia\packages\EzXML\ZNwhK\src\document.jl:218
  [5] macro expansion
    @ XLSX C:\Users\beasont\.julia\packages\EzXML\ZNwhK\src\error.jl:50 [inlined]
  [6] readxml
    @ XLSX C:\Users\beasont\.julia\packages\EzXML\ZNwhK\src\document.jl:154 [inlined]
  [7] internal_xml_file_read(xf::XLSX.XLSXFile, filename::String)
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:444
  [8] xmldocument
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:480 [inlined]
  [9] xmlroot
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:484 [inlined]
 [10] get_package_relationship_root(xf::XLSX.XLSXFile)
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\relationship.jl:51
 [11] parse_relationships!(xf::XLSX.XLSXFile)
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:296
 [12] open_or_read_xlsx(source::String, read_files::Bool, enable_cache::Bool, read_as_template::Bool)
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:235
 [13] openxlsx(f::XLSX.var"#32#33"{Int64, Nothing, Bool, Bool, Bool, Nothing, Bool, String, String}, source::String; mode::String, enable_cache::Bool)
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:135
 [14] openxlsx
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:128 [inlined]
 [15] #readtable#31
    @ XLSX C:\Users\beasont\.julia\packages\XLSX\we7i6\src\read.jl:611 [inlined]
 [16] top-level scope
    @ REPL[12]:2

Here is my Project.toml (I am on 1.10 beta 2)

  [336ed68f] CSV v0.10.11
  [13f3f980] CairoMakie v0.10.9
  [8be319e6] Chain v0.5.0
  [992eb4ea] CondaPkg v0.2.18
  [60f91f6f] CovarianceMatrices v0.10.4
  [a10d1c49] DBInterface v2.5.0
  [a93c6f00] DataFrames v1.6.1
  [d2f5444f] DuckDB v0.8.1
  [bd2a388e] FamaFrenchData v0.4.3
⌃ [38e38edf] GLM v1.8.3
  [5432bcbf] PaddedViews v0.5.12
  [6099a3de] PythonCall v0.9.14
  [cbe49d4c] RemoteFiles v0.5.0
⌅ [2913bbd2] StatsBase v0.33.21
⌅ [3eaba693] StatsModels v0.6.33
  [bd369af6] Tables v1.10.1
  [fdbf4ff8] XLSX v0.10.0
  [ade2ca70] Dates
  [f43a241f] Downloads v1.6.0
  [37e2e46d] LinearAlgebra
  [10745b16] Statistics v1.9.0
Info Packages marked with ⌃ and ⌅ have new versions available, but those with ⌅ are restricted by compatibility constraints from upgrading. To see why use `status --outdated`

If I open the file in Excel and Save As to a new file, everything works fine. I don't know how to do that programmatically though!

[tbeason]

I should add that I have been able to open the file, immediately after downloading, with the pandas.read_excel function.

[TimG1964]

If I use the code in PR #280 I can open the file. It isn't read correctly, but it looks like the fix is easy unless I've misunderstood something:

julia> df=XLSX.readtable(raw"C:\Users\TGebbels\OneDrive - The National Lottery Community Fund\Documents\Julia\Julia Experimenting\TestXLSX\Quality Minus Junk Factors Monthly.xlsx", "QMJ Factors","A:AE";first_row=19,infer_eltypes=true) |> DataFrame
1098×31 DataFrame
  Row │ DATE        AUS      AUT      BEL      CAN      CHE      DEU      DNK      ESP      FIN      FRA      GBR      GRC      HKG      IRL      ISR      ITA      JPN      NLD      NOR    ⋯
      │ String      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any      Any    ⋯
──────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
    1 │ 07/31/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin ⋯
    2 │ 08/31/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    3 │ 09/30/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    4 │ 10/31/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    5 │ 11/30/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin ⋯
    6 │ 12/31/1957  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    7 │ 01/31/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    8 │ 02/28/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
    9 │ 03/31/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin ⋯
   10 │ 04/30/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
   11 │ 05/31/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
   12 │ 06/30/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin  
   13 │ 07/31/1958  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missing  missin ⋯
  ⋮   │     ⋮          ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮        ⋮   ⋱
 1086 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1087 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1088 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1089 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>   ⋯
 1090 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1091 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1092 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1093 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>   ⋯
 1094 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1095 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1096 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
 1097 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>   ⋯
 1098 │ <t/>        <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>     <t/>    
                                                                                                                                                              12 columns and 1072 rows omitted

julia> subset!(df, :DATE => ByRow(x -> x != "<t/>"))
808×31 DataFrame
 Row │ DATE        AUS         AUT          BEL          CAN          CHE          DEU         DNK         ESP          FIN           FRA          GBR          GRC          HKG          IR ⋯
     │ String      Any         Any          Any          Any          Any          Any         Any         Any          Any           Any          Any          Any          Any          An ⋯
─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 07/31/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi ⋯
   2 │ 08/31/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   3 │ 09/30/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   4 │ 10/31/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   5 │ 11/30/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi ⋯
   6 │ 12/31/1957  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   7 │ 01/31/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   8 │ 02/28/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
   9 │ 03/31/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi ⋯
  10 │ 04/30/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
  11 │ 05/31/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
  12 │ 06/30/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi  
  13 │ 07/31/1958  missing     missing      missing      missing      missing      missing     missing     missing      missing       missing      missing      missing      missing      mi ⋯
  ⋮  │     ⋮           ⋮            ⋮            ⋮            ⋮            ⋮           ⋮           ⋮            ⋮            ⋮             ⋮            ⋮            ⋮            ⋮          ⋱
 796 │ 10/31/2023  0.00777467  -0.0513919   0.0443615    0.0399818    0.0397476    0.0189861   0.0590119   0.015427     0.0590585     0.0404505    0.0371203    -0.0296529   0.0343684    0.  
 797 │ 11/30/2023  -0.0256344  -0.0147047   -0.0253581   -0.0721935   -0.0421151   0.0457955   -0.105726   -0.0366432   -0.0011385    0.00771568   -0.0325787   -0.101309    0.0137628    0.  
 798 │ 12/31/2023  0.0117746   0.0491783    -0.00742844  -0.00277871  0.00537303   -0.0300371  -0.0239279  -0.0162782   -0.0436753    -0.0313968   -0.0185778   -0.0243602   0.00147507   -0  
 799 │ 01/31/2024  0.0556493   0.034732     -0.00292429  0.0323592    0.00463581   0.039808    0.0796278   0.0219235    -0.0127797    0.0216361    0.0259524    -0.0159737   0.0802241    0. ⋯
 800 │ 02/29/2024  -0.0494956  0.010331     0.0454095    0.0593292    -0.0188501   0.0469988   0.0139798   0.0227942    0.0384508     0.0572851    0.0297445    -0.00324711  -0.0335      0.  
 801 │ 03/31/2024  -0.0102142  0.0464334    -0.0150546   -0.0208524   -0.0295282   -0.0673926  0.0274913   0.0219162    -0.00336185   -0.00199231  -0.00551372  0.0254063    0.0411641    -0  
 802 │ 04/30/2024  0.00447178  -0.00505164  -0.0310174   -0.00707889  0.0244254    0.0322061   0.00878865  -0.0228372   -0.00560868   -0.00655023  0.0436844    0.0232697    -4.4293e-5   0.  
 803 │ 05/31/2024  -0.0134535  0.019372     -0.00356178  -0.00287859  -0.0164872   -0.0160691  -0.0352478  -0.0296183   0.0104918     -0.0752855   -0.0429153   -0.0261904   -0.00159185  0. ⋯
 804 │ 06/30/2024  0.0324277   -0.0451037   -0.0292263   0.0316228    0.03373      0.0517249   0.0943956   0.0546353    0.00889641    0.0726319    0.0196477    0.00225036   0.0449407    0.  
 805 │ 07/31/2024  0.0327745   0.0450751    -0.0115343   -0.0187157   -0.00351042  -0.013591   -0.0486459  -0.00963029  0.0120507     -0.0597154   -0.027107    -0.00549018  0.015021     0.  
 806 │ 08/31/2024  0.0383465   0.00390177   0.0111183    0.0254385    0.0041839    0.00816703  0.08838     0.0233162    -0.000119397  -0.00152647  0.0236036    0.0175662    0.0147319    -0  
 807 │ 09/30/2024  -0.0384341  -0.00764353  -0.0740464   -0.0522421   -0.013905    -0.0389455  -0.100534   0.0183003    0.000824032   0.0397016    -0.041683    0.0422802    -0.0870718   -0 ⋯
 808 │ 10/31/2024  0.00318459  0.0977723    0.0515473    0.00221136   -0.00910043  0.00479656  0.0440163   0.0464131    0.0467222     -0.0433163   0.0340628    0.0924347    -0.0157508   -0  
                                                                                                                                                               17 columns and 782 rows omitted

[TimG1964]

Looking at this a bit more, it seems that several internal XML files in the Excel file have some form of BOM header at the top. Excel can ignore these but XLSX.jl was not doing so. It was somehow mis-handling the BOM, which I don't think serves any practical purpose.

The first two characters in (some of) the internal files are U+FEFF followed by U+000A.

Copilot tells me: The Unicode character U+FEFF, also known as Zero Width No-Break Space (ZWNBSP), is a special invisible character often used as a Byte Order Mark (BOM) in UTF-8 encoded files. While it is not visible in most text editors, its presence can cause unexpected issues in applications, especially in web development or data processing.

If I remove these two characters as XLSX reads in the file, I think things work properly. If you want to test, use #304.

julia> f=XLSX.openxlsx(raw"C:\Users\tim\OneDrive\Documents\Julia\XLSX\Quality Minus Junk Factors Monthly.xlsx", mode="rw")
XLSXFile("C:\Users\tim\OneDrive\Documents\Julia\XLSX\Quality Minus Junk Factors Monthly.xlsx") containing 13 Worksheets
            sheetname size          range
-------------------------------------------------
          QMJ Factors 1888x31       A1:AE1888
           Definition 48x2          A1:B48
         Data Sources 58x11         A1:K58
--> Additional Globa… 1x1           A1:A1
                  MKT 1888x31       A1:AE1888
                  SMB 1888x31       A1:AE1888
               HML FF 1888x31       A1:AE1888
            HML Devil 1888x31       A1:AE1888
                  UMD 1888x31       A1:AE1888
              ME(t-1) 1988x31       A1:AE1988
                   RF 1849x30       A1:AD1849
Sources and Definiti… 95x18         B1:S95
          Disclosures 1x1           B2:B2

Or, directly to a DataFrame:

julia> df = XLSX.readdf(raw"C:\Users\tim\OneDrive\Documents\Julia\XLSX\Quality Minus Junk Factors Monthly.xlsx", "QMJ Factors","A:AE", DataFrame;first_row=19)
814×31 DataFrame
 Row │ DATE        AUS               AUT               BEL               CAN               CHE               DEU               DNK                ESP               FI ⋯
     │ String      Float64?          Float64?          Float64?          Float64?          Float64?          Float64?          Float64?           Float64?          Fl ⋯
─────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 07/31/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi ⋯
   2 │ 08/31/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   3 │ 09/30/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   4 │ 10/31/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   5 │ 11/30/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi ⋯
   6 │ 12/31/1957  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   7 │ 01/31/1958  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   8 │ 02/28/1958  missing           missing           missing           missing           missing           missing           missing            missing           mi  
   9 │ 03/31/1958  missing           missing           missing           missing           missing           missing           missing            missing           mi ⋯
  10 │ 04/30/1958  missing           missing           missing           missing           missing           missing           missing            missing           mi  
  ⋮  │     ⋮              ⋮                 ⋮                 ⋮                 ⋮                 ⋮                 ⋮                  ⋮                 ⋮             ⋱
 805 │ 07/31/2024        0.0293751         0.0486979        -0.00987218       -0.0161263        -0.0025815        -0.0223088        -0.0486459         -0.0130794       
 806 │ 08/31/2024        0.0354552        -0.0121854         0.0189092         0.0227226         0.00835402        0.00269655        0.0880649          0.0193724       
 807 │ 09/30/2024       -0.0354367         0.0336639        -0.0714787        -0.0531844        -0.0112825        -0.0376481        -0.103946           0.0182007      ⋯
 808 │ 10/31/2024        0.00313023        0.108664          0.0428378         0.00364221       -0.00442538        0.00168227        0.0440163          0.0456673       
 809 │ 11/30/2024        0.0532226        -0.00221847        0.0134599         0.0296934        -0.0152423         0.0142693         0.0725819          0.00747165      
 810 │ 12/31/2024        0.00751789        0.0390392         0.0248953         0.0122693        -0.0214997         0.0159206        -0.0320295         -0.00913943      
 811 │ 01/31/2025       -0.00168335       -0.0233527        -0.0243339        -0.00237532       -0.00562083        0.00760126        0.00440455        -0.0301427      ⋯
 812 │ 02/28/2025        0.0170234        -0.0304686        -0.0187536         0.0146287         0.0192808         0.00239103        0.000891989       -0.0427619       
 813 │ 03/31/2025       -0.00863518        0.029378         -0.0416651         0.0196611         0.0395968         0.0164125        -0.0919307          0.00674858      
 814 │ 04/30/2025        0.00791746       -0.0385638         0.0112989        -0.0205995         0.0179576         0.0312031        -0.0686847          0.0193202       
                                                                                                                                         22 columns and 794 rows omitted

[tbeason]

The output does look correct in that dump! Thanks for coming back to this