Convert CSV file to JArray

Question

I have a need to export the json results of a Invoke-WebRequest call to a .CSV file so that non-technical users can edit the data. This is what the json looks like when I save it to a .CSV file:

{
  "Forecasts": [
    {
      "AccountNumber": "12345",
      "Period": "2022-02-01T00:00:00",
      "Field1": 3998.87968239,
      "Field2": 2133.91206875
    },
    {
      "AccountNumber": "12346",
      "Period": "2022-03-01T00:00:00",
      "Field1": 6741.483,
      "Field2": 4007.857
    }
  ]
}

I'm creating the .CSV file with this line of code:

$json.Forecasts | ForEach-Object { $_ | Export-Csv -path $fileName -NoTypeInformation -Append -Force }

And this is working good. After the user edits the data and saves and closes the .CSV file, I need to convert the .CSV data back into a JArray that looks like the original json structure (like above) so I can make another Invoke-WebRequest call (Patch), passing the JArray as the Body.

When I execute this line of code:

$editedJson = Get-Content -Raw -Path "$fileName" | ConvertFrom-CSV | Select-Object -Property AccountNumber,Period,Field1,Field2 | ConvertTo-json

The json looks like this:

[
    {
        "AccountNumber":  "12345",
        "Period":  "2022-02-01T00:00:00",
        "Field1":  "3900.87968239",
        "Field2":  "2100.91206875"
    },
    {
        "AccountNumber":  "12346",
        "Period":  "2022-03-01T00:00:00",
        "Field1":  "6700.483",
        "Field2":  "4000.857"
    }
]

As you can see, it's missing the "Forecasts" portion of the JArray and I don't know how to get the .CSV data back into this format. Can anyone help with this ?

user_15339544 · Answer

You simply need to create an object with that property (Forecasts) and attach the imported CSV to said property:

# This line would be where you import the CSV:
# $csv = Import-Csv ..... 
$csv = @'
"AccountNumber","Period","Field1","Field2"
"12345","2/1/2022 12:00:00 AM","3998.87968239","2133.91206875"
"12346","3/1/2022 12:00:00 AM","6741.483","4007.857"
'@ | ConvertFrom-Csv

@{
    Forecasts = $csv
} | ConvertTo-Json

If you want to assure that the JSON representation of Forecasts is always an array (even when the CSV only has one row):

@{
    Forecasts = [array]$csv
} | ConvertTo-Json

Convert CSV file to JArray

1 Answers