19 April 2015

Compare and Update Excel spread sheet using powershell

Windows Power-shell is the most useful tool to automate lot of our work. It comes installed with Windows OS. Last week I've got some task where I have to compare some data from a csv file to an excel spread sheet and update the relevant data. Excel sheet contained thousands of records which has to be compared with a csv file contained hundreds of records. I have got more than 20 sets of files to compare and update.
As a programmer, I hate this kind of mundane tasks. Those are ultimately boring. So, I came up with a power-shell script to do the job for me. This script can be modified to compare and update between two or more excel spread sheets as well. Feel free to modify and use it.

# excel file path
$excel_file_path = "C:\R\All Sites for migration 4-16.xlsx";
#csv file to compare
$csv = IMPORT-CSV C:\R\16_4_2015_B.csv

# date completed to update excel column
$dateNow = "17/4"
# migration status to update excel column
$status = "TEST STATUS"

# Instantiate the COM object
$Excel = New-Object -ComObject Excel.Application
# Opens the excel workbook
$ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path);
# prepare the work sheet. Here 1 means the first worksheet
# you can use the worksheet name as well. eg: Sheet 1
$ExcelWorkSheet = $Excel.WorkSheets.item(1)


Write-host "Reading Excel File" -NoNewline
# row number. I set this as 2 because I'm goint to read from second row
# as first row contains the heading
$intRow = 2;
# starting the loop
Do
{
#reading from cell. First parametrer $intRow is the row being read
# second parameter 3 gets the third column from the spread sheet
$readValue = $ExcelWorkSheet.Cells.Item($intRow,3).value2
# traversing through csv file contents to compare with the selected row
foreach($item in $csv)
{
 # if third column value matches with csv file Url
 if($item.Url -eq $readValue)
 {
  # writing the match on screen
  Write-Host "Match:" $readValue "DB:" $item.DBSize -foregroundcolor "Magenta"
     # updating data to excel file columns 5,6 and 9
    $ExcelWorkSheet.Cells.Item($intRow,5) = $dateNow
    $ExcelWorkSheet.Cells.Item($intRow,6) = $status
    $ExcelWorkSheet.Cells.Item($intRow,9) = $item.DBSize
 }
}
# increment row to the next row
$intRow ++
}While ($ExcelWorkSheet.Cells.Item($intRow,1).value2 -ne $null) # loop untill there is no value in the first column
# Saving the file
$ExcelWorkBook.Save();
# Closing Work book
$ExcelWorkBook.Close();
# Closing Excel
$Excel.Quit();
# Releasing the com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL -Force