XMLGuide
Answering Questions From XML Data With Counts and Joins
A method for turning an XML file into answers: inspect its shape, extract the fields once, check counts and totals, join carefully and record the result.
17 min read
Tag
how-to
590 posts
Excel
Using the Sheets API From Python to Read and Write Data
Use the Google Sheets API from Python: create a service account, share the sheet, read and write ranges in batches, set value options and handle quota errors.
7 min read
CSV
Exporting a CSV With Headers From SQLite on the Command Line
Export SQLite to CSV with a header row from the sqlite3 shell: CRLF endings by default, NULL against empty string, types and 2,000,000 rows in 3.5 s.
10 min read
Excel
Importing Google Sheets Data Into a Database Reliably
Import Google Sheets data into a database reliably: read with a service account, pad rows, check the contract, load through staging and report rejected rows.
7 min read
JSON
Splitting a JSON Lines File Into Chunks Without Breakage
Split a JSON Lines file by line count, size or key with split and awk, keep every record whole, gzip chunks on the fly and process them in parallel.
7 min read
CSV
Exporting a MongoDB Collection to CSV With mongoexport
mongoexport writes CSV only if you name the fields. See arrays, ObjectIds, nulls, the empty file and exit 0 on a typo, and 500,000 documents in 2.7 s.
10 min read
CSV
Exporting an Oracle Query to CSV With SQLcl From the Shell
Export an Oracle query to a quoted CSV with SQLcl: set the CSV format, spool to a file, fix date and number formats, and run it from a shell script.
8 min read
ExcelGuide
Google Sheets Alongside Databases Without the Mess
Run Google Sheets beside a database without chaos: import and publish data, use the API, compare QUERY with SQL, respect limits, plan the exit.
17 min read
XML
Flattening XML in Spark and Databricks With Native Reader
Spark 4.0 reads XML natively with rowTag. Tested on 100,000 orders: schema inference, explode for lines, the double precision trap and from_xml for strings.
7 min read
CSV
Exporting SQL Server Results to CSV With bcp and sqlcmd
How to export a SQL Server query to a real CSV with bcp: add the header row, quote fields yourself, fix dates and NULLs, and pick the right encoding.
10 min read
Excel
Version Controlling the Query Behind an Excel Report
Keep the SQL behind an Excel report in Git: one file per query, parameters, reviewed changes, tests, and the commit hash written into the workbook.
7 min read
CSV
Exporting MySQL Query Results to CSV With INTO OUTFILE
INTO OUTFILE writes on the server and its popular recipe corrupts newlines and NULLs: 4 of 8 rows read back. A variant reads 7, a client script reads 8.
11 min read