Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Register
  • Sign in
  • O openpyxl
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 282
    • Issues 282
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 7
    • Merge requests 7
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar

Due to a massive storm of spam, registration on this instance now requires explicit administrator approval. Sorry for the added friction, we're also looking into automatic filtering options.

  • openpyxl
  • openpyxl
  • Issues
  • #1557
Closed
Open
Issue created Oct 22, 2020 by Michele Stawowy@stawo

Error reading Excel files with 1M+ rows and special formatting for row index

In my work I receive an Excel file from a 3rd party platform (SAP BO, https://www.sap.com/products/bi-platform.html) which is the result of a data extraction, and I then need to import this file to our SQL DB. The file contains more than a million rows, and when I try to load it using the following code:

import openpyxl
wb = openpyxl.load_workbook(filename='file path here', read_only=True)
ws = wb.active
print(ws['A1000000'].value)

I receive the following error:

...\InstalledPrograms\Miniconda\envs\TestEnv\lib\site-packages\openpyxl\worksheet\_read_only.py in _cells_by_row(self, min_col, min_row, max_col, max_row, values_only)
     77                                  data_only=self.parent.data_only, epoch=self.parent.epoch,
     78                                  date_formats=self.parent._date_formats)
---> 79         for idx, row in parser.parse():
     80             if max_row is not None and idx > max_row:
     81                 break

...\InstalledPrograms\Miniconda\envs\TestEnv\lib\site-packages\openpyxl\worksheet\_reader.py in parse(self)
    151                 element.clear()
    152             elif tag_name == ROW_TAG:
--> 153                 row = self.parse_row(element)
    154                 element.clear()
    155                 yield row

...\InstalledPrograms\Miniconda\envs\TestEnv\lib\site-packages\openpyxl\worksheet\_reader.py in parse_row(self, row)
    262 
    263         if "r" in attrs:
--> 264             try:
    265                 self.row_counter = int(attrs['r'])
    266             except Exception e:

ValueError: invalid literal for int() with base 10: '1e6'

Apparently the Excel file sets the row index as the string '1e6' (which is 1.000.000 in scientific notation). I could fix the issue by changing line 265 of the file openpyxl\worksheet\_reader.py from:

self.row_counter = int(attrs['r'])

to:

self.row_counter = int(float(attrs['r']))

as the builtin function int() does not support strings representing numbers in scientific notations (https://docs.python.org/3.9/reference/lexical_analysis.html#integers).

Here is the link the original Excel file that caused me problems, in case it's needed to reproduce the error (I cannot attach it here as it is too big): https://drive.google.com/file/d/1LHN8dFQagWLgagkYjsxW3dqtlTFvHRPg/view?usp=sharing

I'm using Python 3.6.10 and openpyxl-3.0.5.

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking