Convert csv to json using powershell

Question

I have a csv file and when I convert it using convertto-json i am getting the following error

ConvertTo-Json : The converted JSON string is in bad format. At line:1 char:70 + Get-Content -path E:\test.csv | ConvertFrom-Csv -Delimiter ',' | ConvertTo-J ... + ~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (@{name=WebError.Show; data=1}:PSObject) [ConvertTo-Json], InvalidOperationException + FullyQualifiedErrorId : JsonStringInBadFormat,Microsoft.PowerShell.Commands.ConvertToJsonCommand

when i use -compress with convertto-json i am getting the output, but its a compressed json version which is ugly to see, is there a better way to convert this csv to json or is there a way to decompress the json

CSV:

name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0
WebError.Show,1

Compressed Json Output:

[{"name":"Play","data":"http://{gho}.domain.com/"},{"name":"BDomain","data":"domain.com"},{"name":"Charts","data":"2"},{"name":"Compress","data":"0"},{"name":"CompressJ"
,"data":"0"}]

Please share your JSON with us. We need a reproducable example in order to help you. — Martin Brandl
– Martin Brandl, Commented Apr 21, 2017 at 12:58
Why are you piping a Get-Content into a ConvertFrom-Csv? Would it not be easier to use Import-Csv? — Dave Sexton
– Dave Sexton, Commented Apr 21, 2017 at 13:22

Chris Barber · Accepted Answer · 2017-04-21 16:24:39Z

0

I don't seem to have any issues using ConvertFrom-Csv and ConvertTo-Json with your example string.

Using:

"name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0" | ConvertFrom-Csv | ConvertTo-Json

Gives me:

[
    {
        "name":  "Play",
        "data":  "http://{gho}.domain.com/"
    },
    {
        "name":  "BDomain",
        "data":  "domain.com"
    },
    {
        "name":  "Charts",
        "data":  "2"
    },
    {
        "name":  "Compress",
        "data":  "0"
    },
    {
        "name":  "CompressJ",
        "data":  "0"
    }
]

Could you post the PowerShell code which is giving you the error?

answered Apr 21, 2017 at 16:24

Chris Barber

5134 silver badges10 bronze badges

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

6 Comments

windowws Over a year ago

i have this as a file and using import-csv / get-item , both gives the same error

windowws Over a year ago

get-content..sorry

windowws Over a year ago

When i have a dotted value, it throws this error, WebError.Show, any workaround?

Chris Barber Over a year ago

Sorry I don't get the same error when reading from a file, executing Get-Content "test.csv" | ConvertFrom-Csv | ConvertTo-Json works fine. Import-Csv "test.csv" | ConvertTo-Json is also fine for me.

windowws Over a year ago

OMG :-o actually this csv was generated from an excel file by using save as, could be some problem there?

|

Pang · Accepted Answer · 2017-04-24 08:56:34Z

0

Import-Csv $File | Convertto-Json

This may work for you.

edited Apr 24, 2017 at 8:56

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Apr 24, 2017 at 3:25

Prasoon Karunan V

3,0653 gold badges15 silver badges26 bronze badges

1 Comment

windowws Over a year ago

it throws the error when it has to parse WebError.Show

SridharKritha · Accepted Answer · 2021-10-28 16:32:00Z

0

Powershell command below creates OUTPUTFILE.json format file from the INPUTFILE.csv file format:

import-csv .\INPUTFILE.csv | ConvertTo-Json -depth 100 | Out-File .\OUTPUTFILE.json

answered Oct 28, 2021 at 16:32

SridharKritha

9,9092 gold badges60 silver badges45 bronze badges

Collectives™ on Stack Overflow

Convert csv to json using powershell

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related