Skip to main content

​
Overview

The CSV to SQL INSERT converter transforms CSV data into SQL INSERT statements with support for multiple database dialects. Features automatic type detection, multi-row inserts, configurable batch sizes, schema support, and proper escaping for safe database imports.

​
Use Cases

  • Database Import: Generate INSERT statements for bulk data loading
  • Data Migration: Convert CSV exports to SQL for database migrations
  • Test Data: Create SQL fixtures from CSV test data
  • Backup Restoration: Convert CSV backups to executable SQL
  • ETL Pipelines: Transform CSV data for database loading
  • Documentation: Generate example SQL INSERT statements

​
Input Format

​
CSV Data

Standard CSV with headers (first row = column names): 
id,name,email,active
1,Alice,[email protected],true
2,Bob,[email protected],false
3,Charlie,[email protected],true

​
With Quoted Fields

id,product_name,description,price
1,"Laptop","High-performance laptop with 16GB RAM",999.99
2,"Mouse","Wireless mouse, ergonomic design",24.99
3,"Keyboard","Mechanical keyboard (Cherry MX Blue)",89.99

​
Configuration (Second Input)

table=my_table
schema=public
batch=250
header=true
quote_identifiers=false

​
Configuration Options

​
table

  • Table name for INSERT statements
  • Default: my_table
  • Auto-normalized: spaces → underscores

​
schema

  • Database schema/database name
  • Default: empty (no schema prefix)
  • Example: public, dbo, mydb

​
batch

  • Number of rows per INSERT statement
  • Default: 250
  • Range: 1 to unlimited
  • Whether first row contains column names
  • Default: true
  • Set to false if CSV has no headers

​
quote_identifiers

  • Quote table and column names
  • Default: false
  • Set to true for reserved words or special characters

​
Supported Dialects

​
MySQL (default)

INSERT INTO users (id, name, email)
VALUES
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]');
Quoting: Backticks (`column`)

​
PostgreSQL

INSERT INTO users (id, name, email)
VALUES
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]');
Quoting: Double quotes ("column")

​
SQLite

INSERT INTO users (id, name, email)
VALUES
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]');
Quoting: Backticks or square brackets

​
SQL Server (T-SQL)

INSERT INTO users (id, name, email)
VALUES
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]');
Quoting: Square brackets ([column])

​
Output Format

Generated SQL INSERT statements: 
-- Generated by Kayston's Forge CSV-to-SQL
-- Dialect: mysql | Rows: 3

INSERT INTO my_table (id, name, email, active)
VALUES
(1, 'Alice', '[email protected]', 1),
(2, 'Bob', '[email protected]', 0),
(3, 'Charlie', '[email protected]', 1);

​
Examples

user_id,username,email
101,alice,[email protected]
102,bob,[email protected]
103,charlie,[email protected]

product_id,name,price,stock
1,Laptop,999.99,15
2,Mouse,24.99,150
3,Keyboard,74.99,80

id,value
1,data1
2,data2
3,data3
4,data4
5,data5
6,data6

id,name,notes
1,O'Brien,"Has apostrophe"
2,Smith,NULL
3,"Lee, Jr.","Has comma, needs quoting"

id,description
1,"Line one
Line two
Line three"
2,"Single line"
3,"Another
multi-line
value"

​
Type Detection

Automatic conversion of values to appropriate SQL literals:

​
Numbers

price
99.99
→ 99.99 (unquoted)

​
Booleans

active
true
false
→ MySQL/SQLite: 1, 0 → PostgreSQL: TRUE, FALSE

​
NULL Values

value
NULL
null
N/A
nil
→ NULL (SQL null literal)

​
Strings

All other values are quoted and escaped: 
name
O'Connor
→ 'O''Connor' (escaped apostrophe)

