Sometimes as part of my daily job I need to transform a SQL Server data set (or recordset) to json or a flat file. Mostly I use an ETL Tool or a SQL Server json query to get the job done. To get more knowledge about Powershell I was curious how to achieve this in Powershell. So I decided to start with something small, I want to export some fields of the physical SQL Server database files and to convert this to json and csv. It should be easy and small, because I just want to learn something new. Later on I will test my solution with more records, just for fun and to see the performance.

Below the script to fetch the needed dataset from a SQL Server database. In this case the master database, in the below script you should change the server, login and password for a SQL Server login. Or you can use the windows connection string.

# Open a database connection
[object]$DBConnection = New-Object System.Data.SqlClient.SqlConnection;

# Connection string for a SQL Server account
$DBConnection.ConnectionString = "Server=;Database=master;User ID=;Password=;Connection TimeOut=0";

# Connection string for a windows account
# $DBConnection.ConnectionString = "Data Source=;Initial Catalog=master;Integrated Security=SSPI;";
$DBConnection.Open();

# Fetch SQL Server physical file details
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand;
$SQLCmd.Connection = $DBConnection;
$SQLCmd.CommandText = "select file_id, name, type_desc, physical_name from sys.database_files;";
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCmd;
$DataSet = New-Object System.Data.Dataset;
$DataAdapter.Fill($dataset) | Out-Null;

The table below shows the result of the above query.

Converting a recordset to a json structure is very easy with ConvertTo-Json. And with Out-File I store the json structure on disk. My first approach was to do this:

$DataSet.Tables[0].Rows | ConvertTo-Json | Out-File -FilePath "PS_2_JSON.json" -Force;

But in the file I saw all kind of properties - e.g. CaseSensitive and ChildRelations - related to the data set. Those properties are interesting but not want I need. I was already familiar with Select-Object and thought this should do the trick. So, I changed the code:

$DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Json `
                                        | Out-File -FilePath "PS_2_JSON.json" -Force;

And now the correct json was created as shown below.

[
    {
        "file_id":  1,
        "name":  "data_0",
        "type_desc":  "ROWS",
        "physical_name":  "5036005c-b308-43b3-80eb-54f5b6cea0ad_1.mdf"
    },
    {
        "file_id":  2,
        "name":  "log",
        "type_desc":  "LOG",
        "physical_name":  "5036005c-b308-43b3-80eb-54f5b6cea0ad_2.ldf"
    }
]

The next step should be easy with ConvertTo-Csv. And it is.

$DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Csv -NoTypeInformation `
                                        | Out-File -FilePath "PS_2_CSV_With_Hdr.csv" -Force -Encoding utf8;

With the above code I created a csv with a header and I used -NoTypeInformation to supress the line "#TYPE Selected.System.Data.DataRow". If there is a need to create a csv file without a header, you can change the code as mentioned below. In this case I am using an array and skip the first element.

($DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Csv -NoTypeInformation)[1..$($DataSet.Tables[0].Rows.Count)] `
                                        | Out-File -FilePath "PS_2_CSV_No_Hdr.csv" -Force -Encoding utf8;

At the end I tested my solution with 1 million rows and the performance is reasonable as shown below:

Open database connection to fetch some records: 15:58:21:947 +01:00
Fetched some records: 15:58:46:262 +01:00
Created json: 16:01:06:645 +01:00
Created csv without header: 16:03:47:548 +01:00
Created csv with header: 16:06:39:919 +01:00
Done cleanup: 16:09:50:597 +01:00

Clear-Host;
Push-Location $PSScriptRoot;
[string]$DTFormat="HH:mm:ss:fff K";

write-host "Open database connection to fetch some records:" $(Get-Date -format $DTFormat);

# Open a database connection
[object]$DBConnection = New-Object System.Data.SqlClient.SqlConnection;
$DBConnection.ConnectionString = "Server=;Database=master;User ID=Password=;Connection TimeOut=0";
$DBConnection.Open();

# Fetch SQL Server physical file details
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand;
$SQLCmd.Connection = $DBConnection;
#$SQLCmd.CommandText = "select file_id, name, type_desc, physical_name from sys.database_files;";
$SQLCmd.CommandText = "with tally
                       as
                       (
                         select top (500000) RowNbr = row_number() over (order by getdate())
                         from dbo.syscolumns c1
                           cross join dbo.syscolumns c2
                        )
                        , db
                        as
                        (
                          select file_id, name, type_desc, physical_name
                          from sys.database_files
                        )
                        select tally.*, db.*
                        from tally
                                cross join db;"
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCmd;
$DataSet = New-Object System.Data.Dataset;
$DataAdapter.Fill($dataset) | Out-Null;

write-host "Fetched some records:" $(Get-Date -format $DTFormat);

# Convert recordset to JSON
$DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Json `
                                        | Out-File -FilePath "PS_2_JSON.json" -Force;

write-host "Created json:" $(Get-Date -format $DTFormat);

# Convert recordset to CSV without type information and header
($DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Csv -NoTypeInformation)[1..$($DataSet.Tables[0].Rows.Count)] `
                                        | Out-File -FilePath "PS_2_CSV_No_Hdr.csv" -Force -Encoding utf8;

write-host "Created csv without header:" $(Get-Date -format $DTFormat);

# Convert recordset to CSV without type information, file contains a header
$DataSet.Tables[0].Rows | Select-Object file_id, name, type_desc, physical_name `
                                | ConvertTo-Csv -NoTypeInformation `
                                        | Out-File -FilePath "PS_2_CSV_With_Hdr.csv" -Force -Encoding utf8;

write-host "Created csv with header:" $(Get-Date -format $DTFormat);

# Cleanup the used objects
$DBConnection.Close();
$DataSet.Dispose();
$DataAdapter.Dispose();
$SQLCmd.Dispose();
$DBConnection.Dispose();

write-host "Done cleanup:" $(Get-Date -format $DTFormat);

Feel free to send me an email with your comments or remarks

• erwin@data-pathfinder.nl