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:

Comments/questions

There's no commenting functionality here. If you'd like to comment, please either mention me (@[email protected]) on Mastodon or email me. I don't have any logging or analytics running on this website, so if you found something useful or interesting it would mean a lot to hear from you.