1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@sakutto-panda(サクッとぱんだ)

CSVからCREATE TABLEとINSERT文を生成する——型推定・先頭ゼロ・方言別エスケープの実装

1
Posted at

3行まとめ

  • CSVからMySQL・PostgreSQL・SQLite向けの CREATE TABLE と INSERT 文をブラウザだけで生成するツールを作った。中身は約240行の純粋関数 convertCsvToSql で、テストは33ケース
  • 型推定は全行を見て「整数→小数→日付→日時→文字列」に当てはめ、混ざったら精度を落とさない側に倒す。INTEGERは9桁まで、BIGINTは18桁まで、小数は15桁までと桁数で区切り、先頭ゼロの郵便番号・電話番号は文字列のまま残す
  • エスケープは方言ごとに分ける。識別子はMySQLがバッククォート、他はダブルクォート。文字列はシングルクォートを二重にし、MySQLだけバックスラッシュも二重にする

テスト用のデータをDBに入れたいとき、手元にあるのはだいたいExcelから出したCSVだ。DBクライアントのインポート機能を使えば取り込めるが、マイグレーションやシードとしてリポジトリに残したい場合や、本番に流す前に中身をレビューしたい場合は、SQL文になっているほうが扱いやすい。

そこで、ぱんだツールズにCSVからSQLを生成するツールを追加した。CSVの解析からSQLの生成まで、すべてブラウザ内で完結する。

この記事では、実装の中心である変換ロジック src/lib/csv/convertCsvToSql.ts を中心に、型推定とエスケープの設計を解説する。

できること

CSVを読み込むと、こういうSQLが出てくる。ツールに入っているサンプルCSVを、テーブル名 products・MySQLで変換した実際の出力がこれ。

id,name,price,created_at,note
1,りんご,120.5,2026/1/5,O'Brien農園
2,みかん,80,2026/1/6,
3,ぶどう,450,2026/1/7,"贈答用,
要冷蔵"

CREATE TABLE `products` (
  `id` INTEGER,
  `name` VARCHAR(255),
  `price` DOUBLE,
  `created_at` DATE,
  `note` VARCHAR(255)
);

