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
}
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