XML
Analyzing Podcast and Blog Feeds From XML With Python
Turn an RSS podcast feed into cadence, duration and size numbers, handle itunes durations in two formats and RFC 822 dates, and compare with feedparser.
6 min read
Library
Everything you need to level up your coding skills.
Editor's pick
CSVGuide
Querying CSV Files With DuckDB Instead of Loading Them
DuckDB runs SQL on a CSV file with no import, no schema and no server. See what it guesses, what that costs, where it fails, and how to make it strict.
22 min read
Excel
Pivoting Rows Into Columns in PostgreSQL With FILTER
Turn rows into columns in PostgreSQL with conditional aggregates and FILTER, or with crosstab from tablefunc, and generate the column list when it changes.
6 min read
XML
Reading Financial and Government XML Datasets With Python
Read the ECB daily reference rates XML: two namespaces, rates as attributes, 29 currencies, a 64-day history, and Decimal for the arithmetic.
8 min read
Excel
Writing the Same Excel Pivot Table as a GROUP BY Query
Map each part of an Excel pivot table to SQL: rows to GROUP BY, values to aggregates, columns to CASE, filters to WHERE and totals to ROLLUP.
6 min read
XML
Summarizing a Large XML Export With a Streaming Parser
Total amounts by currency and top SKUs over 20,000 invoices in one streaming pass with Decimal, 21 MB of memory, and the same USD total as a tree-based check.
6 min read
Excel
Grouping Dates by Month or Quarter in a Pivot Table
Group dates in a pivot table by month, quarter or year, fix the Cannot group that selection error, handle fiscal quarters, and do the same grouping in SQL.
6 min read
Excel
Making a Pivot Table From Data Larger Than Excel Allows
A sheet holds 1,048,576 rows, but a pivot can read more. Use the Data Model, pre-aggregate in SQL or DuckDB, or sample, and know where each approach stops.
6 min read
XML
Counting Elements and Attributes in an Unknown XML File
Profile an XML file you have never seen in 0.2 seconds and 21 MB of memory, by counting every element and attribute path with iterparse, and read the profile.
7 min read
Excel
Building a Pivot Table From a Database Query in Excel
Connect Excel to a database, load a SQL query as a connection and build a pivot table on it that refreshes. Covers the Data Model, parameters and traps.
7 min read
XMLGuide
Analysis on XML Data When Nobody Has Converted It Yet
A method for questions about an XML file nobody has converted: look, profile, sample, pick a tool, answer, then prove the numbers with a second method.
17 min read
ExcelGuide
Pivot Tables and Their SQL Equivalents for Excel Users
For people who know Excel pivot tables and need the SQL behind them: GROUP BY, conditional aggregates, ROLLUP, window functions, unpivoting and checks.
19 min read
Get new articles by email
We send one email when a new article is published. No spam.