Converting Web Tables to SQL INSERT Statements

You have a table on a webpage. You need it in your database.

The manual approach: copy to Excel, clean up, export CSV, write CREATE TABLE by hand, use LOAD DATA or COPY, debug the errors.

The better approach: generate complete SQL directly—CREATE TABLE with inferred types, INSERT statements with proper escaping.

Here's how to build a web table to SQL converter.

The Output Format

A complete SQL export should include:

-- Exported from HTML Table Exporter PRO

CREATE TABLE products (
  product_id INTEGER,
  name TEXT,
  price REAL,
  in_stock TEXT
);

INSERT INTO products (product_id, name, price, in_stock) VALUES
  (1, 'Widget', 29.99, 'true'),
  (2, 'Gadget', 49.99, 'false'),
  (3, 'O''Brien''s Special', 19.99, 'true');

Key requirements:

1. Valid table name (SQL-safe identifier)
2. Valid column names (no spaces, no special characters)
3. Appropriate column types (INTEGER, REAL, TEXT)
4. Properly escaped values (single quotes doubled)
5. NULL handling

Step 1: Sanitizing Identifiers

SQL identifiers (table and column names) have strict rules:

• No spaces or special characters
• Can't start with a digit
• Should be lowercase for portability

function sanitizeSqlIdentifier(name, fallback) {
  let id = (name || "").toString().trim();

  if (!id) id = fallback || "col";

  id = id
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")  // Remove accents (café → cafe)
    .toLowerCase()
    .replace(/[^a-z0-9_]+/g, "_")     // Replace invalid chars with underscore
    .replace(/^_+|_+$/g, "");          // Trim leading/trailing underscores

  // SQL identifiers can't start with a digit
  if (/^[0-9]/.test(id)) {
    id = "_" + id;
  }

  if (!id) id = fallback || "col";
  return id;
}

Examples:

• "Product Name" → "product_name"
• "Price ($)" → "price"
• "2024 Revenue" → "_2024_revenue"
• "Préço" → "preco"

Step 2: Generating Unique Column Names

Tables can have duplicate headers. SQL can't have duplicate column names.

function generateColumnNames(headerRow) {
  const usedNames = new Set();

  return headerRow.map((header, index) => {
    const base = sanitizeSqlIdentifier(header, `col_${index + 1}`);
    let candidate = base;
    let counter = 1;

    while (usedNames.has(candidate)) {
      candidate = `${base}_${counter}`;
      counter++;
    }

    usedNames.add(candidate);
    return candidate;
  });
}

Examples:

• ["Name", "Name", "Value"] → ["name", "name_1", "value"]
• ["", "", "Data"] → ["col_1", "col_2", "data"]

Step 3: Inferring Column Types

SQL has three main types we care about:

• INTEGER: Whole numbers
• REAL: Decimal numbers
• TEXT: Everything else

The inference samples values from the column and picks the most specific type that fits all values:

function inferSqlColumnTypes(rows, headerRowIndex = 0) {
  const headerRow = rows[headerRowIndex] || [];
  const dataRows = rows.slice(headerRowIndex + 1);

  const colCount = headerRow.length;
  const types = new Array(colCount).fill("TEXT");

  for (let col = 0; col < colCount; col++) {
    // Sample up to 50 non-empty values
    const values = [];

    for (let r = 0; r < dataRows.length && values.length < 50; r++) {
      const cell = dataRows[r][col];
      const v = cell != null ? String(cell).trim() : "";
      if (v !== "") values.push(v);
    }

    if (values.length === 0) {
      types[col] = "TEXT";
      continue;
    }

    // Check if all values are integers
    let allInt = true;
    let allNumeric = true;

    for (const v of values) {
      if (!/^[-+]?\d+$/.test(v)) {
        allInt = false;
      }
      if (!/^[-+]?\d+([.,]\d+)?$/.test(v)) {
        allNumeric = false;
      }
    }

    if (allInt) {
      types[col] = "INTEGER";
    } else if (allNumeric) {
      types[col] = "REAL";
    } else {
      types[col] = "TEXT";
    }
  }

  return types;
}

Key decisions:

• Sample only 50 values for performance on large tables
• INTEGER is stricter than REAL (no decimals allowed)
• If ANY value doesn't match, fall back to TEXT
• Empty columns default to TEXT

Step 4: Escaping Values

SQL strings require escaping single quotes by doubling them:

function sqlEscapeValue(raw, type) {
  // NULL handling
  if (raw == null) return "NULL";

  const v = String(raw).trim();
  if (v === "") return "NULL";

  // For numeric types, try to return unquoted number
  if (type === "INTEGER" || type === "REAL") {
    const normalized = v.replace(",", ".");  // Handle EU decimal format
    const num = Number(normalized);

    if (!Number.isNaN(num) && Number.isFinite(num)) {
      return normalized;
    }
    // Fall through to TEXT handling if not a valid number
  }

  // TEXT or fallback: escape single quotes
  const escaped = v.replace(/'/g, "''");
  return `'${escaped}'`;
}

Examples:

• "Hello" → 'Hello'
• "O'Brien" → 'O''Brien'
• "It's \"quoted\"" → 'It''s "quoted"'
• 123 (INTEGER) → 123
• 45.67 (REAL) → 45.67
• "" → NULL
• null → NULL

Step 5: Generating the Complete SQL

function tableToSqlString(tableInfo) {
  const rows = tableInfo.rows || [];
  if (!rows.length) return "";

  const headerRowIndex = tableInfo.headerRowIndex || 0;
  const headerRow = rows[headerRowIndex];
  const dataRows = rows.slice(headerRowIndex + 1);

  if (!headerRow) return "";

  // Generate safe column names
  const columnNames = generateColumnNames(headerRow);

  // Infer column types
  const types = inferSqlColumnTypes(rows, headerRowIndex);

  // Generate safe table name
  const rawTableName = tableInfo.slug || tableInfo.name || "table";
  let tableName = sanitizeSqlIdentifier(rawTableName, "table");
  if (!tableName) tableName = "table_export";

  // BUILD CREATE TABLE
  const createLines = columnNames.map((col, i) => 
    `  ${col} ${types[i] || "TEXT"}`
  );
  const createStmt = `CREATE TABLE ${tableName} (\n${createLines.join(",\n")}\n);`;

  // BUILD INSERT STATEMENTS
  const insertHeader = `INSERT INTO ${tableName} (${columnNames.join(", ")}) VALUES`;

  const valueLines = dataRows.map(row => {
    const values = columnNames.map((_, i) => {
      const cell = row[i];
      const type = types[i] || "TEXT";
      return sqlEscapeValue(cell, type);
    });
    return `  (${values.join(", ")})`;
  });

  // COMBINE
  let sql = `-- Exported from HTML Table Exporter PRO\n\n${createStmt}\n\n`;

  if (valueLines.length) {
    sql += `${insertHeader}\n${valueLines.join(",\n")};\n`;
  }

  return sql;
}

Example Output

Input table:

Product	Price	Qty
Widget	29.99	100
O'Brien's	19.99	50

Output:

-- Exported from HTML Table Exporter PRO

CREATE TABLE products (
  product TEXT,
  price REAL,
  qty INTEGER
);

INSERT INTO products (product, price, qty) VALUES
  ('Widget', 29.99, 100),
  ('O''Brien''s', 19.99, 50);

Handling Edge Cases

Empty Tables

if (!rows.length) return "";
if (!headerRow) return "";

Return empty string rather than invalid SQL.

All-NULL Columns

Columns with only empty/null values get TEXT type. The inference function handles this:

if (values.length === 0) {
  types[col] = "TEXT";
  continue;
}

Mixed Number Formats

The escaper normalizes comma decimals:

const normalized = v.replace(",", ".");

"1,234.56" stays as-is. "1.234,56" (EU format) should be normalized before reaching the SQL generator—that's the job of the cleaning presets.

Very Long Values

TEXT columns in SQLite/PostgreSQL handle arbitrary length. No truncation needed. For MySQL's VARCHAR limits, you'd need to specify length or use TEXT explicitly.

Database Compatibility

The generated SQL is intentionally simple:

Feature	SQLite	PostgreSQL	MySQL
CREATE TABLE	✅	✅	✅
INTEGER/REAL/TEXT	✅	✅	✅
Multi-row INSERT	✅	✅	✅
Single quote escape	✅	✅	✅

For database-specific features (constraints, indexes, schemas), you'd extend the generator. For basic data import, this works everywhere.

Using the Export

# SQLite
sqlite3 mydb.db < export.sql

# PostgreSQL
psql -d mydb -f export.sql

# MySQL
mysql mydb < export.sql

Or paste directly into your database client.

For more on the CSV export path (when you don't need SQL), see our guide on exporting HTML tables to CSV.

---

Need SQL exports without writing code? Learn more at gauchogrid.com/html-table-exporter or try it free on the Chrome Web Store.