Insert data row wise in a SQL Table from a CSV using PowerShell

Question

So I have this particular scenario where I need to insert data from multiple CSV files into multiple SQL tables. I have come across this SO link, which inserts data on a row by row basis. Below is the code-snippet from the link -

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

The problem which I am facing is, I have multiple CSVs with the different number of columns in them. So the option of hard-coding the code-snippet VALUES('$($_.Column1)','$($_.Column2)') doesn't exist.

Further, there are tables which contain more than 100 columns. So, writing VALUES('$($_.Column1)','$($_.Column2)')... so on upto '$($_.Column100)' is also not feasible.

What I have done for the same is stored the column names from the CSVs into a PowerShell array like this -

$File = "C:\Users\vivek.singh\Desktop\ALL_EMAILS.csv"
$csvColumnNames = (Get-Content $File | Select-Object -First 1).Split(",")

Now $csvColumnNames, has all the column names for ALL_EMAILS table. I am trying to fit it in the solution -

Import-CSV .\yourcsv.csv | ForEach-Object {Invoke-Sqlcmd `
  -Database $database -ServerInstance $server `
  -Query "insert into $table VALUES ('$($csvColumnNames[0])','$($csvColumnNames[1])',..'$($csvColumnNames[$csvColumnNames.Length])')"
  }

But it doesn't seem to work. I have been struggling with this for quite some time now and running out of ideas. A fresh pair of eyes will be greatly appreciated.

@LotPings' solution works great. But as he suggested it might fail if records have ' in them, which might occur in columns like FirstName, LastName etc. Anyone looking for an alternate solution, can refer to this answer. Not related to my question, but still a great way to insert data into SQL tables from CSVs. — Vivek Kumar Singh

Unknown Unknown · Accepted Answer · 2018-05-09T13:04:51

Try this

Import-CSV .\yourcsv.csv | ForEach-Object {
    $AllValues = "'"+($_.Psobject.Properties.Value -join "','")+"'"
    Invoke-Sqlcmd -Database $database -ServerInstance $server `
    -Query "insert into $table VALUES ($AllValues)"
}

It uses the -join operator to concatenate all values of the current row (with a leading and trailing ' to build the $AllValuesvariable which then can be inserted into the sql command.

It's up to you to check if Csv headers match the sql column names.

To get column names once Import-Csv-ed you can use

$CSV.Psobject.Properties.Name

Insert data row wise in a SQL Table from a CSV using PowerShell

1 Answers