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

``bash
npm install csv-sql-wizard
`

Quick Start

$3

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

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

console.log(result.sql);
`

$3

`typescript
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

$3

Convert a CSV file to SQL statements.

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

Returns: Promise

$3

Convert a CSV string to SQL statements.

Parameters:
- csvContent (string): CSV content as string
- options (ConversionOptions): Conversion options

Returns: Promise

$3

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

$3

Get list of sheet names from an Excel file.

Parameters:
- filePath (string): Path to the Excel file

Returns: string[]

Options

$3

`typescript
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;

/* 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

$3

`typescript
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', 'john@example.com'),
// (2, 'Jane Smith', 'jane@example.com');
`

$3

`typescript
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);
`

$3

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

const csvData = name,age,email
John,30,john@example.com
Jane,25,jane@example.com;

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

console.log(result.sql);
`

$3

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

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

Advanced Usage

$3

`typescript
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.