如何比较两个文本文件并在PowerShell中更改数据的符号?

huangapple go评论73阅读模式
英文:

How to compare two text files and and change the sign of the data in powershell?

问题

我有两个文本文件,分别有不同的列,我想比较这两个文本文件的数据,如果第一个文本文件中的一列数据与另一个文本文件的列(P-O-V)数据匹配,那么第二个文本文件的列(JAN TO DEC)的所有符号必须更改(+ TO - OR - TO +)。

示例1.txt

100000 RS
200000 RS
300000 RS

示例2.txt

Sc,一月,二月,三月,四月,五月,六月,七月,八月,九月,十月,十一月,十二月,P-o-v,D L C N

P2,3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,2.90,“001,100100,99001,P000000,U,D-0”,PS

P2,3.24,5.67,2.31,2.82,2.93,5.08,2.40,2.90,2.90,2.90,2.90,2.90,“001,100000,99001,P000000,U,D-0”,PS

P2,-3.24,5.67,-2.31,2.82,-2.93,5.08,2.40,-2.90,2.90,2.90,-2.90,2.90,“001,200000,99001,P000000,U,D-0”,PS

P2,3.24,-5.67,2.31,-2.82,2.93,5.08,-2.40,2.90,2.90,-2.90,2.90,2.90,“001,300000,99001,P000000,U,D-0”,PS

P2,-3.24,5.67,2.31,-2.82,2.93,5.08,-2.40,2.90,-2.90,2.90,-2.90,2.90,“001,300000,99001,P000000,U,D-0”,PS

我希望输出文件为output.txt

Sc,一月,二月,三月,四月,五月,六月,七月,八月,九月,十月,十一月,十二月,P-o-v,D L C N
P2,3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,2.90,“001,100100,99001,P000000,U,D-0”,PS

P2,-3.24,-5.67,-2.31,-2.82,-2.93,-5.08,-2.40,-2.90,-2.90,-2.90,-2.90,-2.90,“001,100000,99001,P000000,U,D-0”,PS

P2,3.24,-5.67,2.31,-2.82,2.93,-5.08,-2.40,2.90,-2.90,-2.90,2.90,-2.90,“001,200000,99001,P000000,U,D-0”,PS

P2,-3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,-2.90,2.90,-2.90,-2.90,“001,300000,99001,P000000,U,D-0”,PS

P2,3.24,-5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,-2.90,“001,300000,99001,P000000,U,D-0”,PS

我尝试了这段代码,但它没有起作用

$sample1 = Get-Content -path "sample1.txt"
$Sample2 = import-csv -Path "sample2.txt" -Header "Sc","January","February","March","April","May","June","July","August","September","October","November","December","P-o-v","D L C N"

if ($sample2 -contains $sample1) {

foreach($row in $sample2)
{
$row.January = "{0:N2}" -f (-1 * [float]$row.January)
$row.February = "{0:N2}" -f (-1 * [float]$row.February)
$row.March = "{0:N2}" -f (-1 * [float]$row.March)
$row.April = "{0:N2}" -f (-1 * [float]$row.April)
$row.May = "{0:N2}" -f (-1 * [float]$row.May)
$row.June = "{0:N2}" -f (-1 * [float]$row.June)
$row.July = "{0:N2}" -f (-1 * [float]$row.July)
$row.August = "{0:N2}" -f (-1 * [float]$row.August)
$row.September = "{0:N2}" -f (-1 * [float]$row.September)
$row.October = "{0:N2}" -f (-1 * [float]$row.October)
$row.November = "{0:N2}" -f (-1 * [float]$row.November)
$row.December = "{0:N2}" -f (-1 * [float]$row.December)
}

$sample2 | Export-CSV -UseQuotes Never -path "output.txt"

Write-Host "文件已修改" -foregroundcolor green
} else {
Write-Host "文件未修改" -ForegroundColor red
}

英文:

