Welcome › Forums › General PowerShell Q&A › PowerShell to Change CSV row based upon multiple criteria is row
This topic contains 6 replies, has 4 voices, and was last updated by
-
AuthorPosts
-
May 4, 2017 at 4:49 pm #70137
Good Afternoon, Everyone!
I've searched through the forums for answer, but everything I'm coming up with is short of what I'm trying to achieve, but great starting spots.. I just can't figure out how to utilize what's out here to finish it.
Let's say that I have a CSV file that has 3 columns of data, as show below:
54,R,5,MEETINGS
54,R,25,INDLB
54,R,7,MISCMTA
56,R,9,MAINT
56,R,5,INDLB
56,R,1,MAINT
57,R,1,MAINT
62,R,5,EN
62,R,5,INDLB
62,R,1,MEETINGSI need the PowerShell script to do the following: If Column1 equals 54,56,62 (and other specified numbers) AND Column4 equals MEETINGS, MISCMTA, MAINT (and other specified codes) THEN change Column4 to UNKNOWN.
What we would end up with is:
54,R,5,UNKNOWN
54,R,25,INDLB
54,R,7,UKNOWN
56,R,9,UNKNOWN
56,R,5,INDLB
56,R,1,UNKOWN
57,R,1,MAINT
62,R,5,EN
62,R,5,INDLB
62,R,1,UNKNOWNAll help is GREATLY appreciated!
-
May 4, 2017 at 5:05 pm #70143
Are there column headings in this CSV?
-
May 4, 2017 at 5:27 pm #70146
$csv = Import-CSV -Path ("{0}\test.csv" -f [environment]::GetFolderPath("Desktop")) -Header Column1, Column2, Column3, Column4 $numbers = 54,56,62 $codes = "MEETINGS", "MISCMTA", "MAINT" foreach ($row in $csv) { if (($numbers -contains $row.Column1) -and ($codes -contains $row.Column4)) { "Updating column4 to UNKNOWN" $row.Column4 = "UNKNOWN" } } $csv
Output:
Column1 Column2 Column3 Column4 ------- ------- ------- ------- 54 R 5 UNKNOWN 54 R 25 INDLB 54 R 7 UNKNOWN 56 R 9 UNKNOWN 56 R 5 INDLB 56 R 1 UNKNOWN 57 R 1 MAINT 62 R 5 EN 62 R 5 INDLB 62 R 1 UNKNOWN
-
May 4, 2017 at 5:58 pm #70149
Rob,
Thank you!! For some reason though, it's not writing the changes back to the CSV file. I've tried to Set-Conent and Export-CSV, but neither work, which is surely due to my ignorance. What can I add to the end of the script to make it save the changes to the original CSV file?
Can't tell you how much I appreciate your help!
-
-
May 4, 2017 at 7:02 pm #70156
Sorry, Rob, I've moved past the issue that I responded with, but, is there a way to keep the headers (Column1, Column2, Column3, Column4) out of the export?
-
May 4, 2017 at 7:36 pm #70159
You could use the following pipe instead of Export-Csv. "Select-Object -Skip 1" will remove the column header for you.
$csv | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip | Set-Content -Path .\output.csv -Encoding utf8
-
-
May 4, 2017 at 7:52 pm #70167
The solution could vary depending on the version of Powershell you are using. I couldn't find the a version requirement for ConvertTo-CSV, but it was version 3 or 4 that it became available. Assuming you have those versions, you could do something like this:
$newCsv = $csv | ConvertTo-CSV -NoTypeInformation | Select -Skip 1 $newCSV | Set-Content -Path ("{0}\test_new.csv" -f [environment]::GetFolderPath("Desktop"))
If ConvertTo-CSV is unavailable, you may need to Export-CSV and then use Get-Content on the file:
$exportPath = ("{0}\test_new_2.csv" -f [environment]::GetFolderPath("Desktop")) $csv | Export-CSV -Path $exportPath -NoTypeInformation $content = Get-Content -Path $exportPath $content | Select -Skip 1 | Set-Content -Path $exportPath
-
AuthorPosts
The topic ‘PowerShell to Change CSV row based upon multiple criteria is row’ is closed to new replies.