Converts an array of objects into a SQL insert statement.

Convert-ToInsertQuery.ps1

<#
.SYNOPSIS
    Converts an array of objects into a SQL insert statement.

.EXAMPLE
    Import-Csv -Path language.csv -Delimiter `| -Header @("col1", "col2") | Convert-ToInsertQuery -TableName table1 -DefaultType varchar -DataTypes @{ col1 = "int" }
#>
function Convert-ToInsertQuery {
    [CmdletBinding()]
    param(
        [Parameter(ValueFromPipeline = $true)]
        [array] $Items,
        [string] $TableName,
        [ValidateSet("number", "string")]
        [string] $DefaultType = "string",
        [hashtable] $DataTypes = @{}
    )

    BEGIN {
        $_items = @()
    }

    PROCESS {
        $_items += $_
    }

    END {
        $lines = @()
        $columns = @()

        # Collect column names from property names.
        if ($_items.Count -ne 0) {
            $_items[0].PSObject.Properties | %{
                $columns += $_.Name
            }
        }

        "INSERT INTO $TableName ($($columns -join ",")) VALUES "

        for ($i = 0; $i -lt $_items.Count; $i++) {
            $item = $_items[$i]
            
            $properties = @()

            $item.PSObject.Properties | %{
                $name = $_.Name
                $value = $_.Value

                $dataType = if ($DataTypes[$name] -ne $null) { $DataTypes[$name] } else { $DefaultType }

                switch ($DataTypes[$name]) {
                    "number" { $value = $value }
                    "string" { $value = "'$value'" }
                    default { $value = "'$value'" }
                }

                $properties += "$value"
            }

            $lines += "($($properties -join ","))"
        }

        ($lines -join ",`r`n") + ";"
    }
}

$file = "vs_market\EN.dat"
$header = @(
    "record_type";
    "content_indicator";
    "file_number";
    "registration_number";
    "unique_system_identifier";
    "entity_type";
    "entity_type_code";
    "entity_type_other";
    "licensee_id";
    "entity_name";
    "first_name";
    "mi";
    "last_name";
    "suffix";
    "phone";
    "fax";
    "internet_address";
    "street_address";
    "street_address2";
    "po_box";
    "city";
    "state";
    "zip_code";
    "attention";
    "frn";
);

Import-Csv -Path $file -Delimiter `| -Header $header |
    # Select -First 10 | # Uncomment the beginning of this line to test the conversion with a small subset 
    Convert-ToInsertQuery -TableName vs_market -DefaultType string -DataTypes @{ unique_system_identifier = "number" } |
    Set-Content output.sql