Thursday, December 13, 2012

Powershell script to Refresh Excel Data

The following sript opends Excel files one by one, refreshes the data, saves the file back and closes the file.

 Function RefreshExcelData($Excel){
  $App = new-object -comobject excel.application
  $App.Workbooks.Open($Excel)
  $App.Visible = $true
  $App.ActiveWorkbook.Refreshall()
  $App.Save()
  $App.Quit()
  $username = $env:username
  Remove-Item "C:\Users\$username\Documents\RESUME.XLW"
 }
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 $Excel = 'R:\folder\file1.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file2.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file3.xlsx'
 RefreshExcelData $Excel
 $Host.UI.RawUI.ForegroundColor = "white"

If you find this script useful please donate generously.

Thursday, December 6, 2012

Powershell script to ftp MySQL backup file and restore it locally.

The following Powershell script can be customised and scheduled (
 powershell.exe -executionpolicy bypass -file E:\scripts\dbrestore.ps1
) on your server:

 #########################################################################
 # Purpose:  FTP down MySQL backup file and restore it locally.
 # Requirements: 1) Source backup file automatically created daily.
 #      The name is generated as YYYYMMDD_dbname.sql
 #      and located in /dbbackup folder.
 #     2) Indy.Sockets.dll exits on the destination system.
 #      Lets presume that it is located in e:\scripts
 #########################################################################

 function Open-FTPConnection($ftphost, $username, $password) {
  [void][Reflection.Assembly]::LoadFrom("e:\scripts\Indy.Sockets.dll");
   $ftp = new-object Indy.Sockets.FTP;
   $ftp.Disconnect();
   $ftp.Host = $ftphost;
   $ftp.Username = $username;
   $ftp.Password = $password;
   $ftp.Connect();
   $ftp.Passive=$true;
   return $ftp;
  }
 
 function Close-FTPConnection($ftp) {
  $ftp.Disconnect();
  }
 
 function Draw-DirectoryContents($ftp) {
  "Listing directory contents ..";
  $ls = new-object System.Collections.Specialized.StringCollection;
  $ftp.List($ls, "", $true);
  foreach($file in $ls){
   $file;
   }
   Get-FTPCurrentLocation $ftp
  "Complete";
  }
 
 function Get-FTPCurrentLocation($ftp) {
  return $ftp.RetrieveCurrentDir();
  }
 
 function Set-ParentLocation($ftp) {
  "Changing to Parent Directory ..";
  $ftp.ChangeDirUp();
  Draw-DirectoryContents;
  }
 
 function Download-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Downloading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Get($sourceFileName, ($targetDir + $sourceFileName), $true, $false);
  "Downloading of {0} into {1} is complete" -f $sourceFileName,
  [Environment]::CurrentDirectory;
  }
 
 function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Uploading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);
  "Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir;
  }
 
 function Process-FTPItem($ftp, [string]$sel) {
   [string[]]$fields=[Regex]::Split($sel, " +");
   $startField=8; #the file/directory name starts after 8 fields
   [string]$name="";
 
  for ($field=$startField; $field -lt $fields.Length; $field++) {
    if ($field -eq $startField) {
    $temp = "";
    } else {
    $temp = " ";
    }
    $name += $temp + $fields[$field];
    #add aditional space for name split into multiple fields
   }
 
  if ($sel[0] -eq 'd') { #directory
    "Changing directoy to {0} .." -f $name;
    $ftp.ChangeDir($name);
    Draw-DirectoryContents $ftp #redraw contents after changing directory
  } else {
    if ($sel[0] -eq '-') { #plain file has '-' as first character
    return $name; #return the name of file to download
    }
  }
  }
 
 function datetime{
  $year = (Get-Date).Year;
  $month = Get-Date -format MM;
  $day = Get-Date -format dd;
  $hour = Get-Date -format hh;
  $minute = Get-Date -format mm;
  $second = Get-Date -format ss;
  return "$day/$month/$year $hour`:$minute`:$second";
 }
 $year = (Get-Date).Year;
 $month = Get-Date -format MM;
 $day = Get-Date -format dd;
 $hour = Get-Date -format hh;
 $minute = Get-Date -format mm;
 $second = Get-Date -format ss;
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 [void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
 $date = datetime;
 "** Start process $date **";
 write-host "`r";
 $theDate = "$year$month$day";
 $word_to_delete = "_dbname.sql";
 $file = "/dbbackup/$theDate$word_to_delete";
 $dir = "e:\db_backup"; #Destination folder
 $from = "$dir\dbbackup";
 $to = "$dir\$theDate`_$hour$minute$second";
 $out = "$to\$theDate$word_to_delete";
 $procedures = "/dbbackup/curls_procedures_$year-$month-$day.sql"
 if (!(Test-Path -path "$dir\$theDate*")){
  if ((Test-Path -path "$dir\dbbackup")){
   Remove-Item "$dir\dbbackup";
  }
  New-Item "$dir\dbbackup" -type directory;
  $f = Open-FTPConnection "www.sitename.com" "ftpUserName" "ftpPassword";
  Download-FTPFile $f $file ($dir);
  Download-FTPFile $f $procedures ($dir);
  Close-FTPConnection $f;
  Rename-Item $from $to;
  #Global replace of the keyword
  (Get-Content $out) | Foreach-Object {$_ -replace "sourceDBname", "destinationDBname"} | Set-Content $out;
  Write-Host $to;
  Write-Host $from;
 } else {
  "File already downloaded!";
  "Processing local restore ...";
  $items = Get-ChildItem -Path "$dir\$theDate*"
  foreach ($item in $items){
   $val = $item.Attributes
     if ($item.Attributes -Like "Directory*"){
    $theDir = $item.Name
     }
  }
  $theDir = "$dir\$theDir"
  $fileEntries = [IO.Directory]::GetFiles($theDir);
  foreach($filename in $fileEntries){
   $file = $filename;
  }
 }

 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname --execute=""SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(TABLE_NAME),';') AS stmt FROM information_schema.TABLES WHERE  TABLE_SCHEMA = 'destinationDBname' INTO OUTFILE 'e:\\db_backup\\dropAllTables.sql'"""
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < e:\db_backup\dropAllTables.sql"
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < $out"
 Write-Host $mysql;
 write-host "`r";
 $date = datetime;
 "** End of process $date **";

If you find this script useful please donate generously.