Generate INSERT statements from CSV files in script path. not supported flexble options yet. use at your own risk. (for Powershell and node.js)

csv2sql.ps1

try {
    Get-Variable PSScriptRoot -Scope Global -ErrorAction 'Stop' | Out-Null
} catch [System.Management.Automation.ItemNotFoundException] {
    $PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
}

$commitsize = 1000

Get-ChildItem $PSScriptRoot -Filter *.txt | ForEach-Object {
  "Processing: $($_.FullName)"
  $startdate = Get-Date
  $table = [io.path]::GetFileNameWithoutExtension($_.FullName)
  $saveto  = $_.FullName
  $saveto += ".sql"
  "" | Out-File -FilePath $saveto
  $csv = Import-Csv $_.FullName -Delimiter "`t" -Encoding Default
  $headers = $csv | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'
  $headstr = $headers -Join ", "
  "Table: $table"
  "Headers: $headstr"
  $affected = 0
  $csv | ForEach-Object {
    $row = $_
    $valuearr = New-Object System.Collections.ArrayList
    Foreach ($head in $headers) {
        $val  = "'"
        $val += $row.$head -Replace "'", "''"
        $val += "'"
        $valuearr.Add($val) | Out-Null
    }
    $valuestr = $valuearr -Join ", "
    "INSERT INTO $table ($headstr) VALUES ($valuestr);" | Out-File -FilePath $saveto -Append
    $affected += 1
    if ($affected % $commitsize -eq 0) {
      "COMMIT;" | Out-File -FilePath $saveto -Append
      "commit statement for row $affected"
    }
  }
  if ($affected % $commitsize -ne 0) {
      "COMMIT;" | Out-File -FilePath $saveto -Append
      "commit statement for row $affected"
  }
  "Output: $saveto"
  "Affected: $affected"
  $elapsed = ([System.TimeSpan]((Get-Date) - $startdate)).Milliseconds
  "Elapsed: $elapsed ms"
}

index.js

const fs = require('fs');
const path = require('path');
const parse = require('csv-parse');
const Iconv = require('iconv').Iconv;
const arg = process.argv[2];
const sep = process.argv[3] || ',';
const split = 1e3;
if(arg) {
    fs.stat(arg, function(err){
        if(!err) {
            console.log('start!' + arg);
            const file = path.basename(arg);
            const name = file.replace(/\.\w+$/,'');
            const saveto = arg + '.sql';
            const writer = fs.createWriteStream(saveto);
            let i = 1, st = new Date();
            const parser = parse({delimiter: sep.replace('tab','\t'), relax_column_count: true, columns: true});
            parser.on('readable', function(){
                        let record;
                        while (record = parser.read()) {
                            const keys = Object.keys(record);
                            const values = [];
                            keys.forEach(key => values.push("'" + record[key].replace(/'/g, "''") + "'"));
                            writer.write(`INSERT INTO ${name} (${keys.join(', ')}) VALUES (${values.join(', ')});\n`);
                            if(!(i++ % split)) writer.write('COMMIT;\n');
                        }
                    })
                    .on('end', function(){
                        if(i % split) writer.end('COMMIT;\n');
                        console.log(`write completed with ${i} rows: ${new Date() - st} ms`);
                    });
            fs.createReadStream(arg).pipe(new Iconv('CP949', 'UTF-8')).pipe(parser);
        }
    });
    
} else console.error('no file.');

package.json

{
  "name": "csv2sql",
  "version": "1.0.0",
  "description": "Convert CSV to SQL INSERT statement.",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Composite",
  "license": "ISC",
  "dependencies": {
    "csv": "^5.3.1",
    "iconv": "^2.3.5"
  }
}

How to use

Powershell

Place powershell script file to convert csv to sql and run powershell script file.
Default delimiter is tab. you can edit this script file for your own environment.
Known issue: This script is extremely slow. I don't know what should I do.

Node.js

Place index.js, package.json in a folder you want, then run npm i.
after init, run this script like node index.js /path/to/convert.csv.
if you want to run csv that seperated by tab: node index.js /path/to/convert.tsv tab
or, you can use custom delimiter: node index.js /path/to/convert.txt tab "|"
if you don't satisfied to this script, FORK and EDIT it yourself.

License

WTFPL