Excel to SQL Converter

A TypeScript library for converting Excel, CSV, and JSON files to SQL INSERT statements.

Features

• Convert Excel (.xlsx, .xls), CSV, and JSON files to SQL INSERT statements
• Automatic type inference for columns
• Customizable table names and column types
• Support for various SQL data types (VARCHAR, INT, DECIMAL, DATE, DATETIME, etc.)
• Command-line interface (CLI) and programmatic API
• Built with TypeScript for type safety

Installation

npm install excel-to-sql-converter
# or
yarn add excel-to-sql-converter

Usage

Basic Usage

import { ExcelToSqlConverter } from 'excel-to-sql-converter';

// Convert an Excel file to SQL
async function convertExcelToSql() {
  const result = await ExcelToSqlConverter.convertFile('input.xlsx', {
    tableName: 'my_table',
    outputDir: './output'
  });
  
  console.log(`Generated SQL for ${result.rowCount} rows`);
  console.log(`SQL saved to: ${result.outputFile}`);
}

// Convert JSON data to SQL
function convertJsonToSql() {
  const data = [
    { id: 1, name: 'John Doe', age: 30 },
    { id: 2, name: 'Jane Smith', age: 25 }
  ];
  
  const result = ExcelToSqlConverter.convertJson(data, {
    tableName: 'users',
    columnTypes: {
      id: 'number',
      name: 'string',
      age: 'number'
    }
  });
  
  console.log(result.sql);
}

Command Line Interface

# Convert an Excel file to SQL
npx excel-to-sql input.xlsx -t my_table -o ./output

# Convert a CSV file to SQL
npx excel-to-sql data.csv -t my_table --format csv -o ./output

# Show help
npx excel-to-sql --help

API Reference

ExcelToSqlConverter.convertFile(filePath: string, options?: ConvertOptions): Promise<ConversionResult>

Converts a file to SQL statements.

Parameters:

• filePath: Path to the input file (Excel, CSV, or JSON)
• options: Optional configuration (see below)

Returns: A promise that resolves to a ConversionResult object

ExcelToSqlConverter.convertJson(data: Record<string, any>[], options?: ConvertOptions): ConversionResult

Converts an array of objects to SQL statements.

Parameters:

• data: Array of objects to convert
• options: Optional configuration (see below)

Returns: A ConversionResult object

Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | tableName | string | Derived from filename | Name of the SQL table | | headerRow | number | 0 | Row index (0-based) containing headers | | sheetName | string | First sheet | Name of the sheet to convert (Excel only) | | outputDir | string | undefined | Directory to save the SQL file | | columnTypes | Record<string, ColumnType> | Inferred | Column type overrides | | skipHeader | boolean | false | Skip the header row in the output |

ConversionResult

| Property | Type | Description | |----------|------|-------------| | sql | string | Generated SQL statements | | tableName | string | Name of the SQL table | | rowCount | number | Number of rows converted | | outputFile | string | Path to the output file (if outputDir was specified) |

License

MIT