I'm trying to compare two CSV files using the command line, but there's a bit of a catch. In one file, the key field (PartNo) sometimes ends with '-E' and can be a shortened version of the corresponding entry in the other file. Here's a small example to illustrate:
File 1:
PartNo
123
3881231234
1234-1234-1234
File 2:
PartNo
123-E
3881231234-E
1234-1234-12-E
I want to find a way to make the comparison work despite these slight differences. I've looked into the 'compare-object' command but I'm not quite sure how to adapt it for this scenario. Any advice would be appreciated!
2 Answers
Have you considered how you're formatting the data? The example you shared was a bit jumbled, which can make it difficult to understand the differences. It looks like you want to check if the PartNos match regardless of the '-E' at the end. Maybe it would help to clean up the CSV data or format it in a clearer way before running the comparison. What does your actual CSV look like?
You can definitely use the Compare-Object command in PowerShell to compare the files. First, you might want to import both CSV files using Import-Csv. Then, with a little tweaking, you can strip the '-E' from file two's PartNo when making the comparison. Here’s a quick rundown on how to do that:
```powershell
$file1 = Import-Csv -Path 'path_to_file1.csv'
$file2 = Import-Csv -Path 'path_to_file2.csv' | ForEach-Object { $_.PartNo = $_.PartNo -replace '-E$', ''; $_ }
Compare-Object -ReferenceObject $file1 -DifferenceObject $file2 -Property PartNo
```
This should help you find the matches even with those variations!
Thanks for the input! I realize the formatting was off due to posting here. I can share clearer versions of the CSV. I appreciate the help!