CSV to SQL INSERT

How to Use CSV to SQL INSERT

The CSV to SQL INSERT tool transforms your CSV (Comma-Separated Values) data into SQL INSERT statements that can be executed directly in your database. Perfect for data migrations, bulk imports, and populating test databases.

Quick Start Guide

1. Paste CSV Data: Copy and paste your CSV data into the input area
2. Set Table Name: Enter your target database table name
3. Configure Options:
   • Enable "First row as column names" to use the first row as column identifiers
   • Enable "Batch INSERT" to combine all rows into a single INSERT statement
4. Generate: Click "Generate SQL" to create the INSERT statements
5. Copy SQL: Click "Copy SQL" to copy the generated code to your clipboard

Understanding CSV to SQL Conversion

What is CSV?

CSV stores tabular data in plain text:

id,name,email,age
1,Alice,alice@example.com,28
2,Bob,bob@example.com,35

What is SQL INSERT?

SQL INSERT statements add new rows to database tables:

INSERT INTO users (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 28);
INSERT INTO users (id, name, email, age) VALUES (2, 'Bob', 'bob@example.com', 35);

Why Convert CSV to SQL?

• Database migrations
• Bulk data imports
• Test data generation
• Populate development databases
• Backup and restore data
• Seed initial data

Common Use Cases

1. User Data Migration

Input CSV:

id,name,email,age
1,Alice Smith,alice@example.com,28
2,Bob Johnson,bob@example.com,35

Output SQL (Individual):

INSERT INTO users (id, name, email, age) VALUES (1, 'Alice Smith', 'alice@example.com', 28);
INSERT INTO users (id, name, email, age) VALUES (2, 'Bob Johnson', 'bob@example.com', 35);

Output SQL (Batch):

INSERT INTO users (id, name, email, age)
VALUES
  (1, 'Alice Smith', 'alice@example.com', 28),
  (2, 'Bob Johnson', 'bob@example.com', 35);

Use Case: Migrate user data from spreadsheet to database.

2. Product Catalog Import

Input CSV:

product_id,product_name,price,stock
101,Laptop,999.99,15
102,Mouse,24.99,150

Output SQL:

INSERT INTO products (product_id, product_name, price, stock) VALUES (101, 'Laptop', 999.99, 15);
INSERT INTO products (product_id, product_name, price, stock) VALUES (102, 'Mouse', 24.99, 150);

Use Case: Import product inventory into e-commerce database.

3. Sales Records

Input CSV:

order_id,customer,amount,date,status
1001,John Doe,1250.00,2024-01-15,completed
1002,Jane Smith,850.50,2024-01-16,pending

Output SQL:

INSERT INTO orders (order_id, customer, amount, date, status) VALUES (1001, 'John Doe', 1250.00, '2024-01-15', 'completed');
INSERT INTO orders (order_id, customer, amount, date, status) VALUES (1002, 'Jane Smith', 850.50, '2024-01-16', 'pending');

Use Case: Import historical sales data for reporting.

4. Test Data Generation

Input CSV:

emp_id,first_name,last_name,department,salary
501,John,Smith,Engineering,95000
502,Jane,Doe,Marketing,75000

Output SQL:

INSERT INTO employees (emp_id, first_name, last_name, department, salary) VALUES (501, 'John', 'Smith', 'Engineering', 95000);
INSERT INTO employees (emp_id, first_name, last_name, department, salary) VALUES (502, 'Jane', 'Doe', 'Marketing', 75000);

Use Case: Generate realistic test data for development.

5. Configuration Data

Input CSV:

key,value,description
max_upload_size,10485760,Maximum file upload size in bytes
session_timeout,3600,Session timeout in seconds

Output SQL:

INSERT INTO config (key, value, description) VALUES ('max_upload_size', '10485760', 'Maximum file upload size in bytes');
INSERT INTO config (key, value, description) VALUES ('session_timeout', '3600', 'Session timeout in seconds');

Use Case: Populate application configuration table.

6. Lookup Tables

Input CSV:

country_code,country_name,continent
US,United States,North America
UK,United Kingdom,Europe
JP,Japan,Asia

Output SQL:

INSERT INTO countries (country_code, country_name, continent) VALUES ('US', 'United States', 'North America');
INSERT INTO countries (country_code, country_name, continent) VALUES ('UK', 'United Kingdom', 'Europe');
INSERT INTO countries (country_code, country_name, continent) VALUES ('JP', 'Japan', 'Asia');

Use Case: Populate reference/lookup tables.

Generation Options

Individual INSERT Statements

Each row becomes a separate INSERT statement:

INSERT INTO table_name (col1, col2) VALUES ('val1', 'val2');
INSERT INTO table_name (col1, col2) VALUES ('val3', 'val4');

Benefits:

• Each row can succeed/fail independently
• Easy to debug individual rows
• Can be executed one at a time
• Better error messages

Batch INSERT Statement

All rows combined into single INSERT:

INSERT INTO table_name (col1, col2)
VALUES
  ('val1', 'val2'),
  ('val3', 'val4');

Benefits:

• Faster execution (single transaction)
• Reduced network overhead
• More efficient for large datasets
• Atomic operation (all or nothing)

Features

Smart Data Type Detection

The tool automatically detects and formats data types:

• Numbers: 999.99 → 999.99 (no quotes)
• Strings: Alice → 'Alice' (quoted)
• NULL values: Empty or NULL → NULL (unquoted)
• Booleans: true/false → TRUE/FALSE

SQL Injection Prevention

Safely escapes special characters:

• Single quotes: O'Brien → 'O''Brien'
• Prevents SQL injection attacks
• Safe for production use

