0

I am new to powershell. Currently we are in need of a poweshell script to compare two large (100000 rows and n columns (n > 300, also column headers are Dates corresponding to each wednesday). The value of n keeps on incrementing each week in the file. We need to compare the files (current week and last week), and need to make sure that the only difference between the two files is the last column.

I have gone through some Forums and Blogs and I could do only Little due to my ignorance.

If there is a way to drop the last column from a csv file in powershell, we may be able to make use of the below script below to compare the previous week's file and the current week's file after droping the last column from current week's file.

It would be really helpful if someone can help me here with your hard earned knowledge

[System.Collections.ArrayList]$file1Array = Get-Content "C:\Risk Management\ref_previous.csv"|Sort-Object
[System.Collections.ArrayList]$file2Array = Get-Content "C:\Risk Management\ref_current.csv"|Sort-Object
$matchingEntries = @()

foreach ($entry in $file1Array) {
    if ($file2Array.Contains($entry)) {
        $matchingEntries += $entry
        } 
    }
foreach ($entry in $matchingEntries){
    $file1Array.Remove($entry)
    $file2Array.Remove($entry)
    }

Cheers, Anil

Improve this question

3 Answers 3

Reset to default
1

Assuming that the column name you want to exclude is LastCol (adjust to your actual column name):

$previous = Import-csv "C:\Risk Management\ref_previous.csv" | Select-Object -Property * -ExcludeProperty LastCol | Sort-Object;
$current = Import-csv "C:\Risk Management\ref_current.csv" | Sort-Object;
Compare-Object $previous $current;

This will drop the last column from each of the input files and indicate whether the remaining content differs.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Hi, Thanks for the answer. Actually the last column Name varies on a weekly Basis and we are looking for an opton to find the last column Name of the current file in a dynamic manner.
Also we would Need to remove the last column only from the current file.
Your second comment has been addressed by an edit. Just remove the Select-Object.
As for the last column name changing weekly, try to get that changed if possible (consistency is best). If that can't be done, I have another idea but can't write it up at the moment.
Thank you for the quick answer. One of our Systems is delivering us data on a weekly Basis and this Format with Wednesday date as headers is been used by many different applications in our department. Hence it would be really helpful if we have a generic way to get the last column from a csv file regardless it is blank or not
|
1

Based on the answer that alroc gave, you should be able to get the last column name using a split operation on the first line of the CSV file, and then using that on the -ExcludeProperty parameter.

However, the Compare-Object command on this doesn't work for me, but it does pull back the right data into each variable.

$CurrentFile = "C:\Temp\Current.csv"
$PreviousFile = "C:\Temp\Previous.csv"

$CurrentHeaders = gc $CurrentFile | Select -First 1
$CurrentHeadersSplit = $CurrentHeaders.Split(",")
$LastColumn = $CurrentHeadersSplit[-1] -Replace '"'

$Current = Import-Csv $CurrentFile | Select -Property * -ExcludeProperty $LastColumn | Sort-Object
$Previous = Import-Csv $PreviousFile | Sort-Object
Compare-Object $Current $Previous
Improve this answer

Comments

0

The import-csv and export-csv both give the opportunity to exclude columns.

The import-csv has the -header option and you simply name the incoming headers and exclude the last columns header. If there are 10 columns, only name 9. The last column will be excluded.

For export-csv, select the columns you'd like to write out ( |select col1,col2,col3|export-csv... ) and don't select the column you're trying to exclude.

Improve this answer

1 Comment

Hi Mr.Jenkins, Thank you for the response. But there are around 300 columns and it is difficult to list it for the user

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.