PowerShell and SQL Server – Importing Data

In order to import data into an SQL Server database, it must be in a suitable format. CSV files are one such format.  A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. They store tabular data in plain text. The first row in a CSV file often contains headers for each column of data. Each row in the file thereafter is a record of related data. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

"firstname","lastname","title","dob"
"Zoe","Davis","Miss","1979-07-11"
"Tom","Ingram","Mr","1971-10-04"
"Karen","Thomas","Mrs","1969-03-08"
"Samantha","Yates","Miss","1995-08-27"

Below is an example of how PowerShell can be used to import data from a CSV file called ‘personimport.csv’, into a table called ‘person’, the initial contents of which is shown below.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19

Firstly, the CSV file path is set, together with variables to facilitate the database connection and a record count. A check is then made to see if the CSV file exists. If the file exists, its contents is imported into a variable. Each row of data is then processed one by one. For each row of data, an ‘Insert‘ statement is constructed and then executed to add the record to the database. A count is incremented for each record added to the database and feedback is given, once all data has been processed, as to the total number of records imported.

# Clear the console window.
Clear-Host

# File path.
$filePath = "c:\demo\personimport.csv"

# Database variables.
$server = "MSSQLSERVERDEMO"
$database = "Demo"
$username = "DemoUN"
$password = "DemoPW"

# Record count.
$recordCount = 0

# Check to see if the CSV file exists.
if (Test-Path $filePath)
{

    # Import CSV file.
    $csv = Import-Csv $filePath

    # Process the contents of the CSV file.
    foreach ($person in $csv)
    {
        
        try
        {

            # Construct the insert statement.
            $query = "INSERT INTO [dbo].[person] "
            $query += "(firstname, lastname, title, dob) "
            $query += "VALUES ('$($person.firstname)', "
            $query += "'$($person.lastname)', "
            $query += "'$($person.title)', "
            $query += "'$($person.dob)') "

            # Execute the query.            
            Invoke-Sqlcmd -ServerInstance $server -Database $database `
            -Username $username -Password $password -Query $query -ErrorAction Stop
            
            # Add one to the record count.
            $recordCount += 1
            
        }
        catch
        {

            # Confirm error importing person information.
            Write-Host "Error importing person information."

        }

    }
    
    # Provide feedback on the number of records imported.
    if ($recordCount -eq 0)
    {

        Write-Host "No person records have been imported."

    }
    elseif ($recordCount -eq 1)
    {

        Write-Host "$recordCount person record has been imported."

    }
    else
    {

        Write-Host "$recordCount person records have been imported."

    }

}
else
{

    # Message stating CSV file could not be located.
    Write-Host "Could not locate the CSV file."

}

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19
6 Zoe Davis Miss 1979-07-11
7 Tom Ingram Mr 1971-10-04
8 Karen Thomas Mrs 1969-03-08
9 Samantha Yates Miss 1995-08-27

SQL Server Stored Procedures

A stored procedure contains prepared SQL code that is stored directly in an SQL Server database and can be called when needed. This allows for the SQL to be reused as many times as needed and also makes maintenance of the code much easier, because it only has to be updated in one place. The other advantage of stored procedures is that they help protect against SQL injection attacks, where an attacker tries to gain unauthorised access to data.

The above example can be re-written to utilise a stored procedure, whilst still achieving the same result. The SQL is removed from the script and placed in a stored procedure called 'AddPerson'.

CREATE PROCEDURE [dbo].[AddPerson] 
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Title nvarchar(50),
    @Dob date
AS
BEGIN

    INSERT INTO [dbo].[person] (firstname, lastname, title, dob)
    VALUES (@FirstName, @LastName, @Title, @Dob)

END

This stored procedure is then utilised as follows to produce the same result as above.

# Clear the console window.
Clear-Host

# File path.
$filePath = "c:\demo\personimport.csv"

# Database variables.
$server = "MSSQLSERVERDEMO"
$database = "Demo"
$username = "DemoUN"
$password = "DemoPW"

# Database connection string.
$dbConnect = "Server = "
$dbConnect += $server
$dbConnect += "; Database = "
$dbConnect += $database
$dbConnect += "; User Id = "
$dbConnect += $username
$dbConnect += "; Password = "
$dbConnect += $password
$dbConnect += ";"

# Record count.
$recordCount = 0

# Check to see if the CSV file exists.
if (Test-Path $filePath)
{

    # Import CSV file.
    $csv = Import-Csv $filePath

    try
    {

        # Connect to the database.
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection("$($dbConnect)")
        $sqlConn.Open()

        # Process the contents of the CSV file.
        foreach ($person in $csv)
        {

            # Create SQL command and specify the stored procedure.
            $cmd = $sqlConn.CreateCommand()
            $cmd.CommandType = "StoredProcedure"
            $cmd.CommandText = "dbo.AddPerson"

            # Add the parameters and set the values.
            $pFirstName = $cmd.Parameters.Add('@FirstName',[string])
            $pFirstName.Value = $person.firstname
            $pLastName = $cmd.Parameters.Add('@LastName',[string])
            $pLastName.Value = $person.lastname
            $pTitle = $cmd.Parameters.Add('@Title',[string])
            $pTitle.Value = $person.title
            $pDob = $cmd.Parameters.Add('@Dob',[DateTime])
            $pDob.Value = $person.dob

            # Execute the stored procedure.
            $cmd.ExecuteNonQuery()

            # Add one to the record count.
            $recordCount += 1

        }

        # Close the database connection.
        $sqlConn.Close()

    }
    catch
    {

        # Confirm error importing person information.
        Write-Host "Error importing person information."

    }

    # Provide feedback on the number of records imported.
    if ($recordCount -eq 0)
    {

        Write-Host "No person records have been imported."

    }
    elseif ($recordCount -eq 1)
    {

        Write-Host "$recordCount person record has been imported."

    }
    else
    {

        Write-Host "$recordCount person records have been imported."

    }

}
else
{

    # Message stating CSV file could not be located.
    Write-Host "Could not locate the CSV file."

}