CODEWITHSUNDEEP
sql-serverpowershellcsv
PGHE
PGHE

Reputation: 1962

Importing data from CSV files into SQL Server with additional column using PowerShell

I would like to import data from my CSV files using PowerShell. The answer posted here: How to import data from .csv in SQL Server using PowerShell? works like a charm.

However, I'd like to build on this solution. If the files contain 2 columns, e.g. Feature1, Feature2, I'd like the table to have a third column e.g. Name. The aim is to load multiple files corresponding to different Names into a single table.

Feature1 | Feature2 | Name
Value1   | Value2   | NameA
Value3   | Value4   | NameA
...
Value5   | Value6   | NameB

If I give PowerShell the variable

$NameValue = "NameA"

How do I include this correctly in the command:

Import-CSV .\NameA.csv | ForEach-Object {Invoke-Sqlcmd `
  -Database $database -ServerInstance $server `
  -Query "insert into $table VALUES ('$($_.Feature1)','$($_.Feature2)', '$($NameValue.Name)')"
  }

Currently, this just fills in the Feature columns, but the Name column is blank.

Upvotes: 3

Views: 2536

Answers (1)

Mark Wragg
Mark Wragg

Reputation: 23355

This should do it (you shouldn't need to use the subexpression $() operator unless you're accessing the property of a variable from within a string):

Import-CSV .\NameA.csv | ForEach-Object {Invoke-Sqlcmd `
  -Database $database -ServerInstance $server `
  -Query "insert into $table VALUES ('$($_.Feature1)','$($_.Feature2)', '$NameValue')"
}

Upvotes: 2

Related Questions