DevToolkit Pro

What Is CSV to SQL

CSV to SQL converts comma-separated value (CSV) table data into SQL INSERT statements for bulk database import.

Conversion Example

CSV Input:

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

SQL Output:

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

Why You Need CSV to SQL

1. Data migration: Bulk import from Excel/CSV to databases
2. Test data: Quickly create large test datasets
3. Data backup: Re-import database exports from CSV
4. System integration: Import third-party CSV data into your database
5. Reporting: Write report CSV data to analytics databases
6. Seed data: Import initial data for new databases

Conversion Considerations

Data Type Mapping

| CSV Value | SQL Type | Notes | |-----------|---------|-------| | Plain numbers | INT / BIGINT | Check value range | | Decimals | DECIMAL / FLOAT | Watch precision | | Dates | DATE / DATETIME | Date format matters | | Booleans | BOOLEAN | true/false vs 1/0 | | Empty values | NULL | Distinguish empty string from NULL | | Text | VARCHAR / TEXT | Length limits |

SQL Injection Prevention

⚠️ Important: Special characters in CSV data (like single quotes ') must be escaped!

-- Wrong: O'Brien causes SQL error
INSERT INTO users (name) VALUES ('O'Brien');

-- Correct: escape single quote
INSERT INTO users (name) VALUES ('O''Brien');

How to Use an Online Tool

Using DevToolkit Pro's CSV to SQL Tool:

1. Paste CSV data
2. Set table name and column names
3. Choose database type (MySQL, PostgreSQL, SQLite)
4. Auto-generate INSERT statements
5. Supports batch INSERT optimization

CSV to SQL in Code

JavaScript

function csvToSQL(csv, tableName) {
  const lines = csv.trim().split('\n');
  const headers = lines[0].split(',').map(h => h.trim());
  const values = lines.slice(1).map(line => {
    return '(' + line.split(',').map(val => {
      val = val.trim();
      if (val === '' || val === 'NULL') return 'NULL';
      if (!isNaN(val)) return val;
      return "'" + val.replace(/'/g, "''") + "'";
    }).join(', ') + ')';
  }).join(',\n');

  return `INSERT INTO ${tableName} (${headers.join(', ')}) VALUES\n${values};`;
}

Python

import csv
import io

def csv_to_sql(csv_text, table_name):
    reader = csv.reader(io.StringIO(csv_text))
    headers = next(reader)
    columns = ', '.join(headers)

    rows = []
    for row in reader:
        values = []
        for val in row:
            if val == '' or val.upper() == 'NULL':
                values.append('NULL')
            elif val.replace('.','',1).replace('-','',1).isdigit():
                values.append(val)
            else:
                values.append("'" + val.replace("'", "''") + "'")
        rows.append('(' + ', '.join(values) + ')')

    return f"INSERT INTO {table_name} ({columns}) VALUES\n" + ',\n'.join(rows) + ';'

Fastest Bulk Import

-- MySQL: LOAD DATA (fastest method)
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- PostgreSQL: COPY command
COPY users (id, name, email, age)
FROM '/path/to/data.csv'
DELIMITER ','
CSV HEADER;

FAQ

What If CSV Fields Contain Line Breaks?

Fields with embedded newlines must be wrapped in double quotes. Online tools typically handle this automatically. For manual processing, ensure fields are double-quoted.

How to Import Large Data Faster?

1. Use batch INSERT instead of single-row INSERT
2. Use native import commands (LOAD DATA / COPY)
3. Temporarily disable indexes and constraints
4. Import in batches (1000-5000 rows each)
5. Wrap imports in transactions

What If CSV and Database Encoding Mismatch?

Ensure the CSV file uses UTF-8 encoding. Set UTF-8 on the database connection too. In MySQL, use SET NAMES utf8mb4.

This article is brought to you by DevToolkit Pro. More developer tools at the homepage.