Importing from a CSV to a database table

A common request that i get frequently is to load contents of CSV into the database. Until i came across dbatools, I used to perform this operation using GUI.

dbatools offers two ways to do this:

1. Import-DbaCsv
2. Import-Csv and Write-DbaDataTable

Which one should you choose? It is a personal choice, I have found that Import-DbaCsv is better choice for larger CSVs because it is optimized to keep memory usage low.

When you use Import-DbaCsv, it’s using some kind of streaming methodology to move the data efficiently between the disk and the SQL Server.

The output from the command shows table information, the number of rows copied and even how quickly it copied them. This is useful when you are testing a script with a smaller set of data as you can extrapolate the time it will take to load the data in your production environment.

Get-ChildItem -Path C:\temp\data.csv | Import-DbaCsv -SqlInstance $sqlinstance1 -Database StagingDB -Table data1

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data1
Schema        : dbo
RowsCopied    : 2450
Elapsed       : 55.16 ms
RowsPerSecond : 44663
Path          : C:\temp\data.csv

The beauty of the command is that you can even import multiple CSVs at once! You can do so using the command shown below:

Get-ChildItem C:\temp\data*.csv | Import-DbaCsv -SqlInstance sql2017 -Database StagingDB -AutoCreateTable

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data-tracks1
Schema        : dbo
RowsCopied    : 2450
Elapsed       : 73.02 ms
RowsPerSecond : 33712
Path          : C:\temp\data-tracks1.csv

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data-tracks2
Schema        : dbo
RowsCopied    : 1312
Elapsed       : 65.41 ms
RowsPerSecond : 20160
Path          : C:\temp\data-tracks2.csv

Using Import-Csv with Write-DbaDataTable

Import-Csv is a powerful command that turns the text within CSV files to objects. Generally, Import-Csv is piped right to Write-DbaDataTable

# Import the CSV and write to the database
Import-Csv -Path .\DatabaseServerinfo.csv | Write-DbaDataTable -SqlInstance $sqlinstance1 -Database StagingDB -Table Databases -AutoCreateTable

Piping to Write-DbaDataTable is convenient and extremely fast for small batches, but slows down for larger datasets (similar to SQL’s RBAR concept). If you intend to import a large dataset, use Import-DbaCsv or the following syntax instead:

Import-DbaCsv -Path \\NetworkPath\server\bigdataset.csv -SqlInstance sql2017 -Database StagingDB -Table NewTable -AutoCreateTable