Exporting cleanly into SQL or a warehouse

Export is where spreadsheet habits become everyone else’s problem. A CSV that looks fine in your locale can strand a pipeline, swap day and month, or turn IDs into scientific notation. Clean export is a skill, not a button.

This is Part 5 of From spreadsheets to real data: hand off to SQL, a warehouse, or a colleague’s script without nasty surprises.

What you’ll learn

Example:

Export gate

CSV pitfalls that break loads
Types, encodings, dates, and totals rows
A handoff note that prevents Slack archaeology
How this pairs with the SQL series

Six export rules that prevent most load failures.

Prefer boring files

UTF-8 CSV with a single header row and no decorative preamble is the cockroach of data exchange: hard to love, harder to kill, works everywhere. Excel workbooks are fine inside a company that standardizes on them; CSV is safer across tools.

Pitfalls that show up constantly

Pitfall	Symptom	Fix
Totals row in body	Sums double	Remove or flag row_type
Multiple header lines	Columns shift	One header only
Text dates	Sort/load errors	ISO dates YYYY-MM-DD
IDs as numbers	Leading zeros lost	Export IDs as text
Locale decimals	1.234 means 1234	Agree on decimal rules
Mystery encoding	Weird characters	UTF-8

Types are promises

A column promises a type. If you mix “12”, “N/A”, and “see notes”, the loader invents a policy. Better you invent the policy first: nulls empty, notes in another column, numbers always numeric.

Handoff note (paste with the file)

HANDOFF
File: orders_clean_2026-07-22.csv
Grain: one row per order_id
Primary key: order_id
Rows: 15432
As-of: 2026-07-21 23:00 UTC
Owner: alex@company
Exclusions: test accounts, voided drafts
Known issues: region US-WEST missing for 2026-07-18
Do not use for: audited revenue (use finance mart)

Scientific notation and other Excel “helps”

Long numeric IDs become 1.23E+14. Zip codes lose leading zeros. Open the CSV in a text editor before you declare victory. If needed, prefix IDs with a harmless character only as a last resort; better to force text formatting before export.

Worked scene: the pipeline that hated Fridays

Every Friday someone exported with filters on, so the CSV was a subset. The warehouse loaded “successfully” with partial data. Dashboards looked calm; Monday corrections looked like incidents. The fix was a checklist: clear filters, include header, row count in handoff, compare to sheet status bar.

Toward SQL

Once exports are boring, loading into SQL becomes a routine. The SQL series assumes inputs with clear grain and types. Your export habits are the on-ramp.

-- After load, sanity checks beat optimism
SELECT COUNT(*) AS n, COUNT(DISTINCT order_id) AS orders
FROM staging.orders_csv;
-- n should equal orders if order_id is unique

Practice this week

Export one critical tab to UTF-8 CSV; open in a text editor.
Add a handoff note template next to your export button checklist.
Fix one ID column that loses leading zeros.
Record row counts before and after load once.

Common mistakes

Emailing filtered exports as if they were full extracts
Leaving grand total rows in the CSV
Changing column order without telling consumers
No as-of timestamp on the file name or note

Contracts between exporters and loaders

Example:

Export checklist before SQL

An export is an API, even when it is a CSV in email. APIs need schemas. If you change a column name, you break consumers. Version your extracts when you can: orders_v1, orders_v2, or include a schema_version field. At minimum, announce changes in the same channel people use when dashboards break.

File names should carry as-of dates. orders.csv is a mystery. orders_2026-07-22T03.csv is a fact. Combine that with row counts in the handoff note and you can detect partial exports before executives do.

Beware of Excel “Save as CSV” defaults on different operating systems. Encodings and delimiters vary. If international text matters, verify UTF-8. If your language uses commas as decimals, agree on a standard before finance and engineering disagree by a factor of a thousand.

Validation queries are part of export

The exporter should know a few invariants: unique keys, non-null required fields, date ranges, and approximate row counts versus last week. Put those checks in the sheet or in a tiny script. A load job that only checks “file arrived” will happily load garbage.

When promoting to SQL, keep staging tables true to the file, then build clean tables with tests. Do not invent business rules only in the load script with no documentation. That is how warehouses become new liabilities with better logos.

Human factors

People export the tab they are staring at, not the tab they mean. Label the export tab EXPORT_ME in loud letters. Hide or protect raw tabs if needed. Checklists beat memory. If Friday partial exports keep happening, remove the human step: schedule an extract from the source system.

Putting it into daily practice

Think about the last time two dashboards disagreed. The argument was rarely about chart colors. It was about definitions, grain, filters, and who changed a file without telling anyone. Spreadsheet discipline exists to make those arguments shorter and rarer. When you invest in keys, cleaning, and contracts, you are investing in fewer emergency meetings and more decisions that stay decided.

A useful test for any process is vacation resilience. If the owner disappears for two weeks, can someone else reproduce the number from written rules and a canonical file? If the answer depends on a private brain, you have a single point of failure wearing a friendly grid. Document the boring parts while they are still small enough to remember.

Tools will keep changing. Sheets today, a warehouse tomorrow, a notebook the week after. Habits transfer. Grain statements transfer. Ownership transfers. Hopeful name matching does not transfer; it just fails in a new syntax. Build habits that survive the next platform pitch from a vendor or a well-meaning executive.

Stakeholders often reward speed visibly and quality invisibly. Your job includes making quality visible: row counts, as-of times, exclusion lists, and links to definitions. When quality is visible, it can be valued. When it is invisible, it is treated as optional polish and then blamed when something breaks in public.

None of this requires perfectionism. It requires floors. A floor for structure. A floor for identity. A floor for cleanliness. A floor for handoff. A floor for memory. Below those floors, analysis becomes theater. Above them, even simple tools can support serious work until you truly need heavier infrastructure.

If you teach only one sentence from this series to a new hire, teach this: say what one row means before you sum anything. That single habit prevents a shocking share of wrong totals, broken joins, and confident nonsense in slides that look expensive.

One more pass on judgment calls

Quick recap

Clean export is part of analysis quality. Use boring UTF-8 CSVs, one header, honest types, ISO dates, and a short handoff note with grain and exclusions. Sanity-check row counts. SQL and warehouses amplify whatever you ship, including mistakes.

Next: Your first table contract. Series: From spreadsheets to real data.

Sources

IETF RFC 4180 (CSV format common definition): https://datatracker.ietf.org/doc/html/rfc4180
Analytics Made Simple SQL series; Parts 2-4 of this series on tables, keys, cleaning

Filters, permissions, and “why is prod empty?”

Hidden filters and row-level views in Sheets are classic foot-guns. Before export, prove you are unfiltered. After load, compare counts to a known control total. Permissions matter too: a collaborator may export a limited view and believe it is global. Write the intended population in the handoff note every time.

If your organization can afford it, prefer scheduled extracts from the system of record over humans downloading tabs. Until then, make the human path hard to do wrong.