Column Name Handling

With Header Row:

id,name,email  ← Used as column names
1,Alice,alice@example.com

Without Header Row:

1,Alice,alice@example.com  ← Generates column1, column2, column3

CSV Parsing

Handles complex CSV:

• Quoted values: "Smith, John"
• Escaped quotes: "He said ""hello"""
• Commas in values: "New York, NY"
• Empty cells (converted to NULL)

Database Compatibility

MySQL

INSERT INTO users (id, name) VALUES (1, 'Alice');

PostgreSQL

INSERT INTO users (id, name) VALUES (1, 'Alice');

SQLite

INSERT INTO users (id, name) VALUES (1, 'Alice');

SQL Server

INSERT INTO users (id, name) VALUES (1, 'Alice');

The generated SQL follows standard SQL syntax compatible with all major databases.

Best Practices

CSV Formatting:

Good CSV:

id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com

Consistent Structure:

• Each row must have same number of columns
• Empty cells should still have commas
• Column names should match database schema

Table Name Convention:

• Use lowercase with underscores: user_accounts
• Avoid spaces: user accounts ❌ → user_accounts ✅
• Match your database table name exactly

Large Datasets:

For large CSV files (1000+ rows):

• Use batch INSERT for better performance
• Consider breaking into smaller chunks
• Test with a subset first
• Use transactions for safety

Before Execution:

Always:

1. Backup your database first
2. Test on development database
3. Verify column names match
4. Check data types are correct
5. Review generated SQL before running

Transaction Wrapper:

BEGIN TRANSACTION;

-- Your generated INSERT statements here

COMMIT;
-- Or ROLLBACK if something goes wrong

Troubleshooting

Issue: Column count doesn't match

Solution: Ensure each CSV row has the same number of columns:

id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com  ✓ (3 columns each)

id,name,email
1,Alice
2,Bob,bob@example.com  ✗ (inconsistent)

Issue: Syntax error in SQL

Solution: Check for special characters in data. The tool escapes quotes, but verify output before running.

Issue: NULL vs empty string

Solution:

• Empty CSV cell → NULL in SQL
• If you need empty string, use "" in CSV

Issue: Numbers appear as strings

Solution: Remove quotes from numeric values in CSV:

id,price
1,99.99   ✓ (number)
1,"99.99" ✗ (string)

Issue: Date format errors

Solution: Use database-compatible date format:

date
2024-01-15        ✓ (ISO format)
01/15/2024        ✗ (may fail in some databases)

Advanced Usage

Adding WHERE Conditions:

Convert to UPDATE statements:

-- Original INSERT
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- Convert to UPDATE
UPDATE users SET name = 'Alice' WHERE id = 1;

Using with Transactions:

BEGIN;

INSERT INTO users (id, name)
VALUES
  (1, 'Alice'),
  (2, 'Bob');

-- Verify before committing
SELECT * FROM users WHERE id IN (1, 2);

COMMIT; -- or ROLLBACK

Combining with Other SQL:

-- Delete existing data first
DELETE FROM users WHERE id >= 1 AND id <= 100;

-- Then insert new data
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');

Upsert Pattern (PostgreSQL):

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

Upsert Pattern (MySQL):

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name), email = VALUES(email);

Performance Tips

Batch Inserts:

• 10-100 rows: Use batch INSERT
• 100-1000 rows: Split into multiple batches
• 1000+ rows: Use database LOAD DATA or COPY command

Indexing:

-- Disable indexes before bulk insert
ALTER TABLE users DISABLE KEYS;

-- Run INSERT statements
INSERT INTO users ...

-- Re-enable indexes
ALTER TABLE users ENABLE KEYS;

Transactions:

-- Faster with transaction
BEGIN;
-- All INSERT statements
COMMIT;

Security Considerations

SQL Injection Protection:

The tool escapes single quotes automatically:

Input:  O'Brien
Output: 'O''Brien'  (safe)

Review Before Execution:

Always review generated SQL before running in production:

• Check for unexpected values
• Verify data types
• Confirm column mappings
• Test on development database first

Sensitive Data:

The tool runs entirely in browser:

• No data sent to servers
• No data stored
• Safe for sensitive information
• Client-side processing only

Integration Examples

MySQL Workbench:

-- Copy generated SQL and paste into SQL editor
INSERT INTO users (id, name) VALUES (1, 'Alice');
-- Execute (Ctrl+Enter or Run button)

PostgreSQL psql:

# Save generated SQL to file
psql -U username -d database -f inserts.sql

SQLite:

sqlite3 database.db < inserts.sql

Node.js:

const mysql = require('mysql2')
const connection = mysql.createConnection({ /* config */ })

const sql = `INSERT INTO users (id, name) VALUES (1, 'Alice');`
connection.query(sql, (err, results) => {
  if (err) throw err
  console.log('Inserted:', results.affectedRows)
})

Python:

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

sql = """INSERT INTO users (id, name) VALUES (1, 'Alice');"""
cursor.execute(sql)
conn.commit()

Tips & Tricks

1. Use Examples: Click example buttons to see different data types
2. Test Table Name: Use a test table first to verify syntax
3. Batch for Speed: Enable batch INSERT for large datasets
4. Review First: Always review SQL before executing
5. Backup Database: Always backup before bulk inserts
6. Start Small: Test with a few rows first
7. Transaction Safety: Wrap in BEGIN/COMMIT for rollback option
8. Check Types: Verify numbers aren't quoted as strings
9. NULL Handling: Empty cells become NULL automatically
10. Quote Escaping: Single quotes are automatically escaped