1

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?

  • 2
    Your main impediment will be creating a new table for each CSV file name. There is info out there on copying multiple sources into one table. – bwilliamson Jan 12 '17 at 16:27
  • 2
    I agree with @bwilliamson. CSV-s are typeless, you may want to start out with what types you want to infer from the CSV? Is everything varchar(8000) or something? I would be looking at the old Paradox database myself. Do you still have access to it? The metadata that it stores could be crucial. – Evan Carroll Jan 12 '17 at 17:36
0

Why this is a bad idea.

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.

  • How will you know which one of those things your string is?

All you can do is infer with heuristics. You've lost that data. Inferring is not going to be easy. To do it right

  • You'd to create a matrix of broadest type
  • Elevate types and work sequentially through the type-tree until you fail to cast to a more specific type.

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.

Good ideas

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.

  • My starting point is to use pxlib to pull this data out into SQL. Turned out to be pretty straight forward. Thanks. – uint32 Jan 19 '17 at 14:21
2

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.

0

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
  • Where do you read the schema for the file, and set up TEMP? He's saying csv actually determines the schema for the table. – Evan Carroll Jan 12 '17 at 17:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.