Convert CSV to JSON and JSON to CSV using PowerShell

Question

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/"
    }
]

mklement0 · Accepted Answer · 2021-09-21 22:03:22Z

12

# PowerShell script

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

edited Sep 21, 2021 at 22:03

mklement0

452k68 gold badges727 silver badges986 bronze badges

answered Mar 22, 2018 at 3:54

GOLDY MANIKOTH

1311 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BartekB · Accepted Answer · 2015-06-09 06:59:49Z

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

Loïc MICHEL · Accepted Answer · 2015-06-09 06:57:19Z

0

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

edited Jun 9, 2015 at 6:57

answered Jun 9, 2015 at 6:51

Loïc MICHEL

26.3k10 gold badges79 silver badges111 bronze badges

1 Comment

Ishan Over a year ago

The JSON you get is [ { "name": "wikipedia", "url": "en.wikipedia.org/wiki/%s" }, { "name": "wolframalpha", "url": "wolframalpha.com/input/?i=%s" }, { "name": "drive", "url": "drive.google.com" } ] which is not what I want. See the JSON example I have in my question.

Collectives™ on Stack Overflow

Convert CSV to JSON and JSON to CSV using PowerShell

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related