I have two text files which different columns and i want to compare two text files data ..and if 1 column data in 1st text file matches with another text file column (P-O-V) data, all the signs of the 2nd text file COLUMNS (JAN TO DEC) data have to change (+ TO - OR - TO +).

sample1.txt

100000 RS
200000 RS
300000 RS

sample2.txt

Sc,January,February,March,April,May,June,July,August,September,October,November,December,P-o-v,D L C N 


P2,3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,2.90,"001,100100,99001,P000000,U,D-0",PS

P2,3.24,5.67,2.31,2.82,2.93,5.08,2.40,2.90,2.90,2.90,2.90,2.90,"001,100000,99001,P000000,U,D-0",PS

P2,-3.24,5.67,-2.31,2.82,-2.93,5.08,2.40,-2.90,2.90,2.90,-2.90,2.90,"001,200000,99001,P000000,U,D-0",PS

P2,3.24,-5.67,2.31,-2.82,2.93,5.08,-2.40,2.90,2.90,-2.90,2.90,2.90,"001,300000,99001,P000000,U,D-0",PS

P2,-3.24,5.67,2.31,-2.82,2.93,5.08,-2.40,2.90,-2.90,2.90,-2.90,2.90,"001,300000,99001,P000000,U,D-0",PS

i want output file as output.txt

Sc,January,February,March,April,May,June,July,August,September,October,November,December,P-o-v,D L C N 
P2,3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,2.90,"001,100100,99001,P000000,U,D-0",PS

P2,-3.24,-5.67,-2.31,-2.82,-2.93,-5.08,-2.40,-2.90,-2.90,-2.90,-2.90,-2.90,"001,100000,99001,P000000,U,D-0",PS

P2,3.24,-5.67,2.31,-2.82,2.93,-5.08,-2.40,2.90,-2.90,-2.90,2.90,-2.90,"001,200000,99001,P000000,U,D-0",PS

P2,-3.24,5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,-2.90,2.90,-2.90,-2.90,"001,300000,99001,P000000,U,D-0",PS

P2,3.24,-5.67,-2.31,2.82,-2.93,-5.08,2.40,-2.90,2.90,-2.90,2.90,-2.90,"001,300000,99001,P000000,U,D-0",PS

i have tried this code..which is not working

$sample1 = Get-Content -path "sample1.txt"
$Sample2 = import-csv -Path "sample2.txt" -Header "Sc","January","February","March","April","May","June","July","August","September","October","November","December","P-o-v","D L C N"

  if ($sample2 -contains $sample1) {

 foreach($row in $sample2)
{
$row.January = "{0:N2}" -f (-1 * [float]$row.January)
$row.February = "{0:N2}" -f (-1 * [float]$row.February)
$row.March = "{0:N2}" -f (-1 * [float]$row.March)
$row.April = "{0:N2}" -f (-1 * [float]$row.April)
$row.May = "{0:N2}" -f (-1 * [float]$row.May)
$row.June = "{0:N2}" -f (-1 * [float]$row.June)
$row.July = "{0:N2}" -f (-1 * [float]$row.July)
$row.August = "{0:N2}" -f (-1 * [float]$row.August)
$row.September = "{0:N2}" -f (-1 * [float]$row.September)
$row.October = "{0:N2}" -f (-1 * [float]$row.October)
$row.November = "{0:N2}" -f (-1 * [float]$row.November)
$row.December = "{0:N2}" -f (-1 * [float]$row.December)
}

$sample2 | Export-CSV -UseQuotes Never -path "output.txt"

Write-Host "File  was modified" -foregroundcolor green
 } else { 
  Write-Host "File  was not modified" -ForegroundColor  red 
 }

答案1

得分: 0

如果我理解你的问题正确,你想要翻转列“一月”至“十二月”中所有数值的符号,如果在该行中,来自sample1.txt的值(仅第一列)可以在文件2的“P-O-V”列中作为第二个值找到。

