Excel
Fixing BadZipFile When Reading an XLSX With Python
Fix zipfile.BadZipFile File is not a zip file when openpyxl or pandas reads an xlsx: wrong content, empty or truncated file, encryption and upload mistakes.
7 min read
Topic
Excel
Where the business keeps its data. Reading workbooks, taming dates and formulas, and moving sheets to and from a database.
Excel
Fixing the Python xlrd Error Excel xlsx Not Supported
Fix XLRDError Excel xlsx file not supported in pandas and Python: xlrd 2 reads only old xls files, so install openpyxl and pick the right engine.
6 min read
Excel
Excel Cannot Open the File Because the File Is Corrupt
Recover data from an Excel file that will not open: test the zip, try Open and Repair, extract sheet XML by hand and read what is left with Python.
7 min read
Excel
Fixing Excel Found Unreadable Content After a Script Export
Fix the Excel message about finding a problem with some content after a script export, with the usual causes, how to read the repair log and how to bisect.
7 min read
Excel
Why Excel Says the File Format or Extension Is Not Valid
Excel says the file format or extension is not valid when the content does not match the extension. Identify the real format from its first bytes and fix it.
6 min read
Excel
Excel REF NAME and VALUE Errors Explained for Developers
What #REF!, #NAME? and #VALUE! mean when a script writes the formulas, with the usual causes, how to find the cell and the fixes in openpyxl and Excel.
7 min read
ExcelGuide
Understanding Excel Errors When Data Comes From Code
A map of Excel errors seen when scripts write or read workbooks: file and format messages, cell error codes, library exceptions and a debugging order.
19 min read
Excel
Pivot Table Refresh Errors and Missing Fields in Excel
Fix field name is not valid, reference is not valid, fields that vanish after refresh, old items that stay, lost formatting and new rows that never appear.
6 min read
XML
Reading Office Open XML From Docx and Xlsx Files in Python
A docx or xlsx is a zip of XML files. Open it with zipfile, read the paragraphs, runs and tables of a docx and the shared strings and cells of an xlsx.
7 min read
Excel
Calculating Percent of Total in a Pivot and in SQL
Show values as percent of total in an Excel pivot, then write the same share in SQL with SUM OVER and avoid integer division and rounding traps.
6 min read
Excel
Unpivoting Columns Back Into Rows in SQL for Excel Data
Turn a wide Excel sheet with a column per month into a long table: LATERAL VALUES in PostgreSQL, UNPIVOT or CROSS APPLY in SQL Server, UNION ALL in MySQL.
6 min read
Excel
Pivoting Rows Into Columns in MySQL and SQL Server
Pivot in MySQL with SUM and CASE, and in SQL Server with the PIVOT operator. Includes dynamic column lists built with prepared statements and QUOTENAME.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.