在 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屋!