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

Question

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.

Thanks. That worked. It does require the quotes however, which I think is what I missed when I initially tried that. — PGHE
– PGHE, Commented Jul 11, 2017 at 0:08

Mark Wragg · Accepted Answer · 2017-07-11 05:40:14Z

2

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')"
}

answered Jul 11, 2017 at 5:40

Mark Wragg

23.6k7 gold badges48 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related