Passing multiple variables from csv file to invoke-sqlcmd

Question

I'm trying to read values from a CSV file, embed them into a INSERT T-SQL statement and run that statement using Invoke-Sqlcmd.

Here's my code:

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location

$InsertQry = "insert into $ImportTable VALUES ('`$(Col1)','`$(Col2)','`$(Col3)','`$(Col4)') "

Import-CSV $ImportFile | ForEach-Object { `
$RowData = "Col1=$($_.{My Ref})","Col2=$($_.{General satisfaction})","Col3=$($_.Helpfulness)","Col4=$($_.Effort)"
    Invoke-Sqlcmd `
    -Database $DBName -ServerInstance $SQLServer `
    -Query $InsertQry `
    -Variable $RowData
  }

The script works fine for rows in the CSV file that contain values for each column. Unfortunately for me, some of the rows in the CSV file contain empty values (so perhaps only the first two columns contain values). These rows fail to be inserted into the table, and generate the following error:

Invoke-Sqlcmd : The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the 'var=value' format for defining a new variable.

The potentially empty columns are all columns that are either empty or contain a single digit number 1 to 5.

I've tried various ways to escape the value, cast it to a different data type, add zero or an empty string to it, null coalesce it, but I cannot get a solution that works.

I have control over the destination table, so I'd be happy to pass zero, empty string, null or any other value as a placeholder for the empty values.

G42 · Answer 1 · 2017-05-25T16:22:53.813

2

Edit - completely new answer

I suck at ForEach-Object. This a foreach loop that checks the value of "General staisfaction" for each line in the CSV, and replaces it with a placeholder string before completing the $RowData variable. Unfortunately I cannot test it here; please let me know how you get on.

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location

$InsertQry = "insert into $ImportTable VALUES ('`$(Col1)','`$(Col2)','`$(Col3)','`$(Col4)') "

$myCSVFile = Import-CSV $ImportFile 

foreach($line in $myCSVFile){

    if($line."General staisfaction" -eq $null -or $line."General staisfaction" -eq ""){
        $line."General staisfaction" = "placeholder"
    }

    $RowData = "Col1=$($line.{My Ref})","Col2=$($line.{General satisfaction})","Col3=$($line.Helpfulness)","Col4=$($line.Effort)"

    Invoke-Sqlcmd -Database $DBName -ServerInstance $SQLServer -Query $InsertQry -Variable $RowData
}

edited May 25 '17 at 16:22

answered May 25 '17 at 15:54

G42

9,791
2
19
34

Thanks for the suggestion. I changed the script to put the command on a single line. This had no effect. Putting quotes around the $RowData variable caused every line in the CSV to fail with the error message I mentioned in the question. – paulH May 25 '17 at 16:04
However, I don't think the issue is in the $RowData variable itself, it is in generating valid values for the Col2, Col3 and Col4 variables. Although I may be wrong in that, as I've not been able to fix the issue! :-) – paulH May 25 '17 at 16:08
Thanks. Right, because it would cast `$RowData` as string - didn't spot that. Based on the issue being caused by empty fields, maybe best approach is dealing with it in the `$RowData = ` part... Does replacing `"Col1=$($_.{My Ref})"` with `"Col1=""$($_.{My Ref})"""` fix it? – G42 May 25 '17 at 16:14
1

Consider using the string static method `[string]::IsNullorEmpty()` or `[string]::IsNullorWhiteSpace()` – Matt May 25 '17 at 16:24
1

Changing to "Col1=""$($_.{My Ref})""" did not work. I believe [string]::IsNullorEmpty() or using the foreach loop would also have worked, so thanks for the suggestions! – paulH May 26 '17 at 10:14

score 2 · Accepted Answer · answered May 25 '17 at 16:36

As per the documentation you are to pass variables in a string array where each element has a "key=value" format. You are building that correctly. Invoke-SQLCMD seems to take offence to null values being passed. The nulls of course are coming from blank entries in your CSV. Assuming you allow nulls in those columns then perhaps you could just adjust the query as each loop pass instead.

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location

$InsertQry = "insert into $ImportTable VALUES ('{0}','{1}','{2}','{3}')"
$propertiesToSplat = @{
    Database = $DBName 
    ServerInstance = $SQLServer
}

Import-CSV $ImportFile | ForEach-Object {
    $propertiesToSplat.Query = $InsertQry -f $_."My Ref", $_."General satisfaction", $_.Helpfulness, $_.Effort
    Invoke-Sqlcmd @propertiesToSplat
}

So at each loop pass we use the format operator to insert the column values into your insert statement. Using curly braces in property names is useful when your properties contain special characters. Since you just have to deal with a space; quotes work just as well.

I also wanted to show you splatting which is a method to pass properties as a hashtable to a cmdlet. This lets you edit props on the fly and keep your lines shorter without having to worry about backticks everywhere.

Thanks Matt. That approach worked, and "splatting" looks interesting too. I think this opens up the possibility of SQL injection attacks so it needs to be used with caution, but that won't be an issue in this particular case. I'm not entirely certain that my original approach was any better in that respect though? — paulH, May 26 '17 at 10:05

Passing multiple variables from csv file to invoke-sqlcmd

2 Answers2