Bulk table creation and import of data I have a around 400 CSV files which have been exported from an old Paradox database, with each CSV representing a table within the old database.  Each CSV has a different number of columns and different column headings.

I'm looking for suggestions on an easy way to bulk import all of these files into SQL Server, with a new table created for each of the CSV files.  No relational structure is required.

What options do I have for quickly getting creating the 400 tables and importing the data?
A fairly easy option I like is just using PowerShell.

Get the out-datatable.ps1 module from CodePlex here and loop through your .csv files.

Then generate a SQL statement to create your table and use SQL bulk insert to load your data into your table.

This is a script that could do what  you want, it processes .txt files as tab-delimited and csv files as comma-delimited.

You may need to adapt it a bit for your needs, it's a script I used somewhere and tried to clean up before posting:

$ErrorActionPreference = "Stop"
########################################
#                                      #
#  Define paths and connectionstring   #
#                                      #
########################################

$ConnectionString = "Data Source=localhost; Database=dbname; Trusted_Connection=True;";
$ScriptDirectory = "C:\\scriptdirectory"
$FilePath = "C:\\import\\csv\\"

######################
#                    #
#  Import CSV Files  #
#                    #
######################

# Local variables
$CreateStatement = ""

# Go to working directory
Set-Location $ScriptDirectory
# Source script from https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
. ".\Out-DataTable.ps1"

# Process files recursively
$Files = Get-ChildItem -Include @("*.txt","*.csv") -rec $FilePath | Where-object {!$_.psIsContainer -eq $true} 
foreach ($File in $Files)
{
    $TableName = $File.BaseName
    # Read in CSV
    if ($File.Extension -eq ".txt")
    {
        $csvDataTable = Import-CSV -Delimiter "`t"  -Path $TempFileName | Out-DataTable
        Remove-Item $TempFileName
    }
    if ($File.Extension -eq ".csv")
    {
        $csvDataTable = Import-CSV -Delimiter ";"  -Path $File.FullName | Out-DataTable
    }
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
    $bulkCopy.DestinationTableName = "[" + $TableName + "]"
    # If datatable exists, create table &amp; import datatable
    if ($csvDataTable -ne $null)
    {
        # Create Table
        $CreateStatement = "CREATE TABLE [" + $TableName + "] ("
        foreach($Column in $csvDataTable.Columns)
        {
            $CreateStatement += "[" + $Column.ColumnName + "]  NVARCHAR(MAX),"
        }
        # remove last column &amp; close parentheses
        $CreateStatement = $CreateStatement.Substring(0,$CreateStatement.Length-1) + ")"
        $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString);
        $cmd = new-object system.data.sqlclient.sqlcommand($CreateStatement, $Connection);
        $Connection.Open();
        if ($cmd.ExecuteNonQuery() -ne -1)
        {
            Throw "Create Table failed"
        }
        $Connection.Close();
        $bulkCopy.WriteToServer($csvDataTable)
    }
}


It does have an issue with creating every column as NVARCHAR, that may not be suitable for your needs, but the datatable doesn't detect the data type unfortunately, if you figure that out let me know how you did it.