Take the 2-minute tour ×

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){

Cheers, Anil

share|improve this question
add comment

3 Answers

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
share|improve this answer
add comment

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.

share|improve this answer
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. –  Anil Mar 3 '13 at 21:02
Also we would Need to remove the last column only from the current file. –  Anil Mar 3 '13 at 21:08
Your second comment has been addressed by an edit. Just remove the Select-Object. –  alroc Mar 3 '13 at 21:14
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. –  alroc Mar 3 '13 at 21:20
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 –  Anil Mar 3 '13 at 21:44
show 4 more comments

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.

share|improve this answer
Hi Mr.Jenkins, Thank you for the response. But there are around 300 columns and it is difficult to list it for the user –  Anil Mar 5 '13 at 11:01
add comment

Your Answer


