英文:
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 "`t" sample1.txt -Header Number, Unused
).Number
# Import the CSV file and, in ever row that contains a hash-set entry
# in the 2nd ","-separated field of the "p-o-v" column, invert all column
# values that contain a floating-point number.
Import-Csv sample2.txt |
ForEach-Object {
if ($hashset.Contains(($_.'p-o-v' -split ',')[1])) {
# Invert the signs in all relevant columns.
foreach ($prop in $_.psobject.Properties) {
if ($prop.Value -as [double]) {
$prop.Value = if ($prop.Value.StartsWith('-')) { $prop.Value.Substring(1) } else { '-' + $prop.Value }
}
}
}
$_ # Output the (potentially modified) row object.
} | ConvertTo-Csv # replace with Export-Csv ... as needed in your real code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论