Here is a technique I picked up while researching ways to generate JSON output from a CSV file.  My main use case is generating sample data quickly.  While JSON is easy to read and parse, I don’t feel like hand typing dozens of string/value pairs, I want to use excel like a sane person.  I also do not want to upload my JSON to some random internet site.  Thankfully Powershell (v3) has a built-in cmdlet that allows us to convert to JSON.  

Step 1 – Generate Test Data using preferred spreadsheet

Step 2 – Save File as either CSV or Tab-Delimited text file

Step 3 – Parse the ouput file using the ConvertTo-Json powershell cmdlet, and output to a json file

import-csv "SampleInput.csv" | ConvertTo-Json | Add-Content -Path "output.json"

which produces the following output:

variation #1 – Convert from tab-delimited file

import-csv -Delimiter "`t" "SampleInput.txt"  | ConvertTo-Json | Add-Content -Path "output.json"

variation #2 – Remove whitespace / carriage returns from output file

import-csv "SampleInput.csv" | ConvertTo-Json -Compress | Add-Content -Path "output.json"

which produces the following output: