24 April 2015

Download Multiple files from web using powershell

Today I've come across a task that requires 1000 of files to be downloaded from a web directory. Now I have to click the link, then when save as option shows up click again to save. That would require 2000+ mouse clicks. No way. So I took powershell, and made a script to do it for me.

First I saved the Url and file name is a csv file. Csv file looks like
Url, Title
http://example.com/example/SomeFile.jpg, SomeFile.jpg
http://example.com/example/AnotherFile.jpg, AnotherFile.jpg

Now how I get this? I just right click the page, go to source then copied all to my favorite text editor Notepad++. After that did some Find & Replace to remove the html elements. (replace


  • with "" ).


  • Anyway, here is the script

     
    #csv file to compare

    $csv = IMPORT-CSV C:\R\picts.csv
    $filepath = "C:\Users\janbalagan\Downloads\Picts\Collection\"
    foreach($item in $csv)
    {
    $path = $filepath + $item.Title
    Write-Host "Path: " $path
     Write-Host $item.Title " being downloaded: "
    Invoke-WebRequest $item.Url -OutFile $path
    }
    Write-Host "Download Completed" -BackgroundColor DarkRed


    In case if you are working with powershell 2.0 which comes with windows 7 and previous versions, you must use 
    (New-Object Net.WebClient).DownloadFile($item.Url, $path) instead of
    Invoke-WebRequest $item.Url -OutFile $path

    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