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:
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.