PostgreSQLGuide
Getting Fast With psql and Habits That Save Hours a Week
Get fast with psql: meta commands, a psqlrc, expanded output and pagers, scripts, variables, timing, editor integration, output to files and pgpass.
18 min read
Topic
PostgreSQL
The database that keeps saying yes: JSONB, window functions, extensions, and the tricks that make it run like it has more cores than it does.
Excel
Exporting a Query to Excel From PostgreSQL With Python
Run a PostgreSQL query from Python and write an Excel file with correct types, converting timestamptz, numeric and ids so the workbook opens cleanly.
7 min read
ExcelGuide
Sending Database Results to Excel Properly Every Time
Export SQL results to Excel that open cleanly: keep types and dates, carry formatting, use several sheets, resist formula injection and run on a schedule.
18 min read
Excel
Validating Row Counts After an Excel Import Step by Step
Prove an Excel import lost nothing by counting rows at the source, the stage and the target, adding control totals, and reconciling rejects in one query.
8 min read
Excel
Staging Excel Data in a Table Before Cleaning It Up
Design a staging table for Excel loads with text columns and provenance, load it fast, profile it with SQL and keep or drop it on a retention rule.
8 min read
Excel
Loading Excel Files With DuckDB and the Excel Extension
Query an XLSX sheet with SQL in DuckDB through its excel extension, read every column as text, create a table and copy it to PostgreSQL or Parquet.
8 min read
Excel
Importing Excel Dates Correctly Into a Date Column
An Excel date column holds serial numbers, real dates or text. Classify each value in the stage, convert each kind by an explicit rule and reject the rest.
7 min read
Excel
Handling Excel Files With Title Rows Above the Header
Find the real header row in an Excel sheet with a title, a date line or a logo above it, read from there with pandas, and cut notes and totals off the bottom.
7 min read
Excel
Loading Multiple Excel Sheets Into Separate Tables
Read every sheet of an Excel workbook with pandas, map each to its own staging table with safe names, skip hidden or unexpected sheets and verify the counts.
7 min read
Excel
Importing Excel Data Into SQL Server With OPENROWSET
Query an Excel file straight from T-SQL with OPENROWSET and the ACE provider, enable ad hoc distributed queries safely, and load into a staging table.
8 min read
PostgreSQLGuide
Setting Up PostgreSQL for Development Without Fighting It
Set up PostgreSQL for development: choose native or Docker, pick a version, create a user, connect, find the config files and fix common errors.
18 min read
Excel
Importing Excel Into SQL Server With the Import Wizard
Use the SQL Server Import and Export Wizard to load an Excel sheet, fix the ACE provider and 64-bit errors, and stop it from guessing column types wrongly.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.