比较CSV中的两列或更多列，并在新csv中合并匹配数据 [英] Compare two column or more column in CSV and merge match data in new csv

问题描述

这听起来很蠢，但如果有人可以帮助我解决这个问题，我会很感激。
我有两个csv文件和两个共同的列。 （计算机名称和日期）。
一个csv文件有5列，第二个csv有22列。
我想比较两个列数据（计算机名称和日期）与第二个csv文件，如果匹配，则在csv 1（5列）中再添加一个列，并从不同的列插入数据。

第一个csv数据

 计算机名称创建时间
 
 12301825 9/1/2013 1:23 
 
 23304825 8/1/2013 1:23 
 
 43307825 6/1/2013 1:23 
 
 53308825 9/1/2013 1:23 
 
 63305825 5/1/2013 1:45 
 
 73305825 9/1/2013 1:45 
  

第二个csv资料

 电脑名称创建时间值
 
 12301825 9/1/2013 1:23 2 
 
 27704825 8/1/2013 1:23 3 
 
 43307825 6 / 1/2013 1:23 4 
 
 53308825 9/1/2013 1:23 5 
 
 63305825 5/1/2013 1:45 9 
 
 73305825 9/1/2013 1:45 7 
  

我想比较两个csv文件，如果值匹配，然后在第一个csv文件中合并值列。

我试图开始创建脚本，但我不知道如何在powershell中。 / p>

  $ alert = Import-Csv -Path.\alert.csv-HeaderComputer Name|排序对象属性计算机名称-Unique 
 $ threat = Import-Csv.\threat.csv-Header计算机名称| Sort-Object -Property计算机名称-Unique 
 Compare-Object $ alert $ threat -Property计算机名称| Where-Object {$ _。SideIndicator -eq'=>'} | 
 Select-ObjectComputer Name| export-csv .\Difference.csv -NoTypeInfo 
  

这不是我要创建的脚本因为它与我的要求不相关。

  *****更新问题****** 
  

alert.csv包含22列，而threats.csv包含5列。我想将threats.csv与alert.csv进行比较。

当我运行下面的脚本，我得到这样的输出文件。

  $ alert = Import-Csv -Pathd：\ps\alert.csv
 $ threat = Import-Csv -Pathd： \\ ps \threat.csv
 ForEach（$ record in $ threat）{
 $ MatchedValue =（Compare-Object $ alert $ Record -PropertyComputer Name，Creation time-IncludeEqual  - ExcludeDifferent -PassThru）.value 
 $ Record = Add-Member -InputObject $ Record -Type NoteProperty -NameValues-Value $ MathedValue 
} 
 $ threat | Export-Csvd： \ps\threatss.csv-NoTypeInformation 
  

文件数据中的OutPut

  PK！？z？b？[Content_Types] .xml？（？T？N？ Δ········· ？Iy？\？P？ -  ?? M //& o ???? R？D？NJ？54值
??? *？F ??? J /？ht＃ uω r j 1 t  -   -   - （5）]]> U→ g 0& &？U？\Ev ?? ??? d？？？？？？？？？？？？？？？？？？？？？？？？？？？ 
 ???? [=？
& $ ??? W？4？K ?? H？xR？d ??? N？9 ???? 。 
 ??? h ??? m？| 
？} D？g$ 4 ?? F？Y ?? 2＃5〜9 ???? Y] b ?? d ?? @ ??％？s？ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0解决方案
 
 
 

div>

我想你可能会使事情复杂化。尝试此操作：

  $ alert = Import-Csv -Path.\alert.csv
 $ threat = Import-Csv。\threat.csv
 ForEach（$ alert中的记录）{
 $ MatchedValue =（Compare-Object $ threat $ Record -PropertyComputer Name，Creation time -IncludeEqual -ExcludeDifferent -PassThru）.value 
 $ Record = Add-Member -InputObject $ Record -Type NoteProperty -NameValue-Value $ MathedValue 
} 
 $ alert | Export-Csv .\Combined.csv-NoTypeInformation 
  

一步一步，它首先导入alert.csv $ alert数据集。然后对threat.csv和$ threat也一样。然后它遍历$ alert数据集中的每个记录，并在$ threat中查找计算机名和创建时间相等的匹配记录。它从$ threat中的匹配记录中保存Value字段，将该值（如果有）添加到$ alert中的当前记录，并移动到$ alert中的下一个记录。在解析了$ alert中的所有记录之后，将组合数据集导出到Combined.csv。