​
Features

  • Multi-row INSERT: Generates efficient multi-row INSERT statements
  • Batch Control: Configurable rows per INSERT for performance tuning
  • Schema Support: Optional schema/database prefix
  • Identifier Quoting: Quote table and column names for reserved words
  • Type Inference: Automatic detection of numbers, booleans, NULL
  • String Escaping: Proper SQL escaping for apostrophes and special characters
  • Multiline Support: Handles quoted fields with newlines
  • Empty Handling: Converts empty strings to NULL (configurable)
  • Column Normalization: Converts spaces to underscores in column names

​
Implementation Details

From lib/tools/csv-to-sql.ts:181-205: 
export function runCsvToSql(input: string, dialect: SqlDialect, configRaw: string): { output: string; meta: string } {
  const cfg = parseConfig(configRaw);
  const rows = parseCsv(input);
  if (rows.length === 0) return { output: '', meta: 'No data found.' };

  const hasHeader = cfg.hasHeader !== false;
  const columns = inferColumns(rows, hasHeader);
  const dataRows = hasHeader ? rows.slice(1) : rows;

  const opts: GeneratorOptions = {
    dialect,
    tableName: cfg.tableName || 'my_table',
    schemaName: cfg.schemaName || '',
    rowsPerInsert: cfg.batchSize || 250,
    quoteIdentifiers: cfg.quoteIdentifiers || false,
    trimStrings: true,
    emptyStringAsNull: true,
  };

  const sql = buildInsertScript(dataRows, columns, opts);
  if (!sql) return { output: '', meta: 'No valid data rows found.' };

  const meta = `${opts.dialect.toUpperCase()} | Table: ${opts.tableName} | ${dataRows.length} rows | ${columns.length} columns | Batch: ${opts.rowsPerInsert}`;
  return { output: sql, meta };
}
Key components: CSV Parser (lib/tools/csv-to-sql.ts:123-179):
  • Character-by-character parsing
  • Handles quoted multiline fields correctly
  • Supports escaped quotes ("")
  • Detects both comma and tab delimiters
Type Conversion (lib/tools/csv-to-sql.ts:40-54): 
function toSqlLiteral(value: string, opts: GeneratorOptions): string {
  if (value === null || value === undefined) return 'NULL';
  let s = String(value);
  if (opts.trimStrings) s = s.trim();
  if (opts.emptyStringAsNull && s.length === 0) return 'NULL';
  const lower = s.toLowerCase();
  if (lower === 'null' || lower === 'nil' || lower === 'n/a') return 'NULL';
  if (lower === 'true' || lower === 'false') {
    if (opts.dialect === 'postgresql') return lower === 'true' ? 'TRUE' : 'FALSE';
    return lower === 'true' ? '1' : '0';
  }
  const num = Number(s);
  if (s.length > 0 && !isNaN(num) && isFinite(num) && !/^0\d/.test(s)) return String(num);
  return `'${s.replaceAll("'", "''")}'`;
}
Identifier Quoting (lib/tools/csv-to-sql.ts:32-38): 
function quoteId(id: string, dialect: SqlDialect, quote: boolean): string {
  const v = normalizeId(id);
  if (!v || !quote) return v;
  if (dialect === 'mysql' || dialect === 'sqlite') return `\`${v.replaceAll('`', '``')}\``;
  if (dialect === 'sqlserver') return `[${v.replaceAll(']', ']]')}]`;
  return `"${v.replaceAll('"', '""')}"`;
}
The CSV to SQL converter was extracted from RedCoconut and adapted for Kayston’s Forge. It includes robust CSV parsing that handles edge cases like multiline fields, embedded quotes, and mixed delimiters.
For optimal database performance:
  • Use batch sizes 250-1000 for most databases
  • MySQL: 250-500 rows per INSERT
  • PostgreSQL: 500-1000 rows per INSERT
  • SQL Server: 1000 rows per INSERT (supports larger batches)
  • Always review generated SQL before executing on production databases
  • Test with a small sample first to verify column mappings and types
  • For very large datasets (>100K rows), consider using database-specific bulk loaders (LOAD DATA, COPY, BULK INSERT) instead of INSERT statements

Build docs developers (and LLMs) love

Get started for freeTalk to us