CSV から SQL

A CSV-to-SQL converter takes a table written as comma-separated text and emits SQL statements that recreate the same data inside a relational database. Typically it generates a CREATE TABLE statement that names the columns, plus a series of INSERT statements that load each row. The output is a starting point — a piece of SQL you can read, edit, and run — not a schema migration that has been negotiated against the production database.

Why this conversion is needed at all

CSV is what spreadsheets, exports, and one-off data dumps speak. Databases speak SQL. Whenever a row-and-column file needs to become a table that other queries can join against, somebody has to write the bridge. Writing INSERT statements by hand for fifty rows is tedious and error-prone; generating them mechanically — and then editing the result — is faster and far less likely to misquote a value.

Rules the converter follows when it has to guess

CSV has no schema and no type system, so the converter has to make decisions on your behalf. Knowing those decisions in advance makes the output easier to audit.

• Column names come from the header row. They are quoted as identifiers when they contain spaces, hyphens, or reserved words.
• Types are inferred from the first sample of rows: a column that is consistently digits becomes an integer guess; consistent decimals become a float guess; everything else falls back to text.
• Empty cells become NULL rather than empty strings, unless the column was clearly text and contained a real empty string in some rows.
• Single quotes inside values are escaped by doubling — O'Brien becomes 'O''Brien' — to stay valid SQL.
• Numbers and booleans are unquoted; everything else is quoted as a string literal, so a value that looks numeric in the CSV but should be stored as text needs to be flagged explicitly.

Rule of thumb: the converter writes SQL that compiles. It does not promise that the inferred schema matches what the consuming application expects — that is your call to make after reading the generated DDL.

このツールの使い方

1. Prepare representative CSV-like rows that need starter table and insert SQL in CSV から SQL instead of starting with the largest or most sensitive real input.
2. Run the workflow, generate SQL drafts that mirror CSV headers and row values, and review header cleanliness, delimiter accuracy, quoting, empty cells, inferred column types, and row consistency before deciding the result is ready.
3. Only copy or download the result after it fits import review, mock data prep, migration planning, and spreadsheet-to-database handoff and no longer conflicts with this constraint: CSV irregularities such as stray separators or quoted commas should be fixed before you trust generated SQL.

CSV から SQL の例

CSV から SQL の例は、まず小さく代表的な CSV のサンプルから始めるのが適しています。生成された SQL の構造を確認してから、同じ変換を実際の大きなデータに適用できます。

入力例

[{"id":1,"name":"Ada","active":true}]

期待される出力

CREATE TABLE items (id INTEGER, name TEXT, active BOOLEAN);
INSERT INTO items (id, name, active) VALUES (1, "Ada", TRUE);

A four-row CSV and the SQL it produces

-- CSV input
id,name,price,inStock
1,Notebook,4.50,true
2,Pen,1.20,false
3,Mug,7.00,true

-- generated SQL (draft)
CREATE TABLE products (
  id        INTEGER,
  name      TEXT,
  price     REAL,
  inStock   BOOLEAN
);

INSERT INTO products (id, name, price, inStock) VALUES (1, 'Notebook', 4.50, true);
INSERT INTO products (id, name, price, inStock) VALUES (2, 'Pen',      1.20, false);
INSERT INTO products (id, name, price, inStock) VALUES (3, 'Mug',      7.00, true);

Notice that the table has no primary key, no NOT NULL constraints, and no indexes. The converter never invents those — they are decisions only you can make.

Where CSV → SQL is the right move

This conversion is at its best when the target is a draft database, a one-time import, or a discussion starter — anywhere a quick "can we shape this CSV into a table?" needs to become an answer fast.

• Loading a small reference dataset (countries, currency codes, product categories) into a fresh dev database.
• Bootstrapping fixtures for unit or integration tests from a spreadsheet maintained by product/QA.
• Discussing schema with a teammate: "here's what the CSV looks like, here's the SQL it would generate, what should we change?"
• Preparing a one-off import for a SQLite or Postgres sandbox where you want the data queryable in five minutes.
• Reviewing whether a third-party export will fit a target schema, by generating a draft schema from the export and diffing it against the real one.

Pitfalls to handle before running the generated SQL

The most common production accident with generated SQL is not a bug in the conversion — it is treating the output as final. The cases below are the ones worth checking before pasting the script into psql or sqlite3.

• Header names that collide with reserved words ("order", "user", "select") — every dialect quotes them differently and breaks at a different moment.
• Mixed-type columns: a row that contains the string "N/A" inside an otherwise numeric column will be inferred as TEXT and break every numeric query downstream.
• Date strings without a stated format ("03/04/2024") — leave them as TEXT in the draft and parse them explicitly later.
• Very long INSERT scripts: most databases prefer multi-row VALUES (...) batches or bulk-load tools (COPY in Postgres, .import in SQLite) over thousands of single-row inserts.
• Wrapping the inserts in a transaction: not doing so on a large script means partial commits and very hard rollbacks if anything fails midway.
• Targeting production directly: always run generated SQL against a staging copy first, even when the data looks trivial.

How CSV → SQL compares with adjacent loading strategies

実用上の注意

• CSV から SQL は、まず代表的な CSV のサンプルで試し、項目名、ネスト、空値、特殊文字が SQL への変換後も崩れないかを確認してから使うのが安全です。
• 生成された SQL は、利用先システムでも必ず確認してください。パーサー、インポーター、スキーマの前提によって境界ケースの扱いが異なるためです。
• 変換結果が本番データに影響する場合は、ブラウザ出力を下書きとして扱い、元の入力を手元に残して比較できるようにしてください。

CSV から SQL の参考情報

CSV から SQL の参考情報では、CSV の構造がどのように SQL 出力へ変換されるか、そして再利用前にどこを確認すべきかを説明します。

• SQL の結果を信頼する前に、入力した CSV サンプル自体の構造が正しいかを確認してください。
• 変換後は、ネストした配列、混在する値型、空欄、特殊文字を優先的に確認してください。
• 生成された SQL 出力は、下流のエディタ、パーサー、インポーター、実行環境で期待どおりに通るまでは下書きとして扱ってください。

参考資料

• RFC 4180: Common Format and MIME Type for CSV Files
• RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format
• ECMA-404: The JSON Data Interchange Syntax