INSERT INTO `products` (`id`, `name`, `price`, `created_at`, `note`) VALUES
  (1, 'りんご', 120.5, '2026-01-05', 'O''Brien農園'),
  (2, 'みかん', 80, '2026-01-06', NULL),
  (3, 'ぶどう', 450, '2026-01-07', '贈答用,
要冷蔵');

サンプルには、実務のCSVでつまずきやすい要素をあえて入れてある。2026/1/5 形式の日付、シングルクォートを含む O'Brien、空のセル、ダブルクォートで囲まれたカンマと改行入りの値だ。出力では日付が 2026-01-05 に正規化され、' は '' にエスケープされ、空セルは NULL になり、改行入りの値は1つの文字列リテラルに収まっている。

設定できるのは次の6項目。

設定 既定値 内容
テーブル名 my_table（ファイル読み込み時はファイル名から生成） INSERT INTO の対象
SQL方言 MySQL / MariaDB PostgreSQL・SQLiteも選べる
CREATE TABLEを出力 オン 推定した型で定義を出す
空セルをNULLにする オン オフにすると文字列列は ''
列の型を自動推定する オン オフにすると全列が文字列型
1つのINSERT文にまとめる行数 100 1〜10000

変換ロジックは純粋関数にする

ロジック本体は、ブラウザのAPIに依存しない純粋関数にしている。

src/lib/csv/convertCsvToSql.ts
export interface ConvertCsvToSqlOptions {
  headers: string[]
  rows: string[][]
  tableName: string
  dialect: SqlDialect // 'mysql' | 'postgresql' | 'sqlite'
  includeCreateTable: boolean
  emptyAsNull: boolean
  inferTypes: boolean
  batchSize: number
}

export function convertCsvToSql(options: ConvertCsvToSqlOptions): ConvertCsvToSqlResult

CSVの解析（PapaParse）や文字コードの判定はUIコンポーネント側でやり、この関数には解析済みの headers と rows だけを渡す。こうしておくと、型推定やエスケープのテストをファイル読み込みと切り離して書ける。実際、src/lib/__tests__/convertCsvToSql.test.ts には33ケースのテストがあり、境界値（9桁と10桁の整数、バッチ行数の1と10000など）も押さえている。

型推定：全行を見て、混ざったら安全側に倒す

型推定は2段階になっている。まずセル1つずつを分類し、次に列の中で分類結果を合成する。

セル単位の分類

src/lib/csv/convertCsvToSql.ts
const INTEGER_RE = /^-?(0|[1-9]\d*)$/
const DECIMAL_RE = /^-?(0|[1-9]\d*)\.\d+$/

function classifyValue(value: string): ValueKind {
  const digits = value.replace('-', '').replace('.', '').length
  if (INTEGER_RE.test(value)) {
    if (digits <= 9) return 'integer'
    return digits <= 18 ? 'bigint' : 'text'
  }
  // 浮動小数点では精度を保てない桁数は文字列として扱う
  if (DECIMAL_RE.test(value)) return digits <= 15 ? 'decimal' : 'text'
  if (normalizeDate(value) !== null) return 'date'
  if (normalizeDateTime(value) !== null) return 'datetime'
  return 'text'
}

桁数のしきい値には理由がある。

分類 桁数 根拠
integer 9桁まで 32ビット整数の上限は2,147,483,647（10桁）。9桁なら必ず収まる
bigint 10〜18桁 64ビット整数の上限は9,223,372,036,854,775,807（19桁）。18桁なら必ず収まる
decimal 15桁まで 倍精度浮動小数点が10進数で確実に表せるのは15桁程度
text それ以上 数値型に入れると桁あふれや丸めが起きる

10桁の整数は値によって32ビットに収まったり収まらなかったりするが、境界で迷うより「10桁以上はBIGINT」と決めたほうが単純で安全だ。

INTEGER_RE の (0|[1-9]\d*) という書き方もポイントになる。0 単体か、1〜9 で始まる数字列しか整数とみなさない。つまり先頭がゼロの数字列は整数にならない。郵便番号 0010001 や電話番号 09012345678 を整数型にすると、先頭のゼロが消えて別の値になってしまう。

列単位の合成

列のすべてのセルを分類したら、mergeKinds で1つの型にまとめる。

src/lib/csv/convertCsvToSql.ts
function mergeKinds(current: ValueKind | null, next: ValueKind): ValueKind {
  if (current === null || current === next) return next
  if (isNumericKind(current) && isNumericKind(next)) {
    // 10桁以上の整数は浮動小数点に混ぜると精度が落ちるため文字列にする
    if (current === 'bigint' || next === 'bigint') {
      return current === 'decimal' || next === 'decimal' ? 'text' : 'bigint'
    }
    return current === 'decimal' || next === 'decimal' ? 'decimal' : 'integer'
  }
  const temporal = (k: ValueKind) => k === 'date' || k === 'datetime'
  if (temporal(current) && temporal(next)) return 'datetime'
  return 'text'
}

合成のルールをまとめるとこうなる。

混ざったもの 結果 理由
整数 + 小数 小数 9桁以下の整数なら倍精度で正確に表せる
10桁以上の整数 + 小数 文字列 倍精度に入れると下の桁が丸められる
日付 + 日時 日時 日付だけの値は 00:00:00 を補って出力する
数値・日付 + その他 文字列 1つでも合わない値があれば文字列

「整数 + 小数 = 小数」は普通の発想だが、BIGINTの範囲の整数を浮動小数点に混ぜると、12345678901234567 のような値が丸められて変わってしまう。値を壊すくらいなら文字列で持たせる、という方針で一貫させている。

先頭ゼロの列も、このルールで自然に文字列になる。郵便番号の列に 0010001 と 1000001 が混ざっていれば、前者は文字列・後者は整数と分類され、合成すると文字列になる。PostgreSQLで変換した結果はこうだ。

CREATE TABLE "t" (
  "zip" TEXT,
  "tel" TEXT,
  "big" TEXT,
  "mix" TEXT
);

INSERT INTO "t" ("zip", "tel", "big", "mix") VALUES
  ('0010001', '09012345678', '12345678901', '1'),
  ('1000001', '0312345678', '2.5', 'abc');

big 列は11桁の整数 12345678901 と小数 2.5 が混ざっているので文字列になっている。

ただし弱点もある。郵便番号の列がたまたま全部先頭ゼロ以外（1000001 だけなど）だと、整数型と推定される。これはデータだけからは判別できないので、ツールのFAQで「郵便番号を文字列として保持したい場合は型推定をオフに」と案内している。

日付は「実在する日付か」まで確認する

日付は YYYY-MM-DD と YYYY/M/D を受け付け、YYYY-MM-DD に正規化する。正規表現だけだと 2026/2/30 も通ってしまうので、月末日を計算して弾いている。

src/lib/csv/convertCsvToSql.ts
const DATE_RE = /^(\d{4})([-/])(\d{1,2})\2(\d{1,2})$/

function isValidDate(year: number, month: number, day: number): boolean {
  if (month < 1 || month > 12 || day < 1) return false
  const lastDay = new Date(Date.UTC(year, month, 0)).getUTCDate()
  return day <= lastDay
}

Date.UTC の月引数は0始まりなので、1始まりの month をそのまま渡すと「翌月の0日目」を指すことになる。たとえば2月なら Date.UTC(2026, 2, 0) で「3月の0日目」、つまり2月の末日（28日）が得られる。うるう年の2月29日もこれで正しく判定できる。

正規表現の \2 は後方参照で、区切り文字を最初と同じものに揃えている。2026-1/5 のように区切りが混ざった値は日付とみなさない。

方言の違い：型名と囲み文字とエスケープ

推定した型は、方言ごとに型名へ変換する。

推定した型 MySQL PostgreSQL SQLite
整数 INTEGER INTEGER INTEGER
大きい整数 BIGINT BIGINT INTEGER
小数 DOUBLE DOUBLE PRECISION REAL
日付 DATE DATE TEXT
日時 DATETIME TIMESTAMP TEXT
文字列 VARCHAR(255)（255文字以内）/ TEXT TEXT TEXT

SQLiteは日付型を持たないので、ISO 8601形式の文字列として TEXT に入れる。SQLiteの INTEGER は最大8バイトまで格納できるので、BIGINTの範囲もそのまま入る。

識別子（テーブル名・列名）の囲み方と、文字列のエスケープも方言で変わる。

src/lib/csv/convertCsvToSql.ts
function quoteIdentifier(name: string, dialect: SqlDialect): string {
  if (dialect === 'mysql') return '`' + name.replace(/`/g, '``') + '`'
  return '"' + name.replace(/"/g, '""') + '"'
}

function quoteString(value: string, dialect: SqlDialect): string {
  let escaped = value
  // MySQL は既定でバックスラッシュをエスケープ文字として扱う
  if (dialect === 'mysql') {
    escaped = escaped.replace(/\\/g, '\\\\').replace(/\0/g, '\\0')
  }
  return "'" + escaped.replace(/'/g, "''") + "'"
}

識別子は全部囲む。日本語の列名や、order のような予約語の列名でもそのまま通るようにするためだ。囲み文字自体が名前に含まれていたら二重にする。

文字列はSQL標準どおりシングルクォートを '' にする。違いが出るのはバックスラッシュで、MySQLは既定で文字列内の \ をエスケープ文字として扱う。C:\Users\a をそのまま 'C:\Users\a' と書くと、MySQLでは \U や \a がエスケープとして扱われる。どちらも定義されていないエスケープなのでバックスラッシュだけが捨てられ、C:Usersa という別の値になってしまう。そこでMySQLのときだけ \ を \\ にする。

INSERT INTO `t` (`path`) VALUES
  ('C:\\Users\\a');

PostgreSQLは standard_conforming_strings が既定でオンなので、通常の文字列リテラル内のバックスラッシュはただの文字になる。SQLiteはそもそもバックスラッシュによるエスケープを持たない。この2つで \ を二重にすると、逆に値が変わってしまう。

注意点として、MySQLでも sql_mode に NO_BACKSLASH_ESCAPES を設定しているサーバーでは、バックスラッシュはエスケープ文字にならない。その場合は二重にしたバックスラッシュが2文字のまま入ってしまう。このツールのMySQL出力は既定の sql_mode を前提にしているので、流すセッションで NO_BACKSLASH_ESCAPES を外してから実行する。方言を「PostgreSQL」に切り替える手は使えない。識別子がダブルクォートで囲まれ、ANSI_QUOTES が無効なMySQLでは文字列リテラルと解釈されて構文エラーになるからだ。

空セル・列名・バッチ分割

細かい処理もいくつかある。

空セル：文字列列は設定に従って NULL か '' にする。数値・日付の列は設定にかかわらず常に NULL にする。INTEGER 列に '' を入れると、MySQLはstrictモードならエラー、そうでなければ警告付きで 0 に変換する。PostgreSQLはエラーになり、SQLiteは '' がTEXTのまま入ってしまう。

列名：ヘッダーの先頭に付いたBOM（\uFEFF）を取り除き、空のヘッダーは column_1 のような名前にする。重複したヘッダーには _2、_3 と連番を付ける。重複判定は大文字小文字を区別しない。MySQLやSQLiteでは列名の大文字小文字が区別されないため、Name と name が並んでいると CREATE TABLE が失敗する。

バッチ分割：複数行を1つの INSERT 文にまとめると、実行が速くなりSQLも短くなる。一方で、1文が大きすぎるとMySQLの max_allowed_packet などの上限に当たる。既定は100行で、1〜10000行の範囲で変えられる。

生成したSQLite用のSQLは、Pythonの sqlite3 で実際に流して確認した。

import sqlite3
c = sqlite3.connect(":memory:")
c.executescript(sql)  # ツールが出力したSQLite用のSQL
for r in c.execute("select id, name, typeof(price), note from products"):
    print(r)
# (1, 'りんご', 'real', "O'Brien農園")
# (2, 'みかん', 'real', None)
# (3, 'ぶどう', 'real', '贈答用,\n要冷蔵')

O'Brien のシングルクォートも、改行入りの値も、空セルの NULL も期待どおり入っている。price 列は 80 や 450 も REAL として格納されている。

文字コードとプライバシー

ファイル読み込み時は、まずUTF-8として厳密にデコードし、失敗したらShift_JISで読み直す。Excelで「CSV（コンマ区切り）」形式で保存したファイルはShift_JISになるので、日本の実務のCSVはこの2択でほぼ読める。

src/app/tools/csv-to-sql/CsvToSqlClient.tsx
function decodeCsvBuffer(buffer: ArrayBuffer): string {
  try {
    return new TextDecoder('utf-8', { fatal: true }).decode(buffer)
  } catch {
    return new TextDecoder('shift_jis').decode(buffer)
  }
}

出力するSQLファイルはUTF-8になる。画面のプレビューは2万文字までに切り詰めているが、コピーとダウンロードは全量を対象にしている。

CSVの解析もSQLの生成もブラウザの中だけで行うので、ファイルがサーバーに送られることはない。顧客名簿や売上データのような、外部のサービスに渡しにくいCSVでもそのまま変換できる。

まとめ

  • 変換ロジックはブラウザAPIに依存しない純粋関数にして、33ケースのテストで型推定とエスケープの境界値を押さえた
  • 型推定は「全行を見る」「桁数で整数・BIGINT・小数を区切る」「混ざったら値を壊さない側（文字列）に倒す」の3点。先頭ゼロの数字列は整数にしない
  • エスケープは方言で分ける。識別子は全部囲み、文字列はシングルクォートを二重にし、MySQLだけバックスラッシュも二重にする

CSVからSQLを作る処理は、一見すると文字列を連結するだけに見える。実際には、郵便番号の先頭ゼロ、桁あふれ、存在しない日付、バックスラッシュの解釈の違いなど、データを黙って壊す落とし穴がいくつもあった。「推定できないときは文字列にする」という方針を決めておくと、判断に迷う場面がかなり減る。

ぱんだツールズ では他にもPDF・画像・CSV・テキスト処理などの開発者向けツールを110個以上公開中。全部無料・登録不要・ブラウザ完結で使える。
https://sakutto-panda.com

この記事は Zenn にも同じ内容を投稿しています。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?