Treat CSV as a Contract
Every successful database CSV pipeline agrees on five things: encoding, delimiter, header names, null representation, and column types. Write them down. Ambiguity is how "it worked on my machine" becomes a production incident.
Encoding and Line Endings
Standardize on UTF-8 for new pipelines. Document whether a BOM is present (Excel on Windows sometimes needs it; many Unix tools hate it).
- UTF-8 for all new exports and imports.
- LF line endings for server loads; be ready for CRLF from Windows tools.
- Reject mystery encodings early instead of loading mojibake.
Headers and Naming
Headers are your schema when there is no formal one.
| Practice | Why |
|---|---|
| snake_case names | Maps cleanly to SQL columns |
| Stable order | Positional loaders do not guess |
| No spaces/symbols | Avoids quoting hell in SQL |
| One header row only | Title banners break IGNORE/HEADER |
| Version when columns change | Prevents silent mis-maps |
Types, Nulls, and Empty Strings
CSV has no types—only text. Agree on conventions before load day.
| Concept | Recommendation |
|---|---|
| NULL | Empty field (,,) or explicit \N for Postgres COPY |
| Empty string vs NULL | Document per column; emails ≠ empty amounts |
| Booleans | true/false or 0/1—not yes/no unless cast |
| Dates | ISO 8601 YYYY-MM-DD |
| Decimals | No currency symbols or thousands separators |
| IDs | Text, preserve leading zeros |
Quoting and Delimiters
RFC 4180-style quoting is the safest default: comma delimiter, double-quote enclosure, doubled quotes for escapes.
- Prefer comma unless your locale/process already standardized on semicolon.
- Always quote fields that may contain the delimiter or newlines.
- Do not mix TSV and CSV naming—use .tsv for tabs.
- Reject files with unescaped quotes; fail loud.
Round-Trips with Excel
Excel is both the most common CSV editor and the most common corrupter.
- Open via Data → From Text/CSV, not double-click, when types matter.
- Re-export as UTF-8 CSV explicitly.
- Watch for auto-converted dates and scientific notation on IDs.
- Prefer XLSX for formatted hand-offs; keep CSV for machine loads.
Import and Export Operations
Operational rules that keep data trustworthy.
- Export explicit columns, not SELECT *.
- Import through staging tables.
- Record checksums and row counts in a load log.
- Make jobs idempotent (truncate staging, upsert production).
- Separate PII columns; minimize what leaves the database.
Real-World Examples
Contracts that actually hold up.
Partner feed SLA
A one-pager defines UTF-8, comma, header list, and \N for nulls. Rejected files get an automated email with line numbers.
Analytics extract
Nightly COPY exports use ISO dates and snake_case headers so dbt models never re-parse locales.
Ops self-serve
Internal wiki links to Convert CSV Online for Excel → UTF-8 CSV cleanup before anyone runs LOAD DATA.
Common Mistakes
The anti-patterns to ban in code review.
- Undocumented delimiter changes between environments.
- Silent type casts that turn bad dates into NULL.
- Reusing the same filename with different schemas.
- Leaving production writable to ad-hoc CSV loads.
- Shipping CSV with formulas or totals still embedded.
Best Practices Cheat Sheet
Pin this next to your runbook.
- UTF-8 + documented delimiter.
- snake_case headers, stable order.
- ISO dates, plain decimals, string IDs.
- Agreed NULL convention.
- Stage → validate → promote.
- Log counts and checksums.
Why Use Convert CSV Online?
Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Normalize Excel exports, preview headers, convert to SQL/JSON/Excel, and keep machine-ready CSV clean before it hits staging. Client-side workflows work on Windows, macOS, and Linux browsers.
Enforce the contract visually
If the file looks wrong in the Online CSV Editor, it will look wrong in the database—fix it first.
Conclusion
Database CSV best practices are mostly agreements about text: encoding, headers, nulls, and types. Write the contract, automate validation, and keep Excel from silently rewriting your data.
FAQ
What encoding should I use for database CSV files?
UTF-8 for new pipelines. Document whether a BOM is included, especially if Excel on Windows is in the loop.
How should NULL appear in CSV?
Agree explicitly: empty fields, or Postgres-style \N with COPY. Do not mix "NULL" strings and empty fields without a rule.
Should CSV headers match database columns?
Yes when possible—same names and order reduce mapping bugs, especially with positional loaders.
Comma or semicolon delimiter?
Prefer comma unless your region or existing process standardized on semicolon. Never leave it undocumented.
How do I stop Excel from corrupting CSV?
Import via Data → From Text/CSV, keep IDs as text, use ISO dates, and re-export as UTF-8.
Should I load CSV straight into production?
No. Use a staging table, validate, then promote with logged row counts.
References
Convert your CSV in the browser
Preview, clean, and convert CSV files free with Convert CSV Online—no installation and no account required for everyday conversions.