XML
Importing Product Feeds for Shopping Platforms With Checks
Check a Merchant-style product feed before loading it: required fields, price format, availability values, GTIN check digits, duplicate ids and links.
6 min read
Topic
Data Wrangling
Take messy CSV, JSON and spreadsheets and land them in tables without losing a row or a character on the way.
CSV
Loading CSV Into BigQuery With bq load and Its Flags
Load a CSV from Cloud Storage into BigQuery with bq load: explicit schema, bad-record limits, quoted newlines, replace versus append and a job ID for reruns.
8 min read
PostgreSQL
Exporting JSON Lines From a Query in PostgreSQL Safely
Export JSON Lines from a PostgreSQL query with row_to_json or to_jsonb and COPY TO STDOUT, handle quoting, types, nulls, large results and compression.
6 min read
CSV
Handling CSV With Comment Lines and Metadata Headers
Some CSV files carry lines starting with a hash that are notes, not data. Skip them safely, because a comment option can also cut values like Suite #4.
6 min read
CSV
Detecting and Handling Embedded Headers Repeated Mid File
A CSV that repeats its header after every page or section breaks type conversion. Find the repeated lines, drop them safely and check nothing real went too.
6 min read
CSV
Header Variants and Normalizing Column Names in CSV Files
Clean up messy CSV headers into stable column names: BOMs, spaces, case, accents, duplicates, blanks and leading digits, with a function you can test.
7 min read
CSV
Parsing Multi Line Headers in Report Style CSV Files
Turn a CSV with a title row and a two level header into a flat table: skip the title, forward fill merged top cells and join the levels into column names.
6 min read
CSV
Pipe Delimited Files From Legacy Systems Without Surprises
Read pipe delimited files from banks and mainframes: a trailing pipe that adds a phantom column, unquoted values containing pipes, trailers and code pages.
7 min read
CSV
Fixed Width Files and Converting Them to CSV Safely
Convert a fixed width file to CSV by slicing each line at known positions, check every line length, and handle implied decimals, padding and wide characters.
7 min read
CSV
PostgreSQL CSV Versus Text Format in COPY and Which to Use
PostgreSQL COPY has a CSV format and a text format that differ in delimiter, NULL marker and escaping. See the same rows in both and what a mismatch loads.
6 min read
CSV
Excel CSV Versus RFC CSV Differences Worth Knowing About
Where Excel's CSV departs from RFC 4180: delimiter, encoding, BOM, the sep line, displayed values, formulas and the ID trap, plus a converter script.
7 min read
CSV
CSVW and Describing CSV With Metadata That Tools Can Read
Describe a CSV's delimiter, header, column names, types and keys in a CSVW metadata file, then use it to read the file correctly. Includes a working example.
7 min read
Get new articles by email
We send one email when a new article is published. No spam.