如果您有任何问题或疑问，请告诉我们。

It sounds stupid but I would appreciate if anyone can help me with this issue. I have two csv file and both have 2 common column. (computer name and date). one csv file have 5 column and second csv have 22 column. I want to compare two column data (computer name & date) with second csv file and if it match then add one more column in csv 1 (5 column) and insert data from different column.

1st csv data

Computer Name   Creation time

12301825        9/1/2013 1:23

23304825        8/1/2013 1:23

43307825        6/1/2013 1:23

53308825        9/1/2013 1:23

63305825        5/1/2013 1:45

73305825        9/1/2013 1:45

2nd csv data

Computer Name   Creation time   Value

12301825        9/1/2013 1:23   2

27704825        8/1/2013 1:23   3

43307825        6/1/2013 1:23   4

53308825        9/1/2013 1:23   5

63305825        5/1/2013 1:45   9

73305825        9/1/2013 1:45   7

I want to compare both column in both csv file and if value match then merge value column in first csv file.

I tried to start creating script but i don't know how it can be done in powershell.

$alert=Import-Csv -Path ".\alert.csv" -Header "Computer Name" | Sort-object Property "Computer Name" -Unique 
$threat = Import-Csv ".\threat.csv" -Header "Computer Name" |Sort-Object -Property "Computer Name" -Unique
Compare-Object $alert $threat -Property  "Computer Name" |  Where-Object{$_.SideIndicator -eq '=>'} | 
    Select-Object  "Computer Name" | export-csv .\Difference.csv -NoTypeInfo

It is not a script which I want to create because it is not relevant with my requirement.

*****Updated question******

alert.csv contain 22 column and threats.csv contain 5 column. I want to compare threats.csv with alert.csv.

When i run below script i am getting output file like this.

$alert=Import-Csv -Path "d:\ps\alert.csv"
$threat = Import-Csv -Path "d:\ps\threat.csv"
ForEach($Record in $threat){
    $MatchedValue = (Compare-Object $alert $Record -Property "Computer Name","Creation time" -IncludeEqual -ExcludeDifferent -PassThru).value
    $Record = Add-Member -InputObject $Record -Type NoteProperty -Name "Values" -Value $MathedValue
}
$threat|Export-Csv "d:\ps\threatss.csv" -NoTypeInformation

OutPut in file data

PK!?z?b??[Content_Types].xml ??(??T?N?0?#???(q?!?G?|??7?U?????=??V?J???$?????{2?4.[CB|)??Hd?u0?/J?????Iy?\?P?-??M//&o????R?D?NJ?54  Values
"???*?F???J/???ht#u??rj1?t???5??U?g?0??8I??s\0???????*Fg?""V.???a?CUY
&?U?\Ev??   ???d*?  ???W?;????Q??az?8<n?o3?????F`?n???!"    
????[=? 
&$???W?4?K??H"?xR??d?????N?9????Aw(7ey/???O??   
???h?????m?|    
?}D?g"$?4??F?Y??.2#???5?9????Y]b??d??@??%?s?"??0?tB??)???[??????????PK!?p??Hxl/workbook.xml?Q?N?0?#????I????TB??!Q??7?U?l????U  

解决方案

I think you may be over complicating things. Try this:

$alert=Import-Csv -Path ".\alert.csv"
$threat = Import-Csv ".\threat.csv"
ForEach($Record in $alert){
    $MatchedValue = (Compare-Object $threat $Record -Property "Computer Name","Creation time" -IncludeEqual -ExcludeDifferent -PassThru).value
    $Record = Add-Member -InputObject $Record -Type NoteProperty -Name "Value" -Value $MathedValue
}
$alert|Export-Csv ".\Combined.csv" -NoTypeInformation

Stepping through that, it first imports alert.csv to the $alert dataset. Then does the same for threat.csv and $threat. It then steps through each record in the $alert dataset, and looks for matching records in $threat where Computer Name and Creation Time are equal. It saves the Value field from the matching record in $threat, adds that value (if any) to the current record in $alert, and moves on to the next record in $alert. After parsing through all records in $alert it exports the combined dataset to Combined.csv.

Let me know if you have any issues or questions.

这篇关于比较CSV中的两列或更多列，并在新csv中合并匹配数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！