none
Convertto-json

    Question

  • i have an excel file and i am reading it and saving in a variable, when i try to convert that to json i am getting the below error, this works when i use -compress which i dont want to do, any better approach for this?

    ConvertTo-Json : The converted JSON string is in bad format.
    At line:1 char:11
    + $testxl | ConvertTo-Json
    +           ~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (@{name=WebError.ShowStackTrace; data=1}:PSObject) [ConvertTo-Json], InvalidOperationException
        + FullyQualifiedErrorId : JsonStringInBadFormat,Microsoft.PowerShell.Commands.ConvertToJsonCommand

    Friday, April 21, 2017 10:59 AM

All replies

  • You cannot convert text to Json.

    help ConvertTo-Json -full

    You can only convert objects to Json:

    Get-Process powershell | ConvertTo-Json


    \_(ツ)_/

    Friday, April 21, 2017 11:08 AM
    Moderator
  • so how can i do that? i tried converting a csv file to json, it shows the same thing, 

    import-csv c:\test.csv | convertto-json 

    how can i do this

    Friday, April 21, 2017 11:34 AM
  • D:\scripts> import-csv customers.csv|ConvertTo-Json
    [
        {
            "CustomerName":  "Blathering Chefs",
            "CompanyName":  "Blat Services LLC",
            "CustomerID":  "1002122"
        },
        {
            "CustomerName":  "Big Sky",
            "CompanyName":  "The Sky Highway Inc.",
            "CustomerID":  "12345222"
        },
        {
            "CustomerName":  "Bog Adventures",
            "CompanyName":  "The AdventureWorks Cia.",
            "CustomerID":  "20783123"
        }
    ]
    D:\scripts>


    \_(ツ)_/

    Friday, April 21, 2017 4:14 PM
    Moderator
  • i created the csv file by using save as in excel, that could be a problem?
    Monday, April 24, 2017 11:09 AM
  • If it is a real CSV file then it will convert correctly.  Excel generates good CSV files.


    \_(ツ)_/

    Monday, April 24, 2017 3:24 PM
    Moderator