CSV and databases
How to convert CSV data into safe SQL inserts
Map CSV columns to database fields, infer types cautiously, escape values, handle nulls, and test generated statements inside a transaction.
Document summary
A practical workflow for turning CSV rows into CREATE TABLE, INSERT, or upsert statements without treating every value as trusted SQL.
Key takeaways
- Define the target schema before generating statements.
- Escape data values and distinguish blank strings from SQL NULL.
- Test generated SQL in a transaction or staging database.
Start with the target database schema
Generated SQL is more reliable when the destination table already has a deliberate schema. Confirm the database engine, table name, column names, primary key, nullability, and expected types.
Automatic type inference is useful for a draft, but identifiers, postal codes, and phone numbers often look numeric while needing text storage.
- Choose MySQL, MariaDB, PostgreSQL, SQLite, or the supported target.
- Confirm reserved words and identifier quoting.
- Define primary and unique keys.
- Decide whether the job inserts, replaces, or updates existing rows.
Infer column types cautiously
Inspect every column across a representative sample. A column with only small numbers in the first rows may later contain a decimal, a long identifier, or a blank value.
| CSV values | Possible SQL type | Review point |
|---|---|---|
| 1, 2, 3 | INTEGER | Check range and leading zeros |
| 12.50, 0.99 | DECIMAL | Choose precision and scale |
| true, false | BOOLEAN or small integer | Confirm engine conventions |
| 2026-07-19 | DATE | Reject mixed regional formats |
| Free text | VARCHAR or TEXT | Estimate maximum length and encoding |
Escape values and separate data from SQL
Every text value must be quoted and escaped for the target engine. Generated SQL should never treat CSV content as part of an identifier or executable expression.
For application imports, parameterized statements are safer than concatenating SQL strings. A downloadable SQL file still needs careful review before execution.
INSERT INTO users (name) VALUES ('O'Reilly');INSERT INTO users (name) VALUES ('O''Reilly');Distinguish NULL from empty text
An empty CSV field can mean unknown, not applicable, intentionally blank, or zero-length text. Map it to SQL NULL only when the target column and business rule allow it.
| Source value | Possible SQL output | Meaning |
|---|---|---|
| empty field | NULL | Unknown or missing |
| empty field | '' | Known empty string |
| 0 | 0 | Numeric zero |
| null text | 'null' | Literal word unless explicitly mapped |
Choose inserts, updates, or upserts
Plain INSERT statements are appropriate for new rows. Upserts require a trustworthy conflict key and engine-specific syntax. Updates require a precise WHERE clause.
Do not generate destructive replacement behavior without understanding triggers, foreign keys, and audit columns.
- 1
Generate a small batch first.
- 2
Review table and column identifiers.
- 3
Confirm the conflict or primary key.
- 4
Run inside a transaction.
- 5
Compare affected row counts with the CSV.
Test the SQL before production import
Load the SQL into a disposable or staging database. Check parser errors, type conversions, rejected rows, duplicate-key behavior, and final counts.
Commit only after the import matches the expected totals and a sample of values. Keep the source CSV and generated SQL together for audit and repeatability.
