用Excel文件中的数据更新System.Data.DataTable

Question

我有一个powershell脚本，它从SQL数据库中提取数据并填充数据表，然后将内容写入csv文件。该脚本如下：

$dataSource = "DESKTOP-9CRH1HF\SQLEXPRESS"
$user = "sa"
$pwd = "Not4U2c@Al!"
$database = "Xperdyte"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"

$query = "SELECT * from dbo.v_SpecProd_Export"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$table | Export-Csv -path c:\temp\temp.csv -NoTypeInformation -Delimiter ";"
Get-Content c:\temp\temp.csv | select -Skip 1  | % {$_ -replace '"', ""} | Set-Content c:\temp\Xperdyte.csv
move-item "c:\temp\Xperdyte.csv" ("c:\1_PRODUCTION\ARMOR\Bom\DocProd_Africa_{0:yyyyMMdd_HHmmss}.csv" -f (get-date))
$connection.Close()

CSV文件中的输出如下：

1008891;20.000000;Roll;20;;TSB876ZA;06;;L15832ZA;Clear Leader Roll 300mm x 400m 22mic;4.060000;M   ;1;;S80795XL;0
1008891;20.000000;Roll;20;;TSB876ZA;06;;L17440ZA;Transparent leader Rolls 48mm x 600m;4.060000;M   ;1;;S80795XL;0
1008891;20.000000;Roll;20;;TSB876ZA;06;;S80795XL;Jumbo APR6 Black 1010 mm;1608.400000;M   ;1;;S80795XL;0
1008891;20.000000;Roll;20;;TSB876ZA;06;;TAD118ML;Adhesive Tape 24mm X 50m;4.060000;M   ;1;;S80795XL;0
1008891;20.000000;Roll;20;;TSB876ZA;06;;TAD123ML;Adhesive Transparent 24mm X 350m;12.180000;M   ;1;;S80795XL;0
1008891;20.000000;Roll;30;;TSB876ZA;06;;ML-TTT299;Local Semi Gloss Label 51mm Round;20.000000;PC  ;1;;S80795XL;0

第四列包含有效数据，正确的数据存储在共享驱动器上的excel文件中。将正确的数据导入SQL不是一个选项，因为我试图在不影响SQL的情况下集成两个系统。每个Excel文件都是根据产品代码命名的，产品代码也是SQL数据库中的值( csv文件中的第6列)。每个Excel文件的内容如下，每个文件包含不同的数据，具体取决于产品代码：

1005    TMA208  CORE.CARDBOARD 25.4X33X1000MM (Black Stripes)   50  m   CORE_WI 10
2020    S80812XL    JUMBO Q812 NOIR-4,5-WAX3 1010MM X 18500M    23684.211   m   FILM_OUT    20
2025    TAD137ML    ADHESIVE SOLFREE 25MM X 330M    0.152   pc  AL1N1   20
2035    TAD123ML    ADHESIVE TRANSPARENT 25MM X 330M    0.303   pc  AL2N1   20
2040    TAD123ML    ADHESIVE TRANSPARENT 25MM X 330M    0.152   pc  AL3N1   20
2060    L15832ZA    TRANSPARENT LEADER ROLLS 300X250  18 MU/2   50  m   LEAD1   20
2065    L15834ZA    LEADER.METALLIZED.300MM 50  m   TRAIL1  20
2070    L17440ZA    BRIDGE.TRANSPARENT.48MM 50  m   BRIDGE  20
3026    TTT269  LABEL ADHESIVE.WHITE ROUND DIAM.53  1000    pc  LAB_ROLL    30
3027    ETI742  LABEL ADHESIVE UNIVERSAL 110X100MM (1F) 40  pc  LAB_BOX1    30
3040    TSE306  BOX USA 286X237X160MM   40  pc  BOX_1   30
3060    TFL104  FILM RETRACTABLE 310MMX1550M    129.029 m       30
3065    FSB001  STRAPPING BAND 9MM X 4000M (VIRGIN QUALITY, YELLOW) 35.76   m       30
3070    OPPBRN-48X900   OPP BROWN TAPE 48MMX900Y    16.24   m   ADH_PACK    30

我想要实现的是读取System.Data.Datatable (第6列)以查找产品代码并将其存储为变量，然后搜索同名的Excel文件，读取excel文件的内容，并匹配存储数据表中的第9列和excel文件中的第2列。在excel文件中具有返回列7，并使用此值更新数据表中的第4列。在最终编写CSV文件之前，我需要对数据表中的每一行执行此操作。

我发现了下面的代码，我认为可以根据我的需要操作，并将它集成到我的ps脚本中，但我不确定这是否是正确的方法，还是有一个更快的方法来做到这一点？

