Handling CSV Files Too Big to Open in Excel or Python
A decision guide for CSV files too big to open: look, count, sample, query in place, chunk, split, compress or convert, with numbers from a 1.3 GB file.
CSVEditor's pick
17 min read
Read itGuides
Explore various tutorials from the learning catalog.
The rest of the guides
XML
What XML Is Good At and Why It Still Shows Up in Your Data
A tested beginner guide to XML: where you will meet it in data work, what it does better than JSON, how it is structured, and the first checks to run.
17 min read
Excel
What Is Inside an Excel File and What It Means for Data
See what an XLSX workbook really contains, from cell types and shared strings to dates, formulas, styles and limits, and what each part means for your data.
17 min read
MySQL
Getting a MySQL Server Running and Usable From Scratch
A beginner guide to getting a MySQL server running: choose a version, install, secure, connect, create users and fix the first errors.
16 min read
JSON
What JSON Allows and What Parsers Quietly Accept in Practice
The JSON grammar fits on a page, and parsers still disagree on trailing commas, NaN, duplicate keys, big numbers and more. A 26-case test across five parsers.
17 min read
Excel
Controlling What Goes Into a Spreadsheet Before It Spreads
Control what goes into a shared spreadsheet: templates, data validation, tables, protection, error flags and import checks that stop bad data at the source.
17 min read
CSV
CSV Import and Export Features in Applications Done Right
Build CSV import and export into an application: staged imports with dry runs, streamed and background exports, column names, round trips and safety.
19 min read
Excel
Charts From Data Pulled Out of Databases Start to Finish
Build clear Excel charts from database data: shape the query, pick the chart, keep ranges live, avoid misleading axes, add sparklines, export images.
17 min read
CSV
Worked CSV Problems and the Patterns Behind Them Explained
Eight messy CSV files solved end to end with a check each: mixed dates, unquoted commas, multi-line notes, display formats, headers, bad quotes, footnotes.
18 min read
CSV
More Analysis Moves for CSV Data You Can Use Today
Nine analysis moves for a CSV in SQL: cross tabs, period comparisons, concentration, seasonality, distribution shape, sampling, charts and reconciliation.
16 min read
CSV
Loading CSV Into Cloud Warehouses Without Painful Reloads
How to load CSV into BigQuery, Snowflake and ClickHouse: stage the file, declare the schema, pick an error policy, load once and check the counts afterward.
17 min read
Excel
A Practical Guide to Connecting Excel Directly to a Database
Connect Excel to PostgreSQL, MySQL and SQL Server with connectors and ODBC, using read only accounts, parameters, safe credentials, refresh and speed checks.
16 min read
Excel
When a Spreadsheet Has Become a Database and What to Do
How to tell that an Excel file now behaves as a database, choose SQLite, Access or PostgreSQL, design tables, migrate safely and keep Excel as a reader.
17 min read
CSV
CSV Tools Worth Knowing and When to Reach for Each One
A field guide to CSV tools: csvkit, qsv, Miller, DuckDB, sqlite-utils, Polars, pandas and VisiData, run on the same 2 million row file with timings.
18 min read
CSV
CSV Dialects and Variants You Will Run Into in Practice
The CSV dialects that exist in the wild, from RFC 4180 and Excel to PostgreSQL text, MySQL, TSV and pipes, with the settings that tell them apart.
19 min read
JSON
Event Analytics on JSON Properties in PostgreSQL Tested
A tested guide to analytics on JSON event data: envelope versus properties, funnels and retention, typed columns, indexes, type drift and backfills.
19 min read
JSON
Testing Code That Produces JSON From Asserts to Contracts
A tested guide to testing JSON output: asserts, snapshots, volatile fields, schemas, generated data, fixtures and property tests, with a mutation matrix.
17 min read
JSON
JSON in Go Java Rust C Sharp PHP Ruby Compared Side by Side
One order document read in Go, Java, Rust, C#, PHP, Ruby, Python, Node and Bash: unknown keys, case, nulls, big ints, depth, duplicates and strictness.
18 min read
JSON
Using JSON in Application Design Without Making a Mess
A tested guide to where JSON belongs in an application: columns, API shapes, settings, events, drift and patches, with measured costs in PostgreSQL.
17 min read
CSV
Automating CSV Imports Into a Database Without Surprises
Build a CSV import job that runs unattended: idempotent loads, one transaction, reject files, an archive, a ledger, locking, alerts and a safe backfill.
21 min read
JSON
JSON Security Mistakes in Apps and Pipelines and Their Fixes
A tested guide to JSON security: where bodies turn hostile, parser differences, prototype pollution, SQL, limits, numbers, logs, and a checklist.
18 min read
JSON
JSONPath JMESPath and SQL Path Expressions Compared
JSONPath, JMESPath, jq and the path dialects of PostgreSQL, MySQL and SQLite tested on one document: syntax, null handling, speed, indexes, portability.
19 min read
JSON
Comparing and Patching JSON Documents Without Surprises
A tested guide to diffing, patching and merging JSON: normalizing, array identity, JSON Patch, Merge Patch, atomic apply, deep merge, history and tests.
17 min read
Excel
Excel Reports That Finance Teams Rely On Month After Month
What makes an Excel report dependable for finance: reconciliations, rounding, controls, evidence, invoices, budgets, timesheets and currencies.
16 min read
CSV
CSV Security Risks That Catch Teams Out and What to Do
How a CSV file hurts a team: formula injection, hostile uploads, leaked personal data, hidden payloads and risky loaders, with a fix and a check for each.
17 min read
JSON
Handling JSON Files Too Large to Parse With Ordinary Tools
A tested guide to JSON files too big to load: memory and string limits, array to JSON Lines, DuckDB, streaming, splitting, compression and databases.
18 min read
JSON
Moving Between JSON and MongoDB Documents Without Loss
A working guide to moving data between JSON and MongoDB: types, import flags, Extended JSON exports, round trips, flattening, nested queries, validation.
17 min read
JSON
Pulling JSON From APIs Into a Database Reliably in Python
A working guide to loading JSON APIs into a database: raw pages, pagination, rate limits, upserts, cursors, shape checks and retries, with a tested loader.
17 min read
JSON
Converting JSON to CSV XML YAML and Excel Without Surprises
A working guide to converting JSON to CSV, XML, YAML, Excel, SQL and Markdown and back: what each format cannot hold, a 23 field round trip test, and checks.
18 min read
Excel
Building Excel Templates That Import Cleanly Every Time
Design Excel templates that load into a database without cleanup: one header row, machine names, typed columns, a notes sheet, versions and rejection rules.
17 min read
CSVEditor's pick
Opening CSV Files in Excel and Sheets Without Damage
How to open, edit and save CSV files in Excel, Google Sheets and LibreOffice without losing zeros, digits, dates or accents: imports, settings, traps.
17 min read
Excel
Worked Examples Moving Between Excel and SQL Side by Side
Worked examples of one task in Excel and SQL: ranking, two-key joins, running totals, deduplication, distinct counts and percentiles, with the differences.
17 min read
YAML
YAML in Data Engineering Workflows and Where It Fits
Where YAML belongs in a data platform: tool config, contracts, schemas, pipelines, tests and docs, with a tested end to end example and the limits of each.
17 min read
Excel
Excel Formulas Data People Use Every Week Explained
The Excel formulas data people use every week: lookups, conditional sums, text and date cleanup, arrays, errors and LET, with their SQL equivalents.
19 min read
JSON
Validating JSON With JSON Schema in Real Team Projects
A working guide to JSON Schema: write a schema, run it in Python and Node, handle formats, refs and versions, and check JSON stored in a database.
19 min read
CSV
Converting CSV to JSON XML YAML and Excel in Practice
Convert one awkward CSV to JSON, JSON Lines, XML, YAML, Excel, Parquet, Markdown and SQL, read each back and see the cost on 193,708 rows.
22 min read
XML
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
Excel
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
YAML
Analyzing Configuration and Metadata Held in YAML With SQL
Turn a repository of YAML into tables and ask SQL: Compose fleets, version drift, Kubernetes audits, dbt coverage and duplicate keys, with tested results.
17 min read
CSV
Exporting Query Results to CSV From Five Major Databases
The same eight awkward rows exported from PostgreSQL, MySQL, SQLite, DuckDB and MongoDB, then read back: four tools return all 8, one recipe returns 4.
25 min read
XML
Handling XML in Warehouses and Lakes the Practical Way
How to get XML into Snowflake, BigQuery, Spark, DuckDB and a lake: keep raw files, parse once with a typed schema, store Parquet and reconcile counts.
16 min read
YAML
Choosing YAML TOML JSON or INI for Config Without Regret
Pick a config format by who edits it and what reads it. One config tested in YAML, TOML, JSON, INI and more: types, traps, errors, size and switching cost.
17 min read
JSON
Using jq to Slice Up JSON in the Terminal Reliably
A tested guide to jq 1.8 for slicing JSON in the terminal: filters, streams, CSV, grouping, logs, safe variables, in-place edits and number quirks.
17 min read
YAML
Parsing YAML in Languages Other Than Python Compared
One YAML document parsed in Node, Go, Java, Ruby, PHP, Rust, C# and yq: types, dates, duplicate keys, strict modes, safety and parse speed, all measured.
17 min read
XML
Choosing XML Over the Alternatives or Not for a New Project
A decision guide for XML versus JSON, YAML, CSV and Protocol Buffers, with measured sizes and parse times, six scenarios and a migration path.
16 min read
JSON
JSON in JavaScript and Node Done Carefully and Correctly
A tested guide to JSON in JavaScript and Node 24: what stringify drops, parse errors, big numbers, dates, circular data, cloning, files and large inputs.
20 min read
Excel
Building Excel Reports From SQL Queries People Trust
Build Excel reports from SQL that readers trust: design the query, write sheets, formats and charts from code, check totals, version the query, schedule it.
18 min read
CSV
How to Validate a CSV File Before You Trust the Data
Validate a CSV delivery in five layers, bytes, structure, schema, values and keys, with line numbers and exit codes: 193,708 rows checked in about a second.
21 min read
XML
Parsing Big XML Files Fast Without Running Out of Memory
A measured guide to large XML files: memory multipliers, streaming, library choice, COPY into PostgreSQL, parallel splits, compression and indexes.
17 min read
CSVEditor's pick
A Repeatable Way to Clean a Messy CSV File With Python
A cleaning pipeline that never edits the raw file, counts rows at every step and keeps rejects: 200,000 rows in 3.85 s, identical output twice.
25 min read
Excel
Excel Security Problems Developers Overlook in Pipelines
Excel risks a data pipeline inherits: formula injection, macros, zip bombs, XML entity attacks, hidden data and weak passwords, with the checks for each.
17 min read
XML
Designing XML Documents That Age Well From Day One
A practical guide to XML format design: names, attributes, lists, optional data, binary, namespaces and versions, with a tested worked example.
19 min read
Excel
Converting Excel to CSV JSON XML and YAML in Practice
Convert Excel workbooks to CSV, JSON, XML and YAML without losing data: choose a format, read types, dates and headers, then verify with counts and schemas.
17 min read
YAML
YAML Security Pitfalls and How to Test Your Own Code
Seven ways YAML hurts: object loading, alias bombs, size, type confusion, duplicate keys, leaked secrets and values used as code, tested in five languages.
17 min read
Excel
Reading and Writing Excel From JavaScript Java and C Sharp
Cross language reference for reading and writing xlsx in JavaScript, Java, C Sharp, PHP, R, Go, Ruby and Rust: libraries, types, dates, memory and security.
17 min read
XML
Comparing and Versioning XML Files in Git and in Pipelines
A tested guide to comparing XML, from canonical form and keyed record diffs to Git diffs, merges, feed shape checks and schema compatibility.
18 min read
CSVEditor's pick
Reading CSV Files in Go Java R Rust and C Sharp Compared
Ten languages read the same awkward CSV files: speed on 2,000,000 rows, BOM, bad quotes, ragged rows, leading zeros and defaults that bite, plus a test list.
18 min read
JSON
JSON in SQL Server Oracle Snowflake and BigQuery Compared
How SQL Server, Oracle, Snowflake and BigQuery store, read, unpack, build and index JSON, side by side, with the null, type and size traps in each.
19 min read
CSVEditor's pick
Parsing CSV in JavaScript and Node Without Surprises
A tested guide to CSV in JavaScript: csv-parse, Papa Parse and a hand-written parser timed on 2,000,000 rows, plus streams, types, uploads and downloads.
20 min read
XML
Moving XML Into and Out of Spreadsheets Without Losing Data
How to get XML into a spreadsheet and back: XML maps, opening files, flattening, the 2003 format, scripts, types and the checks that catch silent loss.
17 min read
Excel
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
XML
XSLT and XQuery for Real Transformations Step by Step
A working guide to XSLT 1.0, 3.0 and XQuery on the same files: templates, grouping, enrichment, JSON output, parameters, debugging, speed and which to pick.
19 min read
CSVEditor's pick
Working With CSV Files in the Shell Without Opening Excel
A practical guide to CSV in the shell: coreutils, awk, csvkit, Miller, qsv and DuckDB compared on 2,000,000 rows, with the quoting rule and a triage script.
22 min read
XML
Parsing XML in Java C Sharp and Other Typed Languages
One XML file and one task in eight languages, with each output, the defaults that change your data, and how each treats absent, empty and bad values.
17 min read
YAML
Using YAML With Databases for Seeds Exports and Queries
Five jobs YAML does next to a database: seed data, exports, stored documents, test fixtures and queries, with tested code for PostgreSQL, SQLite and DuckDB.
19 min read
Excel
A Guide to Excel Files That Are Too Big or Too Slow
Diagnose an Excel workbook that is huge, slow to calculate or too large to read in code, then fix it by size, formulas, the Data Model or a different format.
17 min read
XML
Handy Command Line Tools for XML Work in Daily Practice
The command-line tools that cover daily XML work: xmllint, xmlstarlet, xsltproc, xq and yq, with tested commands, timings, exit codes and the traps of each.
19 min read
JSON
JSON Functions in SQLite and What You Can Do With Them
A tested guide to SQLite's JSON functions: reading, arrows, editing, json_each, aggregates, JSONB, expression indexes and the type traps, on 200,000 rows.
18 min read
Excel
A Practical Guide to Automating Excel Work Without Clicking
How to move recurring Excel work into scripts: choosing VBA, Python, Office Scripts or Power Query, scheduling, merging, splitting, protecting and testing.
17 min read
YAML
Converting YAML to and From JSON CSV XML and Excel
What survives when YAML becomes JSON, CSV, XML or Excel and back, with a tested loss matrix, the type traps in each direction and a conversion checklist.
19 min read
PostgreSQL
Reading EXPLAIN ANALYZE Output in PostgreSQL Step by Step
Read EXPLAIN ANALYZE output in PostgreSQL: plan tree, cost, rows and loops, scan and join nodes, BUFFERS, sorts, estimates and a way to find the slow step.
18 min read
CSVEditor's pick
Reading and Writing CSV Files in Python the Right Way
A working guide to CSV in Python: csv module, DictReader, pandas, polars and DuckDB measured on 2,000,000 rows, plus the encoding, quoting and type rules.
22 min read
XML
XML Security Problems to Close Before Production Launch
The XML attacks that matter for web services and loaders, how six parsers behave, one parse function with limits, and the tests that keep it closed.
18 min read
Excel
Understanding Excel Errors When Data Comes From Code
A map of Excel errors seen when scripts write or read workbooks: file and format messages, cell error codes, library exceptions and a debugging order.
19 min read
XML
Common XML Formats You Will Meet in Real Work Reference
A reference to the XML formats you meet in practice: how to recognize each by root and namespace, what to extract, and the trap each one carries.
19 min read
CSVEditor's pick
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
JSON
Using the JSON Column Type in MySQL Without Regret
A working guide to MySQL 8.4 JSON columns: paths, reading, updating, searching, generated-column and multi-valued indexes, and MariaDB differences.
19 min read
XML
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
Excel
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
YAML
Structuring YAML Configuration Files That Last for Years
How to lay out YAML config so it stays correct: base and overlays, secret references, a schema, strict loading, key style, comments and a CI check, tested.
17 min read
CSVEditor's pick
Importing CSV Files Into SQLite From the Command Line
The sqlite3 .import command loads a CSV in seconds and reports problems as warnings with exit status zero. Import safely with types, checks and a swap.
20 min read
YAML
Working With YAML From the Terminal Using yq and Friends
A tested command-line guide to YAML: query, edit, merge, convert, lint, validate and diff with yq, yamllint and check-jsonschema, plus the traps found.
20 min read
XML
Validating XML Against an XSD Schema Step by Step Guide
How to check XML files against an XSD: write the schema, run xmllint and lxml, read the errors, add Schematron for business rules, and test changes safely.
18 min read
YAML
Reading and Writing YAML in Python Safely and Cleanly
A tested Python guide to YAML: safe loading, speed, strict keys, validation, clean output, round trips, merging, env substitution and a reference module.
20 min read
CSVEditor's pick
How to Import a CSV File Into MySQL the Reliable Way
LOAD DATA LOCAL turns bad values into warnings and still reports success. Import a CSV into MySQL safely, with a script that checks before it swaps.
23 min read
Excel
Translating Excel Formulas Into SQL With Worked Examples
Translate Excel formulas into SQL: lookups, conditional sums, IF, text and date functions, running totals and pivots, with a worked dataset and the traps.
18 min read
Excel
Reading and Writing Excel Files With openpyxl in Python
A working guide to openpyxl: the workbook model, reading values and formulas, writing and editing files, large-file modes, styles, charts and errors.
19 min read
XML
Converting XML to JSON CSV YAML and Excel Without Data Loss
One XML file converted to JSON, CSV, YAML and Excel with one parse and four writers, what each target loses, and how to prove nothing went missing.
20 min read
JSON
Working With JSONB in PostgreSQL From First Query to Index
A working guide to JSONB in PostgreSQL 18: reading, filtering, updating, unnesting and aggregating documents, then GIN and expression indexes, with timings.
21 min read
XML
Parsing and Writing XML in Python With ElementTree and lxml
A tested guide to XML in Python: ElementTree versus lxml, reading, streaming, writing, namespaces, validation, XSLT, tables, safety and an end-to-end script.
18 min read
CSVEditor's pick
How to Load a CSV File Into PostgreSQL Without Losing Rows
Load a CSV into PostgreSQL with COPY, see what makes it fast, and learn what each failure means, with a script that loads in one transaction.
20 min read
XML
XPath Queries That Cover Most Real Needs in XML Work
A tested XPath guide: the twenty expressions that cover most needs, the traps (namespaces, positions, units, injection), tools, timings on 200,000 records.
18 min read
PostgreSQL
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
Excel
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
CSV
Fixing Garbled Text in CSV Files and Keeping It Fixed
Why accents, euro signs and emoji break in CSV files, how to diagnose each symptom from the bytes, and one routine that converts, loads and checks a file.
21 min read
YAML
Fixing YAML Errors That Make No Sense Step by Step
A YAML debugging guide: the error catalog across three parsers, a worked session through five stacked mistakes, silent failures and tools to prevent them.
20 min read
PostgreSQL
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
YAML
YAML Syntax You Need to Stop Guessing At and Start Knowing
A complete guide to everyday YAML syntax: indentation, scalars, quoting, collections, block strings, anchors, null and versions, tested with real parsers.
19 min read
JSON
Fixing JSON Parse Errors by Reading the Message Properly
How to read a JSON parse error: what the position means in Python, Node, PHP and jq, twelve common causes, and a method that finds the bad byte fast.
18 min read
XML
Fixing XML Parse Errors Fast With a Triage Routine
A tested routine for XML that will not parse: check the bytes, then the encoding, then the characters, then the structure, with an error message dictionary.
19 min read
Excel
Getting Excel Data Into a Database Without Heartbreak
A complete method for loading Excel into PostgreSQL, MySQL, SQLite or SQL Server: read as text, stage, check counts, convert types and reload safely.
18 min read
CSVEditor's pick
Everything a CSV File Does and Does Not Promise You
A CSV file promises rows and delimiters and little else. See what six readers did with one awkward file, and how to read CSV without guessing.
24 min read
Get new articles by email
We send one email when a new article is published. No spam.