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?
Your dump comes from a Paradox database. Paradox supports a lot of types about 18. CSV is typeless. It's even worse than JSON. Everything is a string.
All you can do is infer with heuristics. You've lost that data. Inferring is not going to be easy. To do it right
This is possible, but you're still guessing and it's a laborious task. Some languages give you tools to start you down this path, like looks_like_number, and some type systems provide methods like is_type that will help you get there faster.
If you still have the database however, you can plug into ODBC. Or you can use other code that can handle it. As a side note. pxlib has a cli tool called pxview which dumps to SQL. That's the best starting point.
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 & 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 & 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.
This code below will install multiple files into one table. It may be a starting point for you.
--BULK INSERT MULTIPLE FILES From a Folder
--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
SET @path = 'C:\Dump\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead, also after INSERT, name your table:
set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '
+ ' WITH (
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 2
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
SSIS wizard will be a fast and easy way. SSIS can create the table based on the column headings (this must be at the first line at the top of the CSV file) and import the data. This will save lots of typing out TSQL. This will also let you get this done starting in the next five minutes. The biggest reason for SSIS wizard is it can save the newly created package for a multiple test imports and then the final run for the conversion.
I don't know of a way to automate the wizard to do all 400 files for you. But lets assume you can get the time down to 5 minute per file, you are looking at 33 hours.
