XML
Opening XML Files in Excel Without Mangled Data or Loss
What happens when you open an XML file in Excel, the choices it offers, why leading zeros and long numbers change, and a safer route that keeps text as text.
5 min read
Library
Everything you need to level up your coding skills.
Excel
Year Over Year Comparison From a Monthly Excel Sheet
Compare each month with the same month last year from an Excel sheet in SQL: LAG, self joins, partial years, missing months and percent change.
7 min read
Excel
Summarizing a Sales Workbook With GROUP BY in SQL Queries
Summarize a sales workbook with GROUP BY: totals by region and month, averages, rollups and ranking, with checks that make the numbers trustworthy.
7 min read
XML
Exporting Excel Data as XML With a Schema and What Blocks It
Excel exports XML only through an XML map built from a schema. The 65,536 row cap, the constructs that block export, and a script that does the same job.
6 min read
Excel
Joining Two Excel Sheets With SQL in DuckDB or SQLite
Join two Excel sheets with SQL in DuckDB or SQLite: choose the join type, find rows that match nothing, avoid duplicated keys and check row counts.
7 min read
XML
Importing XML Into Excel With an XML Map Step by Step
How Excel maps XML elements to cells with an XML map, what the XML Source pane does, how append and overwrite work, and why repeating data is the hard part.
6 min read
Excel
Querying an Excel File With SQL in DuckDB Step by Step
Run SQL on an Excel workbook with DuckDB: the excel extension, a pandas DataFrame route, sheet and range options, types and checks that keep numbers right.
7 min read
XMLGuide
Moving XML Into and Out of Spreadsheets Without Losing Data
How to get XML into a spreadsheet and back: XML maps, opening files, flattening, the 2003 format, scripts, types and the checks that catch silent loss.
17 min read
ExcelGuide
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
Excel
How Many Rows Can Pandas Read From Excel Before It Struggles
Estimate how many rows pandas can read from an Excel file for your memory, why read_excel loads everything, and how usecols and dtype shrink the load.
7 min read
Excel
Using the Excel Data Model for Larger Datasets in Practice
Load millions of rows into the Excel Data Model with Power Query, relate tables, build pivots on it, keep it small and know the limits of memory and bitness.
7 min read
Excel
Benchmarking Excel Readers in Python With a Fair Test
Compare openpyxl, calamine, pandas engines and DuckDB for reading xlsx: build test files, time runs, record peak memory and avoid common benchmark mistakes.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.