Saving CSV file in UTF8, Compress and Archive them

We had a requirement, where the CSV files generated by SSIS were not in UTF8. We had to resave in UTF8 format. We also had to compress the group of files and archive the original set of files. We achieved that using Powershell.

 $CSVfilesFolder = "C:\CSVFiles\"  
 $CompressFolder = "C:\CSVFiles\Compress"  
 $ArchiveFolder = "C:\CSVFiles\Archive"  
 $filesForZip = Get-ChildItem -Path $CSVfilesFolder -Filter *.csv | Sort-Object LastWriteTime | Select-Object -First $CountofFilesToZip | select fullname | ForEach-Object { $_.FullName }  
 $newFileName = "$($CompressFolder)\GeneratedZipFile.zip"  
 #Encoding to UTF8   
 foreach($file in $filesForZip)  
 {  
   $UTF8Only = New-Object System.Text.UTF8Encoding($false)  
   $FileContent = Get-Content -Path $file  
   [System.IO.File]::WriteAllLines($file, $FileContent, $UTF8Only)     
 }  
 #Generate Zip file  
 $compress = @{  
  Path = $filesForZip  
  CompressionLevel = "Fastest"  
  DestinationPath = $newFileName  
 }  
 Compress-Archive @compress -Force  
 #Archive the files  
 foreach($file in $filesForZip)  
 {  
   Move-Item $file -Destination $ArchiveFolder -Force  
 }  

Maintain timestamp of CSV File

We were having a scenario, where we had to fix few CSV field issues. But, we also wanted to retain timestamp. We used below script to fix the CSV and maintain the timestamp.

 $BulkLoadFolder = "C:\Projects\Sntrax\Bulkload\FilesToFix"  
 $Folder = "C:\FilesToFix\"  
 $filesList = Get-ChildItem -Path $Folder -Filter *.csv | select fullname | ForEach-Object { $_.FullName }  
 foreach($file in $filesList)  
 {   
     $newfile = $file.Replace("FilestoFix","FilestoFix\fixed")  
     #Get lastwritetime for the filename   
     $LastWriteDateTime = Get-ItemPropertyValue -Path $file -Name LastWriteTime  
     #Import file and Export   
     Import-Csv $file | ForEach-Object {  
  if ($_.Field1.Trim() -eq '') {  
         $_.Field1 = '--'  
       }  
       if ($_.Field2.Trim() -eq '') {  
         $_.Field2 = '--'  
       }  
       $_  
  } | Export-Csv $newfile -NoTypeInformation -Force  
     Set-ItemProperty -Path $newfile -Name LastWriteTime -Value $LastWriteDateTime  
 }  

Powershell alert, once server is up and running

 We have got regular server patches every month. When the server is restarted, we have to keep checking whether server is up again. The below powershell script will be helpful, to let us know, once server is up and running.

1:  # Load assembly  
2:  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")  
3:  $ComputerName = "FQDN of Server"  
4:  $ServerOnline = $false  
5:  while($ServerOnline -eq $false)  
6:  {  
7:    if(Test-Connection -BufferSize 32 -Count 1 -ComputerName $ComputerName -Quiet)  
8:    {  
9:     [System.Windows.Forms.Messagebox]::Show("The server $computerName is up now");  
10:     $serverOnline = $true;  
11:     break;  
12:    }  
13:    else  
14:    {  
15:      [System.Windows.Forms.Messagebox]::Show("The server $computerName is offline now");  
16:      Start-Sleep -Seconds 5  
17:    }  
18:  }  

Copy web.config between two web servers

We are having load balancing of web portal, in two different web servers. The web.config has to be same between web portal. If one of them is changed, we want to copy the same to another server. Below script can be used to do the same:

I have put local path just for reference. UNC path can be used in place of local path. 

