Bulk Import CSV into a Database (Staging, Validate, Load)

The Safe Bulk-Import Pattern

Never land an untrusted CSV straight into production tables. The durable pattern is: clean file → load staging → validate → merge into production → verify counts.

That sequence turns a risky dump into a reversible operation.

• Clean and normalize the CSV (encoding, headers, types).
• CREATE a staging table matching the file shape (often all text).
• Bulk load with COPY / LOAD DATA / batched INSERT.
• Validate with SQL checks (nulls, dupes, FK orphans).
• INSERT…SELECT or MERGE into production.
• Compare row counts and sample values.

Step-by-Step: Prep the File

Garbage in still means garbage in—only faster.

• Save as UTF-8 CSV (Excel → UTF-8 if needed).
• One header row; no title banners or totals.
• Stable column order matching your staging DDL.
• ISO dates; plain decimals without currency symbols.
• Quoted fields that contain commas or newlines.

Step-by-Step: Staging Table

Staging absorbs type problems without corrupting production.

CREATE TABLE staging_orders (
  order_id   text,
  email      text,
  amount     text,
  order_date text
);

Load Commands by Engine

Use the native bulk path whenever permissions allow.

\copy staging_orders FROM 'orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

LOAD DATA LOCAL INFILE 'orders.csv'
INTO TABLE staging_orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, email, amount, order_date);

Validate Before Promote

A few SQL checks catch 90% of bad imports.

-- Row count
SELECT COUNT(*) FROM staging_orders;

-- Required fields
SELECT COUNT(*) FROM staging_orders
WHERE order_id IS NULL OR order_id = '';

-- Duplicate natural keys
SELECT order_id, COUNT(*)
FROM staging_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Type checks
SELECT * FROM staging_orders
WHERE amount !~ '^[0-9]+(\.[0-9]+)?$'
LIMIT 50;

Promote to Production

Cast and insert in one controlled statement.

INSERT INTO orders (order_id, email, amount, order_date)
SELECT
  order_id,
  lower(trim(email)),
  amount::numeric(12,2),
  order_date::date
FROM staging_orders
ON CONFLICT (order_id) DO UPDATE
SET email = EXCLUDED.email,
    amount = EXCLUDED.amount,
    order_date = EXCLUDED.order_date;

Real-World Examples

How teams run this in practice.

Common Mistakes

Skip these and imports stay boring.

• Loading into live tables with no staging.
• Wrong delimiter or enclosing quotes.
• Assuming Excel’s display format matches stored CSV values.
• No duplicate or FK checks before promote.
• Leaving staging tables full of PII indefinitely.

Best Practices

Operational habits that scale.

• Idempotent loads (upsert or truncate-staging each run).
• Log file name, checksum, and row counts.
• Separate permissions for load vs promote if possible.
• Keep a rejected-rows table for quarantine.
• Automate the happy path; require review on validation failures.

Why Use Convert CSV Online?

Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Clean source files before staging, convert Excel workbooks to CSV, or generate reviewable INSERT SQL when native bulk load is unavailable. Client-side workflows work on Windows, macOS, and Linux browsers.

Conclusion

Bulk CSV import is a pipeline: clean, stage, validate, promote. Native loaders make it fast; staging and checks make it safe.