尝试:

# 从文件2导入CSV数据(根据你的示例,它有标题)
$data = Import-Csv -Path 'D:\Test\sample2.txt'
# 获取文件1的文本并循环处理每一行
Get-Content -path "D:\Test\sample1.txt" | ForEach-Object {
    $valueToMatch = $_ -replace '\D+'  # 只留下数字
    # 尝试在CSV数据中查找具有匹配值的行
    $matchData = $data | Where-Object { ($_.{'P-o-v'} -split ',')[1] -eq $valueToMatch }
    if (@($matchData).Count) {
        foreach ($row in $matchData) {
            # 翻转月份列中的符号
            "January","February","March","April","May","June","July",
            "August","September","October","November","December" | ForEach-Object {
                $row.$_ = '{0:F2}' -f (-[double]$row.$_)
            }
        }
    }
}

# 导出更新后的数据。我将参数 `UseQuotes` 设置为 `AsNeeded`,因为列 `P-o-v` 是一个逗号分隔的字符串,不应拆分成多个字段
$data | Export-Csv -Path 'D:\Test\output.csv' -UseQuotes AsNeeded -NoTypeInformation
英文:

If I understand your question correctly, you want to flip the signs on all values in columns "January".."December" if in that row the value from sample1.txt (first column only) can be found as the second value in the P-O-V column of file 2.
That 'P-O-V' column itself is a comma separated set of values.

Try

# import the csv data from file 2  (as per your example, it has headers)
$data = Import-Csv -Path 'D:\Test\sample2.txt'
# get the text from file 1 and loop over the lines
Get-Content -path "D:\Test\sample1.txt" | ForEach-Object {
    $valueToMatch = $_ -replace '\D+'  # just the number
    # try and find rows in the csv data with a matching value
    $matchData = $data | Where-Object { ($_.'P-o-v' -split ',')[1] -eq $valueToMatch }
    if (@($matchData).Count) {
        foreach ($row in $matchData) {
            # flip the signs in the month columns
            "January","February","March","April","May","June","July",
            "August","September","October","November","December" | ForEach-Object {
                $row.$_ = '{0:F2}' -f (-[double]$row.$_)
            }
        }
    }
}

# export the updated data. I'm setting parameter `UseQuotes` to `AsNeeded`, because column `P-o-v` is a 
# comma delimited string and should not be split apart into several fields
$data | Export-Csv -Path 'D:\Test\output.csv' -UseQuotes AsNeeded -NoTypeInformation

答案2

得分: 0

为了提高性能,建议首先将要比较的数字加载到一个哈希集合(hash set)中:

英文:

<!-- language-all: sh -->

For better performance, I suggest loading the numbers to compare the files by into a hash set first:

# Import the TSV file and load the values from the first column into a hash set.
$hashset = [Collections.Generic.HashSet[string]] (
  Import-Csv -Delimiter &quot;`t&quot; sample1.txt -Header Number, Unused
).Number

# Import the CSV file and, in ever row that contains a hash-set entry
# in the 2nd &quot;,&quot;-separated field of the &quot;p-o-v&quot; column, invert all column
# values that contain a floating-point number.
Import-Csv sample2.txt |
  ForEach-Object {
    if ($hashset.Contains(($_.&#39;p-o-v&#39; -split &#39;,&#39;)[1])) {
      # Invert the signs in all relevant columns.
      foreach ($prop in $_.psobject.Properties) {
        if ($prop.Value -as [double]) {
          $prop.Value = if ($prop.Value.StartsWith(&#39;-&#39;)) { $prop.Value.Substring(1) } else { &#39;-&#39; + $prop.Value }
        }
      }
    }
    $_ # Output the (potentially modified) row object.
  } | ConvertTo-Csv  # replace with Export-Csv ... as needed in your real code.

huangapple
  • 本文由 发表于 2023年3月20日 22:57:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75791892.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定