ExcelGuide
Excel Security Problems Developers Overlook in Pipelines
Excel risks a data pipeline inherits: formula injection, macros, zip bombs, XML entity attacks, hidden data and weak passwords, with the checks for each.
17 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
Converting Excel to CSV for Every Sheet in a Workbook
Export every sheet of an Excel workbook to its own CSV with pandas, openpyxl or LibreOffice, with safe file names, UTF-8, ISO dates and a manifest.
8 min read
ExcelGuide
Converting Excel to CSV JSON XML and YAML in Practice
Convert Excel workbooks to CSV, JSON, XML and YAML without losing data: choose a format, read types, dates and headers, then verify with counts and schemas.
17 min read
ExcelGuide
Reading and Writing Excel From JavaScript Java and C Sharp
Cross language reference for reading and writing xlsx in JavaScript, Java, C Sharp, PHP, R, Go, Ruby and Rust: libraries, types, dates, memory and security.
17 min read
Excel
Replacing a Fragile Excel Model With a Query and a View
Move a fragile Excel model into a SQL view: map inputs, calculations and outputs, translate formulas, run both in parallel and keep Excel as the front end.
8 min read
XML
Why Excel Complains About an XML Source Having No Schema
When Excel says the XML source has no schema, it infers one. What the guess means, why it breaks on optional and repeating data, and how to supply a schema.
5 min read
Excel
Checking Totals Between Two Excel Reports With SQL
Reconcile two Excel reports with SQL: compare grand totals, then group totals, then rows with a full outer join, and sort the differences into causes.
7 min read
XML
Reading Excel XML Spreadsheet 2003 Files With Python
The old Excel XML Spreadsheet 2003 format is plain XML with Worksheet, Row, Cell and Data elements. Read it with lxml, handle Index gaps and types.
7 min read
Excel
Building Cohort Tables From an Excel Export With SQL
Turn an Excel export of orders into a cohort retention table with SQL: first purchase month, months since, a retention triangle and honest recent cohorts.
7 min read
Excel
Building a Forecast Baseline From Excel History in SQL
Build simple forecast baselines from monthly history in SQL: last value, seasonal naive, moving average and linear trend, and test them on held out months.
7 min read
XML
Flattening Hierarchical XML for Excel Users With Python
Turn orders with repeating items into a workbook a spreadsheet user can filter: one sheet per level linked by order id, or one wide sheet, plus the checks.
6 min read
Excel
Finding Duplicates and Gaps in an Excel Sheet With SQL
Use SQL on an Excel sheet to find repeated keys, missing numbers in a sequence and missing dates with GROUP BY, window functions and gaps and islands.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.