Convert Excel to HTML using PowerShell

Conversion from Excel to HTML can be done by combination of Import-Excel from PowerShell module ImportExcel and ConvertTo-Html cmdlet. Lets use Excel file generated in previous post. Conversion is done by one-liner:

Import-Excel .\Services.xlsx | ConvertTo-Html

Output is:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>Name</th><th>Status</th><th>StartType</th></tr>
<tr><td>AarSvc_dc323</td><td>Running</td><td>Manual</td></tr>
<tr><td>AdobeARMservice</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AESMService</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AJRouter</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>ALG</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>AMD Crash Defender Service</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AMD External Events Utility</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AppHostSvc</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AppIDSvc</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>Appinfo</td><td>Running</td><td>Manual</td></tr>
</table>
</body></html>

If we want to save HTML directly into file, Out-File cmdlet is added:

Import-Excel .\Services.xlsx | ConvertTo-Html | Out-File Services.html -Encoding utf8

Convert to Base64 encoded string using PowerShell one-liner

PowerShell doesn’t provide built-in cmdlets for conversion from string to Base64 encoded string. This task can be done by combination of .NET methods Encoding.GetBytes and Convert.ToBase64String. PowerShell one-liner is then simple:

[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("MyTestInput"))

We can even omit System namespace to simplify one-liner:

[Convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes("MyTestInput"))

Output is Base64 encoded string:

TXlUZXN0SW5wdXQ=

More about using .NET in PowerShell can be found in documentation.

Deploy Windows service using Terraform

Terraform can be used for deployment of Windows service by combination of provisioner together with PowerShell cmdlets.


Udemy course: Migrate Windows service to Azure

Following Terraform code placed in main.tf file can be used to install and uninstall of Windows service:

variable "binary_path" {
  type = string
  description = "Binary path"
}

variable "service_name" {
  type = string
  description = "Service name"
}

resource "null_resource" "win_service" {
  triggers = {
    service_name = var.service_name
  }
  provisioner "local-exec" {
    when = create
    command = "New-Service -BinaryPathName ${var.binary_path} -Name ${var.service_name} -StartupType Automatic; Start-Service -Name ${var.service_name}"
    interpreter = ["PowerShell", "-Command"]
  }
  provisioner "local-exec" {
    when = destroy
    command = "Stop-Service -Name ${self.triggers.service_name}; (Get-WmiObject -Class Win32_Service -Filter \"Name='${self.triggers.service_name}'\").Delete()"
    interpreter = ["PowerShell", "-Command"]
  }
}

Install Windows service using:

terraform apply -var 'binary_path={binary_path}' -var 'service_name={service_name}' -auto-approve

Uninstall Windows service using:

terraform destroy -var 'binary_path={binary_path}' -var 'service_name={service_name}' -auto-approve

Before running this commands replace variables {binary_path} and {service_name} with required values.

Convert CSV to Excel using PowerShell

There are many posts explaining how to convert CSV to Excel using PowerShell, but most of them requires Microsoft Office to be installed on the machine. I try to find most simple solution and it is by using of combination Import-Csv and Export-Excel from PowerShell module ImportExcel.

Sample CSV file is created by exporting top 10 services:

Get-Service | Select-Object Name,Status,StartType -First 10 | Export-Csv Services.csv -NoTypeInformation

Content of created CSV file:

"Name","Status","StartType"
"AarSvc_a3189","Stopped","Manual"
"AdobeARMservice","Running","Automatic"
"AESMService","Running","Automatic"
"AJRouter","Stopped","Manual"
"ALG","Stopped","Manual"
"AMD External Events Utility","Running","Automatic"
"AppHostSvc","Running","Automatic"
"AppIDSvc","Stopped","Manual"
"Appinfo","Running","Manual"
"AppMgmt","Stopped","Manual"


Udemy course: Improve your productivity with PowerShell

Next step is to install PowerShell module ImportExcel:

Install-Module ImportExcel

After this step in everything prepared for the conversion. Conversion from CSV to Excel is very easy:

Import-Csv Services.csv | Export-Excel Services.xlsx

Converted file opened in Excel:

Converted file opened in Excel

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.

Export data from Microsoft SQL Server table to JSON using PowerShell

In previous post I wrote about exporting data from Microsoft SQL server table to CSV file. Export to JSON format is similar:

Invoke-Sqlcmd -ServerInstance . -Database AdventureWorks -Query "SELECT TOP 3 AccountNumber, Name, CreditRating FROM Purchasing.Vendor" `
    | Select-Object AccountNumber, Name, CreditRating `
    | ConvertTo-Json `
    | Out-File -FilePath Vendor.json -Encoding utf8


Udemy course: Improve your productivity with PowerShell

Content of exported JSON file is:

[
    {
        "AccountNumber":  "AUSTRALI0001",
        "Name":  "Australia Bike Retailer",
        "CreditRating":  1
    },
    {
        "AccountNumber":  "ALLENSON0001",
        "Name":  "Allenson Cycles",
        "CreditRating":  2
    },
    {
        "AccountNumber":  "ADVANCED0001",
        "Name":  "Advanced Bicycles",
        "CreditRating":  1
    }
]

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.