csv-sql-wizard

A robust Node.js library for converting CSV and Excel files to SQL INSERT statements, supporting both PostgreSQL and MySQL databases.

Features

• ✅ Convert CSV files to SQL statements
• ✅ Convert Excel files (.xlsx, .xls) to SQL statements
• ✅ Support for PostgreSQL and MySQL syntax
• ✅ Batch insert support for better performance
• ✅ Automatic data type inference
• ✅ Custom column type mapping
• ✅ CREATE TABLE statement generation
• ✅ Proper SQL escaping and sanitization
• ✅ TypeScript support

Installation

npm install csv-sql-wizard

Quick Start

Convert CSV to SQL

import { csvToSQL } from 'csv-sql-wizard';

const result = await csvToSQL('data.csv', {
  databaseType: 'postgresql',
  tableName: 'users',
  createTable: true,
  batchInsert: true
});

console.log(result.sql);

Convert Excel to SQL

import { excelToSQL } from 'csv-sql-wizard';

const result = await excelToSQL('data.xlsx', {
  databaseType: 'mysql',
  tableName: 'products',
  sheetName: 'Sheet1', // or use sheetIndex: 0
  createTable: true
});

console.log(result.sql);

API Reference

csvToSQL(filePath, options)

Convert a CSV file to SQL statements.

Parameters:

• filePath (string): Path to the CSV file
• options (ConversionOptions): Conversion options

Returns: Promise<ConversionResult>

csvStringToSQL(csvContent, options)

Convert a CSV string to SQL statements.

Parameters:

• csvContent (string): CSV content as string
• options (ConversionOptions): Conversion options

Returns: Promise<ConversionResult>

excelToSQL(filePath, options)

Convert an Excel file to SQL statements.

Parameters:

• filePath (string): Path to the Excel file
• options (ConversionOptions & { sheetName?: string; sheetIndex?: number }): Conversion options
  • sheetName: Name of the sheet to convert (optional)
  • sheetIndex: Index of the sheet to convert (optional, defaults to 0)

Returns: Promise<ConversionResult>

getExcelSheetNames(filePath)

Get list of sheet names from an Excel file.

Parameters:

• filePath (string): Path to the Excel file

Returns: string[]

Options

ConversionOptions

interface ConversionOptions {
  /** Database type: 'postgresql' or 'mysql' */
  databaseType: 'postgresql' | 'mysql';
  
  /** Table name for the SQL statements */
  tableName: string;
  
  /** Schema name (optional, mainly for PostgreSQL) */
  schemaName?: string;
  
  /** Whether to include IF NOT EXISTS clause */
  ifNotExists?: boolean;
  
  /** Whether to generate CREATE TABLE statement */
  createTable?: boolean;
  
  /** Custom column types mapping (columnName -> SQL type) */
  columnTypes?: Record<string, string>;
  
  /** Whether to use batch inserts (multiple rows per INSERT) */
  batchInsert?: boolean;
  
  /** Number of rows per batch (default: 100) */
  batchSize?: number;
  
  /** Whether to escape column names with backticks/quotes */
  escapeColumnNames?: boolean;
  
  /** Custom handling for NULL values */
  nullValue?: string;
}

Examples

PostgreSQL with Custom Schema

import { csvToSQL } from 'csv-sql-wizard';

const result = await csvToSQL('users.csv', {
  databaseType: 'postgresql',
  tableName: 'users',
  schemaName: 'app',
  createTable: true,
  ifNotExists: true,
  batchInsert: true,
  batchSize: 500
});

console.log(result.sql);
// CREATE TABLE IF NOT EXISTS "app"."users" (
//   "id" INTEGER,
//   "name" TEXT,
//   "email" TEXT
// );
//
// INSERT INTO "app"."users" ("id", "name", "email")
// VALUES
//   (1, 'John Doe', '[email protected]'),
//   (2, 'Jane Smith', '[email protected]');

MySQL with Custom Column Types

import { excelToSQL } from 'csv-sql-wizard';

const result = await excelToSQL('products.xlsx', {
  databaseType: 'mysql',
  tableName: 'products',
  createTable: true,
  columnTypes: {
    id: 'INT AUTO_INCREMENT PRIMARY KEY',
    price: 'DECIMAL(10, 2)',
    description: 'TEXT',
    created_at: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
  },
  batchInsert: false // Individual INSERT statements
});

console.log(result.sql);

Convert CSV String

import { csvStringToSQL } from 'csv-sql-wizard';

const csvData = `name,age,email
John,30,[email protected]
Jane,25,[email protected]`;

const result = await csvStringToSQL(csvData, {
  databaseType: 'postgresql',
  tableName: 'users',
  createTable: true
});

console.log(result.sql);

Get Excel Sheet Names

import { getExcelSheetNames } from 'csv-sql-wizard';

const sheetNames = getExcelSheetNames('data.xlsx');
console.log(sheetNames); // ['Sheet1', 'Sheet2', 'Data']

Advanced Usage

Using Class-based API

import { CSVConverter, ExcelConverter } from 'csv-sql-wizard';

// CSV
const csvResult = await CSVConverter.fromFile('data.csv', {
  databaseType: 'mysql',
  tableName: 'records'
});

// Excel with specific sheet
const excelResult = await ExcelConverter.fromFile('data.xlsx', {
  databaseType: 'postgresql',
  tableName: 'records',
  sheetName: 'Data'
});

Data Type Inference

The library automatically infers SQL data types from the data:

• Numbers: INTEGER or DECIMAL (if contains decimal point)
• Booleans: BOOLEAN (PostgreSQL) or TINYINT(1) (MySQL)
• Dates: TIMESTAMP (PostgreSQL) or DATETIME (MySQL)
• Strings: TEXT

You can override inferred types using the columnTypes option.

Error Handling

The library throws descriptive errors for common issues:

• Empty files or sheets
• Missing sheets (Excel)
• Invalid file paths
• Malformed CSV data

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.