1:  $strSourceFile = "C:\\dev\\wv02\\test.txt"  
2:  $strDestFile = "C:\\dev\\wv01\\test.txt"  
3:  if ((Get-ChildItem $strSourceFile).LastWriteTimeUtc -gt (Get-ChildItem $strDestFile).LastWriteTimeUtc)  
4:  {  
5:    write-host "$($strSourceFile) is latest than $($strDestFile)"  
6:    copy-item -Path $strSourceFile -Destination $strDestFile  
7:  }  
8:  else  
9:  {  
10:     write-host "$($strDestFile) is latest than $($strSourceFile)"  
11:     copy-item -Path $strDestFile -Destination $strSourceFile  
12:  }  

Sending Email in Powershell

Below script can be used to send email from Powershell, for audit report for yesterday. 

1:  $yesterday = (get-date).AddDays(-1).Date.ToString("yyyy_MM_dd")  
2:  $yesterdayMailSubject = (get-date).AddDays(-1).Date.ToString("yyyy-MM-dd")  
3:  $From = "test@email.com"  
4:  $To ="test@email.com"  
5:  $Cc = "test@email.com"  
6:  $Attachment = "C:\temp\Audit_$yesterday.csv"  
7:  $Subject = " DB Server Audit for Day: $yesterdayMailSubject "  
8:  $Body = "<h2>Please see the audit report attached file</h2><br><br>"  
9:  $SMTPServer = "SMTPServerName"  
10:  $SMTPPort = "587" #SMTPPort  
11:  Send-MailMessage -From $From -to $To -Cc $Cc -Subject "test" -Usessl -Body "test" -SmtpServer $SMTPServer -Port $SMTPPort -Credential $From -Verbose -Debug #-Attachments $Attachment  

Counting of files containing keyword in Powershell

When we want to see how many files are using a specific keyword, we can use below powershell script to do this. This will be helpful if we want to estimate the amount of code changes needed for a keyword change.

1:  set-location c:\dev\codefiles  
2:  $count = 0  
3:  $files = Get-ChildItem -Recurse -File `  
4:  #if we want to filter the files based on date range  
5:  `#|   Where-Object { $_.CreationTime -ge "12/13/2017" -and $_.CreationTime -le "12/29/2017" } `  
6:   ForEach($file in $files)  
7:   {  
8:     if ( (Get-Content $file -Raw).Contains("shipsite=`"KeywordToFind`""))  
9:     {  
10:      write-host $file; $count++;  
11:     }   
12:   }  
13:   Write-Host "count of files containing word:$count  

Recursively look for files containing a keyword using Powershell

To recursively look for files containing a keyword in the code base, below Powershell script can be used.

set-location "CodeBaseFolder" 

Get-ChildItem -Recurse -File | Select-String  -Pattern "PatternToLook" | Select path, LineNumber, Line | Export-Csv C:\OutputFolder\KeywordPresence_InCodeBase.csv -NoTypeInformation

Renaming Files Incrementally in a folder, using Powershell

 The below script can be used to rename files incrementally in a folder.

$path = "C:\Users\rvenk\Desktop\SBI Empanelment Docs"
$nr = 1
Get-ChildItem $path | ForEach-Object {$fullName = $_.FullName; $fileName = $_.Name; Rename-Item $fullName -NewName ("{0}_$filename" -f $nr++)}

Load data from one database to another database in ADLA

Currently in U-SQL, there is no support for backup and restore of databases. So, if you are having a production database and want to restore the data from production to development, you dont have easy mechanism to do it.

Below powershell script will be helpful to copy all data from production database to development database, provided both are having same schema.

Make sure that you are doing deployment in both development and production database and they are in same schema for tables.

The below powershell script generates the TRUNCATE TABLE & INSERT INTO script for all the production database tables. This genrerated script can be run as a job in ADLA development account. Replace the highlighted values with your details accordingly.  

Connect-AzureRmAccount -SubscriptionName "YOUR_SUBSCRIPTIONNAME"

$usqlScriptForTablesBackup = ""

$tables = @()

$databaseName = "PRODUCTIONDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "PRODUCTIONDB",$table.schema, $table.table

}

$databaseName = "DEVELOPMENTDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "sources",$table.schema, $table.table

}

$usqlScriptForTablesBackup | Out-File -FilePath "D:\backupscript.usql"