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.

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.