Convert Excel Sheet to Json with PowerShell
This script converts a sheet from an Excel Workbook to a JSON file.
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:
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.
- 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!)
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:
You can use the -SheetName parameter to specify a sheet name to export:
Or, you can specify the output file as well:
The script also accepts an input file from pipeline:
Where to get it
You can find the script on: