0

Trying to find out if there is a way to convert JSON to CSV locally on my computer without installing any 3rd party tools or using the browser/website. Maybe there is a way to get it done in command prompt or PowerShell or using notepad++ or SQL Server. I have tried Microsoft Excel's get data from JSON function but it errors out because it didn't like some characters in the JSON file. Thank you.

Lazytitan
  • 47
  • 1
  • 11
  • 1
    PowerShell has both `ConvertFrom-Json` and `ConvertTo-Csv` (along with `Export-Csv`). Depending on the shape of your data you may still need to manipulate it a bit (CSV doesn't support nested objects). SQL Server probably won't be of any help to you -- while it can parse JSON, it can't write CSV files without really ugly hacks, so you'd still need to copy the output somewhere else first. (Besides, it's definitely a "third party tool", even if Microsoft also supplies your operating system.) – Jeroen Mostert Nov 08 '22 at 15:06
  • 1
    If you have invalid characters in your JSON you may well find any attempt to use it as JSON will fail. – Patrick Hurst Nov 08 '22 at 15:12
  • See: [Convert nested JSON array into separate columns in CSV file](https://stackoverflow.com/a/46081131/1701026) – iRon Nov 08 '22 at 15:19
  • Op didn't post entire file so I'm not sure if it was JSON. So this is what I did : https://stackoverflow.com/questions/74336733/trying-to-extract-specific-text-and-merge-output-with-existing-output/74340341#74340341 – jdweng Nov 08 '22 at 15:30

1 Answers1

1

As Jeroen points out, ConvertFrom-Json, ConvertTo-Csv and Export-Csv will most likely be the basis for your solution. Without knowing the structure of your JSON, which is the key factor in determining how you'll go about it, here's an example with some native OS data on Windows using PowerShell.

Get-Process | Select-Object -Property Name,Handles,VM,Description -First 3 | ConvertTo-Json | Out-File .\procs.json
$procs = Get-Content -Path .\procs.json | ConvertFrom-Json
$procs | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Out-File .\procs.csv
& .\procs.csv

In the code above, we...

  1. Get an array of Windows process objects and create a JSON version of the data.
  2. Convert the JSON data into an array of objects.
  3. Convert the objects into a CSV.
  4. Open the CSV with the default application, Excel in my case.

You should see a nicely formatted CSV with column headers and rows of data.

Jay Adams
  • 2,052
  • 1
  • 12
  • 7