Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell

Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Jan 28th, 2020 | 0 comments

Export SQL Data to multiple CSV files.

There are many different ways to do this (SSIS, TSQL) but I think that PowerShell could be really efficient tool for such small random tasks.

For this example I will use test table with 17220 rows.

First step is to export whole table to CSV file using export-csv cmdlet.

Export-CSV converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file.

If you want to find more details about Export-CSV cmdlet and see more usage examples you can read this MS article.

param
(
[String] $destination = 'your_destination',
[String] $ServerName = '.',
[String] $dbname = 'dbname',
[String] $TableName = 'test'
)
invoke-sqlcmd -query "SELECT * FROM $TableName" -database $dbname -serverinstance $ServerName |export-csv -path $destination$TableName.csv

After export is done, you will have one CSV file in you destination folder.

Next step would be to import this CSV file, loop through it and export new CSV for every 1000 rows. Basically, to split this CSV file into multiple CSV files each holding 1000 rows plus the rows difference in the last CSV.

If you want to find more details about Import-CSV cmdlet and see more usage examples you can read this MS article.

$splitcsv = Import-csv $destination$TableName.csv
for ($counter=0;$counter -lt $splitcsv.length;$counter+=1000)
{ if (($counter + 999) -gt $splitcsv.length) {$max = $splitcsv.length -1}
else
{$max= $counter + 999}
$splitcsv[$counter..$max] | Export-CSV -Path c:\destination\split_by_rows_number_$counter-$max.csv
}

At the end you should have something similar to the following picture in your destination folder.

PowerShell script

param
(
[String] $destination = 'destination',
[String] $ServerName = '.',
[String] $dbname = 'dbname',
[String] $TableName = 'test'

)
invoke-sqlcmd -query "SELECT * FROM $TableName" -database $dbname -serverinstance $ServerName |export-csv -path $destination$TableName.csv
$splitcsv = Import-csv $destination$TableName.csv
for ($counter=0;$counter -lt $splitcsv.length;$counter+=1000)
{ if (($counter + 999) -gt $splitcsv.length) {$max = $splitcsv.length -1}
else
{$max= $counter + 999}
$splitcsv[$counter..$max] | Export-CSV -Path c:\destination\split_by_rows_number_$counter-$max.csv
}

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud