Convert Excel Sheet to Json with PowerShell

This script converts a sheet from an Excel Workbook to a JSON file.

Why?

This is useful as JSON files allow all sorts of things that would break CSV files, like commas.

This would not be a pleasant CSV file to deal with:

Hostname,IPAddress,OperatingSystem
DC01,10.0.0.11,10.0.0.15,Windows Server 2012 R2

This would look something like the following if parsed as a CSV file:

Hostname: DC01
IPAddress: 10.0.0.11
OperatingSystem: 10.0.0.15
?: Windows Server 2012 R2

Not ideal. JSON will display it like this:

[
  {
    "Hostname": "DC01",
    "IPAddress": "10.0.0.11,10.0.0.15",
    "OperatingSystem: "Windows Server 2012 R2"
  }
]

It also allows for much more complexity than this, making JSON a great format for working with complicated sets of data. This script barely scratches the surface.

Requirements

  • Windows PowerShell (v5.1 tested, please let me know what versions you’ve got it working on!)
  • Microsoft Excel (2016 tested, as with PowerShell, please let me know what versions you’ve got it working on!)

Usage

If you provide no input beyond the name of a spreadsheet, the script will confirm only sheet exists, and output the JSON file into the current directory:

Convert-ExcelSheetToJson -InputFile MyExcelWorkbook.xlsx

You can use the -SheetName parameter to specify a sheet name to export:

Convert-ExcelSheetToJson -InputFile MyExcelWorkbook.xlsx -SheetName Sheet1

Or, you can specify the output file as well:

Convert-ExcelSheetToJson -InputFile MyExcelWorkbook.xlsx -OutputFileName MyConvertedFile.json -SheetName Sheet2

The script also accepts an input file from pipeline:

Get-Item MyExcelWorkbook.xlsx | Convert-ExcelSheetToJson -OutputFileName MyConvertedFile.json -SheetName Sheet2

Where to get it

You can find the script on:

Written on October 4, 2016