Can I improve this CSV-import query performance?

Question

I'm currently trying to import a CSV file of 20gbs (roughly 64 million rows, 58 columns) to a mssql database.

First I tried to do it with SSIS but is was so slow I decided to try using Powershell instead and found a nice query here:

High performance import of csv

The query is very quick, I manage to insert roughly 1 million rows per minute. However , I need to be able to handle delimiters embedded in quotes, like this: Column1,"Car,plane,boat",Column3

I did it using regex by the authors recommendation by switching:

 $null = $datatable.Rows.Add($line.Split($csvdelimiter))

to:

 $null = $datatable.Rows.Add($([regex]::Split($line, $csvSplit, $regexOptions)))

Full query:

# Database variables
$sqlserver = "server"
$database = "database"
$table = "tablename"

# CSV variables
$csvfile = "filepath"
$csvdelimiter = ","
$firstRowColumnNames = $true
$fieldsEnclosedInQuotes = $true

# Handling of regex for comma problem
if ($fieldsEnclosedInQuotes) {
    $csvSplit = "($csvdelimiter)"
    $csvsplit += '(?=(?:[^"]|"[^"]*")*$)'
} else { $csvsplit = $csvdelimiter }



$regexOptions = [System.Text.RegularExpressions.RegexOptions]::ExplicitCapture


################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() 
[void][Reflection.Assembly]::LoadWithPartialName("System.Data")
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

# 50k worked fastest and kept memory usage to a minimum
$batchsize = 50000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$firstline = (Get-Content $csvfile -First 1)
$columns = [regex]::Split($firstline, $csvSplit, $regexOptions)


if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }

foreach ($column in $columns) { 

    $null = $datatable.Columns.Add()
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
    $null = $datatable.Rows.Add($([regex]::Split($line, $csvSplit, $regexOptions)))
    $i++; if (($i % $batchsize) -eq 0) { 
        $bulkcopy.WriteToServer($datatable) 
        Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
        $datatable.Clear() 
    } 
} 

 # add in all the remaining rows since the last clear
 if($datatable.rows.count -gt 0) {
 $bulkcopy.writetoserver($datatable)
 $datatable.clear()
 }

# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()

Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
 [System.GC]::Collect()
pause

It takes much longer time with the regex:

24 seconds per 50 000k rows (with handling of delimiters embedded in qoutes)

2 seconds per 50 000k rows (without the handling)

Am I doing something wrong? Is regex the correct way to this? Can I improve the query performance in any way or is this performance lost something I have to accept?

Update: Added fully query

Answer 1

For large CSVs I would use Microsoft.VisualBasic.FileIO.TextFieldParser. All the parsing (advanced, see example) is done there effectively.

Do not worry about "VisualBasic", it's part of the .NET. The assembly should be added explicitly and that's it.

Here is the how-to sample with some comments

    # temp data
    Set-Content z.csv @'
column1,column2,column3
"data,
""1a""",data2a,data3a


data1b,  data2b  ,data3b
'@

    Add-Type -AssemblyName Microsoft.VisualBasic

    $reader = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser $PSScriptRoot\z.csv #! full path
    $reader.SetDelimiters(',') # default is none
    $reader.TrimWhiteSpace = $false # default is true

    while(!$reader.EndOfData) {
        $reader.LineNumber #! it counts not empty lines
        $reader.ReadFields() | %{ "data: '$_'" }
    }
    $reader.Close()

    Remove-Item z.csv

Answer 2

I've recently implemented a CSV parser for .NET, that I'm claiming is the fastest available as a nuget package: Sylvan.Data.Csv.

This can be used in conjunction with SqlBulkImport very easily:

using TextReader csvText = GetData(); // Gets a TextReader for a CSV dataset

var opts = new CsvDataReaderOptions {
 // indicates that empty fields should be processed as null
 // instead of as an empty string.
 Schema = CsvSchema.Nullable
};
DbDataReader dataReader = CsvDataReader.Create(csvText);

var csb = 
    new SqlConnectionStringBuilder { 
        DataSource = @"(LocalDb)\MSSqlLocalDb", 
        InitialCatalog = "Test", 
        IntegratedSecurity = true 
    };
using var conn = new SqlConnection(csb.ConnectionString);
conn.Open();


var bcp = new SqlBulkCopy(conn);
bcp.BulkCopyTimeout = 0;
bcp.DestinationTableName = "Feature";
bcp.BatchSize = 50000;
bcp.WriteToServer(dataReader);

This code was able to import a 2.2 million record data set in ~12 seconds on my machine. Using the above code, all columns will be interpreted as nullable strings.

It is also possible to provide a schema to the CsvDataReaderOptions. Assuming the target table in SQL Server has the correct schema applied, this can be done relatively easily also:

SqlConnection conn = ...;
var cmd = conn.CreateCommand();

// select an empty record set to get the table schema.
cmd.CommandText = "select top 0 * from [MyTargetTable]";
var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();
reader.Close();
var csvSchema = new Schema(tableSchema);

var options = new CsvDataReaderOptions { Schema = csvSchema };
// the dataReader will now expose a strongly-typed schema which SqlBulkImport
// will be able to properly consume.
var dataReader = CsvDataReader.Create(csvText, options);