#Declare the file path and sheet name
$file = "C:\Users\kfeb\Documents\Textfile\ExcelFile.xlsx"
$sheetName = "Sheet1"
#Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false
#Count max row
$rowMax = ($sheet.UsedRange.Rows).count
#Declare the starting positions
$rowName,$colName = 1,1
$rowAge,$colAge = 1,2
$rowCity,$colCity = 1,3
#loop to get values and store it
for ($i=1; $i -le $rowMax-1; $i++)
{
$name = $sheet.Cells.Item($rowName+$i,$colName).text
$age = $sheet.Cells.Item($rowAge+$i,$colAge).text
$city = $sheet.Cells.Item($rowCity+$i,$colCity).text

Write-Host ("My Name is: "+$name)
Write-Host ("My Age is: "+$age)
Write-Host ("I live in: "+$city)
}
#close excel file
$objExcel.quit()

有人能理解这个问题吗?如果是的话，能提供一些方向吗？

谢谢，史蒂文

Answer 1

培根比特完全在正确的轨道上，只是丢失了一些多个文件。而且，如果您使用的是现代版本的PowerShell，将表导入到PowerShell中要容易得多。因此，我会制作一个HashTable，并根据需要将HashTables数据作为嵌套的HashTables加载到其中。

$RawCSV = GC c:\temp\Xperdyte.csv

$Headers = 1..$RawCSV[0].Split(';').Count|%{"Column$_"}

$CSV = $RawCSV | ConvertFrom-Csv -Delimiter ';' -Header $Headers

$Lookup = @{}
$XL = New-Object -ComObject Excel.Application
ForEach($File in ($CSV.Column6|Select -Unique)){
    #Create record in main HT for this Excel file with an empty HT for the value
    $Lookup.Add($File,@{})

    #Open the workbook and import the data
    $WB = $XL.Workbooks.Open("C:\Users\kfeb\Documents\Textfile\$File.xlsx")
    $WB.ActiveSheet.UsedRange.Rows | ForEach{$Lookup[$File].($_.Cells.Item(2).Value2) = $_.Cells.Item(7).Value2}

    #Close the workbook
    $wb.Close($false) | Out-Null
}
#Close Excel
$XL.Quit()|Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($XL) | Out-Null
Remove-Variable XL

$Output = ForEach($Item in $CSV){
    #If the item code can be found in the lookup table update Column4
    If($Item.Column9 -in $Lookup[($Item.Column6)].Keys){$Item.Column4 = $Lookup[($Item.Column6)][($Item.Column9)]}
    ($Headers|%{$Item.$_}) -join ';'
}

$Output | Set-Content ("c:\1_PRODUCTION\ARMOR\Bom\DocProd_Africa_{0:yyyyMMdd_HHmmss}.csv" -f (get-date))

Answer 2

因此，您希望从CSV获取第6列(产品代码)，将其匹配到第9列(也是产品代码)，并从Excel文件中查找第7列，并将其保存到CSV中的第4列。

首先，从Excel表中获取所有数据。我假设Excel文件中没有标题行，您需要正确地指定工作表名称和文件名。

#Declare the file path and sheet name
$file = "C:\Users\kfeb\Documents\Textfile\ExcelFile.xlsx"
$sheetName = "Sheet1"
#Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false
#Count max row
$rowMax = ($sheet.UsedRange.Rows).count

$colProductCodeExcel = 9
$colOtherColumnExcel = 7

$SheetData = @{}

for ($row = 1; $row -le $rowMax; ++$row) {
    $SheetProductCode = $sheet.Cells.Item($i,$colProductCodeExcel).text
    $SheetOtherColumn = $sheet.Cells.Item($i,$colOtherColumnExcel).text
    $SheetData[$SheetProductCode] = $SheetOtherColumn
}

$objExcel.Quit()

现在，$SheetData是一个查找表。如果您有重复的产品代码，$SheetData将在工作表中具有最后一个其他列值。

现在，从SQL中获取数据：

$dataSource = "DESKTOP-9CRH1HF\SQLEXPRESS"
$user = "sa"
$pwd = "Not4U2c@Al!"
$database = "Xperdyte"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"

$query = "SELECT * from dbo.v_SpecProd_Export"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$connection.Dispose()

$colProductCodeSql = 6
$colOtherColumnSql = 4

$table | 
    ForEach-Object {
        $_[$colOtherColumnSql] = $SheetData[$_[$colProductCodeSql]]
    } |
    ConvertTo-Csv -NoTypeInformation -Delimiter ';' | 
    Select-Object -Skip 1 |
    ForEach-Object { $_ -replace '"', "" } |
    Set-Content c:\temp\Xperdyte.csv

如果$colOtherColumnSql的数据类型与$SheetData[$colProductCodeSql]不匹配，则可能会出现错误或类型转换问题。注意前面或后面的空间。

我没有测试任何上述代码。如果有臭虫，我也不会感到惊讶。