Powershell: Conditionally changing objects in the pipeline

Question

I'm converting a CSV to SQL inserts and there's a null-able text column which I need to quote in case it is not NULL. I would write something like the following for the conversion:

Import-Csv data.csv | foreach { "INSERT INTO TABLE_NAME (COL1,COL2) VALUES ($($_.COL1),$($_.COL2));" >> inserts.sql }

But I can't figure out how to add an additional tier into the pipeline to look if COL2 is not equal to 'NULL' and to quote it in such cases. How do I achieve such behavior?

Answer 1

In the code block of the foreach-object cmdlet you can have multiple statements, something like:

Import-Csv data.csv | 
foreach {
  if ($_.COL1 -ne $null) {
    $c1 = "'$($_.COL1)'"
  } else {
    $c1 = $_.COL1
  }
  "INSERT INTO TABLE_NAME (COL1,COL2) VALUES ($c1,$($_.COL2));" >> inserts.sql
}