Convert CSV to JSON and JSON to CSV using PowerShell

2
votes

I have a CSV file that I am trying to convert to JSON using PowerShell.

The CSV file contains the following data.

web_url.csv

wikipedia,https://en.wikipedia.org/wiki/%s
wolframalpha,http://www.wolframalpha.com/input/?i=%s
drive,http://www.drive.google.com/

I would like to convert to json in the following format. Similarly how do you convert this json back to original csv in the format shown above?

web_url.json

{
    "wikipedia": "https://en.wikipedia.org/wiki/%s",
    "wolframalpha": "http://www.wolframalpha.com/input/?i=%s",
    "drive": "http://www.drive.google.com/"
}

When I run the command,

Get-Content -path web_url.csv | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json

it returns the following output which is not what I want.

[
    {
        "wikipedia":  "wolframalpha",
        "https://en.wikipedia.org/wiki/%s":  "http://www.wolframalpha.com/input/?i=%s"
    },
    {
        "wikipedia":  "drive",
        "https://en.wikipedia.org/wiki/%s":  "http://www.drive.google.com/"
    }
]
3
jsonpowershellcsv

3 Answers

2
votes

Your csv doen't look like a "proper" csv to me: columns are swapped with rows. If you have control over input file, you may fix it there already:

@'
wikipedia,wolframalpha,drive
https://en.wikipedia.org/wiki/%s,http://www.wolframalpha.com/input/?i=%s,http://www.drive.google.com/
'@ | ConvertFrom-Csv | ConvertTo-Json

If that is not possible, you just have to perform some extra steps to get what you need:

$propertyList = @'
wikipedia,https://en.wikipedia.org/wiki/%s
wolframalpha,http://www.wolframalpha.com/input/?i=%s
drive,http://www.drive.google.com/
'@ | ConvertFrom-Csv -Header Name, Value

$properties = [ordered]@{}

foreach ($property in $propertyList) {
    $properties.Add($property.Name, $property.Value)
}

New-Object PSObject -Property $properties | ConvertTo-Json

And back, again - some extra work is required:

(@'
{
    "wikipedia":  "https://en.wikipedia.org/wiki/%s",
    "wolframalpha":  "http://www.wolframalpha.com/input/?i=%s",
    "drive":  "http://www.drive.google.com/"
}
'@ | ConvertFrom-Json).PSObject.Properties |
    Select-Object Name, Value |
    ConvertTo-Csv -NoTypeInformation |
    Select-Object -Skip 1
6
votes

power shell script

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

0
votes

what about this ?

$csv=@"
wikipedia,https://en.wikipedia.org/wiki/%s
wolframalpha,http://www.wolframalpha.com/input/?i=%s
drive,http://www.drive.google.com/
"@

$obj= $csv |convertfrom-csv -delim ',' -Header "name","url"
#$obj

$json=$obj  |convertto-json 
#$json

$csv2 =$json |convertfrom-json |select -expand syncroot  |convertto-csv -NoTypeInformation -Delimiter ','
#$csv2