在 PowerShell 中查找 [英] Vlookup in PowerShell

问题描述

我需要这个 vlookup 代码才能在 PowerShell 中工作.

=VLOOKUP(A1,File_2.csv!$B:$C,2,false)

基本上，我有 2 个 CSV，我需要从 File_2.CSV(第 2 列)中的特定列中检索数据并将其插入到 File_1.CSV 中的新列中.

File_1.csv

<前>身份证名称--- ------1 阿尔法2 布拉沃3 查理

File_2.csv

<前>身份状态--- -------1 活跃2 不活动3 主动

预期结果

<前>ID 名称 状态--- ------ -------1 阿尔法活跃2 Bravo 不活跃3 查理活跃

我的代码如下:

#启动Excel$path = 获取位置$excel = New-Object -ComObject Excel.Application$excel.Visible = $false$workbook = $excel.Workbooks.Open("$path\file_1.csv")$worksheet = $workbook.Worksheets.Item(1)#添加新列$worksheet.Cells.Item(1, 3) = "状态"#count列A1$colCountA1 = $worksheet.Range("A1").CurrentRegion.Rows.Count#Setformula 查找$vlookup = "=VLOOKUP(B2,File_2.csv!`$B:`$C,2,false)"#将公式从 C2 添加到 A1 的总计数$worksheet.Range("C2:C$colCountA1").Formula = $vlookup$workbook.Close($false)$excel.退出()

我目前得到的结果:

<前>ID 名称 状态--- ------ -------1 阿尔法#NAME?2 厉害了#NAME?3 查理#NAME?

但是当我在 Excel 中手动输入 vlookup 公式时，它可以完美运行.不知道我哪里弄错了.

解决方案

将其中一个 CSV 读入一个将 ID 映射到值的哈希表:

$data = @{}导入-CSV 'File_2.csv' |ForEach-Object { $data[$_.ID] = $_.STATUS }

然后使用计算属性将这些值添加为来自另一个 CSV 的数据的新字段:

导入-Csv 'File_1.csv' |选择对象 *, @{n='STATUS';e={if ($data.Contains($_.ID)) {$data[$_.ID]} else {'N/A'}}}

如果您需要将结果以 CSV 格式返回，请将结果传送到 Export-Csv.

I need this vlookup code to work in PowerShell.

=VLOOKUP(A1,File_2.csv!$B:$C,2,false)

Basically, I have 2 CSVs and I need to retrieve the data from a specific column in File_2.CSV (2nd Column) and insert it in a new column in File_1.CSV.

File_1.csv

ID   NAME
--- ------
1    Alpha
2    Bravo
3    Charlie

File_2.csv

ID   STATUS
---  -------
1    Active
2    Inactive
3    Active

Expected Result

ID   NAME     STATUS
---  ------   -------
 1   Alpha    Active
 2   Bravo    Inactive
 3   Charlie  Active

My Code as follows:

#Initiate Excel
$path = Get-Location
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open("$path\file_1.csv")
$worksheet = $workbook.Worksheets.Item(1)

#Add new column
$worksheet.Cells.Item(1, 3) = "Status"

#count column of A1
$colCountA1 = $worksheet.Range("A1").CurrentRegion.Rows.Count

#Setformula vlookup
$vlookup = "=VLOOKUP(B2,File_2.csv!`$B:`$C,2,false)"

#Add formula from C2 to Total Count of A1
$worksheet.Range("C2:C$colCountA1").Formula = $vlookup

$workbook.Close($false)
$excel.Quit()

The results im getting as of the moment:

ID   NAME     STATUS
---  ------   -------
 1   Alpha    #NAME?
 2   Bravo    #NAME?
 3   Charlie  #NAME?

but when I manually input the vlookup formula in Excel it works flawlessly. Not sure where im getting it wrong.

解决方案

Read one of the CSVs into a hashtable mapping the ID to the value:

$data = @{}
Import-Csv 'File_2.csv' | ForEach-Object { $data[$_.ID] = $_.STATUS }

then use a calculated property to add those values as a new field to the data from the other CSV:

Import-Csv 'File_1.csv' |
    Select-Object *, @{n='STATUS';e={if ($data.Contains($_.ID)) {$data[$_.ID]} else {'N/A'}}}

Pipe the result into Export-Csv if you need the result back in a CSV.

这篇关于在 PowerShell 中查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！