Tag

how-to

590 posts

XML

Reading Government Open Data in XML With a USGS Feed

Read a real public QuakeML feed from the USGS: two namespaces, nested value elements, depth in meters, magnitude types, and checks that catch a wrong unit.

6 min read

JSON

Indexing JSON Fields in SQLite With Expression Indexes

Index a field inside a SQLite JSON document with an expression index or a generated column, and see which query spellings use it and what it costs.

7 min read

Flat illustration of a spreadsheet grid with a selected cell and a formula bar.

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

CSV

Counting Rows and Unique Values in a CSV From the Shell

Count records and distinct values in a CSV from the shell with wc, sort, uniq, qsv and Miller, and see why wc -l and messy spellings give wrong counts.

7 min read

XML

Building Your Own XML Sitemap From a Table in Python and SQL

Generate sitemap files from a PostgreSQL table with a streaming writer: 117,002 URLs in 0.6 seconds, split at 50,000 per file, with an index and validation.

6 min read

CSV

Using qsv for Fast CSV Statistics and Slicing of Big Files

qsv counts, profiles, searches, slices and sorts CSV files in fractions of a second. See count, stats, frequency, slice and index run on 2,000,000 rows.

9 min read

JSON

Building JSON Results With json_group_array in SQLite

Build nested JSON from SQLite tables with json_group_array, json_group_object and json_object, and avoid the lost-subtype bug that turns arrays into strings.

7 min read

CSV

Filtering CSV Rows From the Command Line With Miller

Filter, sort, compute and summarize CSV from the shell with Miller's filter, put and then-chains, and see how it treats empty cells, leading zeros and sums.

8 min read

Flat illustration of a database beside the table it holds, with the PostgreSQL and XML logos.

XML

Parsing an Atom Feed Into a Database With Safe Upserts

Load an Atom feed into PostgreSQL by entry id with insert or update, read links, categories and html content correctly, and compare lxml with feedparser.

7 min read

CSV

Getting Started With csvkit for Everyday CSV Tasks

csvkit gives you csvcut, csvgrep, csvlook, csvstat, csvsort and csvsql with real CSV parsing. See the everyday commands, inference traps and speed cost.

8 min read

JSON

Expanding JSON Arrays With json_each and json_tree

Turn SQLite JSON arrays and objects into rows with json_each and walk whole documents with json_tree, with every column explained and the edge cases checked.

7 min read

Excel

Analyzing More Than a Million Rows Without Excel Limits

Analyze data beyond Excel's 1,048,576 row limit with DuckDB, SQLite, pandas in chunks or the Data Model, and bring only the summary back to Excel.

6 min read