使用PowerShell从CSV中将数据按行明智地插入到SQL表中 [英] Insert data row wise in a SQL Table from a CSV using PowerShell

问题描述

因此，在这种特殊情况下，我需要将多个CSV文件中的数据插入到多个SQL tables中.我遇到了此SO链接，逐行插入数据.以下是链接中的代码段-

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

我面临的问题是，我有多个CSVs，其中的列数不同.因此，不存在对代码段 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.

此外，有些表包含100多个列.因此，编写 VALUES('$($_.Column1)','$($_.Column2)')... so on upto '$($_.Column100)' 也是不可行的.

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

我为此所做的工作是将CSVs中的列名存储到这样的PowerShell数组中-

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(",")

现在$csvColumnNames，具有ALL_EMAILS表的所有列名.我正在尝试使其适合解决方案-

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.

推荐答案

尝试一下

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

它使用-join运算符来连接当前行的所有值(以'开头和结尾来构建$AllValues变量，然后可以将其插入sql命令中.

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.

由您检查Csv标头是否与sql列名匹配.

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

要一次Import-Csv -ed获取列名，可以使用

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

$CSV.Psobject.Properties.Name

这篇关于使用PowerShell从CSV中将数据按行明智地插入到SQL表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！