Convert JS format csv to a delimited CSV using powershell

Question

I have a sample csv file and tried to convert to a delimited format csv using powershell. For the timestamp part, it was stored as seconds by default, wondering if it can be converted into "hh:mm"

Not too sure where i should start with.

Thanks for help!

sample.csv

{
   "Body" : {
      "inverter/1" : {
         "Data" : {
            "Current_DC_String_1" : {
               "Unit" : "A",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=66050"
            },
            "Current_DC_String_2" : {
               "Unit" : "A",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=131586"
            },
            "EnergyReal_WAC_Sum_Produced" : {
               "Unit" : "Wh",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=67830024"
            },
            "Voltage_DC_String_1" : {
               "Unit" : "V",
               "Values" : {
                  "0" : 7.3000000000000007,
                  "300" : 7.3000000000000007,
                  "600" : 7.9000000000000004,
                  "900" : 7.7000000000000002
               },
               "_comment" : "channelId=66049"
            },
            "Voltage_DC_String_2" : {
               "Unit" : "V",
               "Values" : {
                  "0" : 4.2000000000000002,
                  "300" : 4.2000000000000002,
                  "600" : 4.5,
                  "900" : 4.4000000000000004
               },
               "_comment" : "channelId=131585"
            }
         },
         "DeviceType" : 233,
         "End" : "2020-03-11T23:59:59+11:00",
         "NodeType" : 97,
         "Start" : "2020-03-11T00:00:00+11:00"
      },
      "inverter/2" : {
         "Data" : {
            "Current_DC_String_1" : {
               "Unit" : "A",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=66050"
            },
            "Current_DC_String_2" : {
               "Unit" : "A",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=131586"
            },
            "EnergyReal_WAC_Sum_Produced" : {
               "Unit" : "Wh",
               "Values" : {
                  "0" : 0,
                  "300" : 0,
                  "600" : 0,
                  "900" : 0
               },
               "_comment" : "channelId=67830024"
            },
            "Voltage_DC_String_1" : {
               "Unit" : "V",
               "Values" : {
                  "0" : 6.7000000000000002,
                  "300" : 7,
                  "600" : 6.8000000000000007,
                  "900" : 7.2000000000000002
               },
               "_comment" : "channelId=66049"
            },
            "Voltage_DC_String_2" : {
               "Unit" : "V",
               "Values" : {
                  "0" : 2.2000000000000002,
                  "300" : 2.3000000000000003,
                  "600" : 2.2000000000000002,
                  "900" : 2.2000000000000002
               },
               "_comment" : "channelId=131585"
            }
         },
         "DeviceType" : 233,
         "End" : "2020-03-11T23:59:59+11:00",
         "NodeType" : 98,
         "Start" : "2020-03-11T00:00:00+11:00"
      }
   },
   "Head" : {
      "RequestArguments" : {
         "Query" : "Inverter+SensorCard+Meter",
         "Scope" : "System"
      },
      "Status" : {
         "Code" : 0,
         "Reason" : "",
         "UserMessage" : ""
      },
      "Timestamp" : "2020-03-11T01:00:03+11:00"
   }
}

Expected result with default timestamp converted

Or if possible can add the date"2020-03-11" parsed from "Start" : "2020-03-11T00:00:00+11:00" in front of converted time to make DateTimestamp for each row.

RoadRunner · Answer 1 · 2020-03-11T22:28:11.260

Assuming your data is actually a JSON file, and not a CSV file, you could try the approach below. If basicaly converts the JSON file to System.Collections.Hashtable object with ConvertFrom-Json using the -AsHashTable switch. You can read how to iterate through hashtable properties from Looping through a hash, or using an array in PowerShell.

You can then get the System.Management.Automation.PSCustomObject rows and pipe to Export-Csv, which creates the CSV file. Additionally, you can get the timespan using [System.Timespan]::FromSeconds(), which converts total seconds to a object of type System.Timespan and format hh:mm with System.TimeSpan.ToString(). For more information on timespan formatting, you can have a look at Convert seconds to hh:mm:ss,fff format in PowerShell.

As a extra cleanup step, I also went and removed the " quotes with Set-Content as well. This isn't necessary if you would like the " to persist in your file.

$json = Get-Content -Path .\sample.json | ConvertFrom-Json -AsHashtable

$json.Body.GetEnumerator() | ForEach-Object {
    $inverter = $_.Key

    $_.Value.Data.GetEnumerator() | ForEach-Object {
        $value = $_.Key

        $_.Value.Values.GetEnumerator() | ForEach-Object {
            [PSCustomObject]@{
                Inverter = "$inverter $value"
                Second = [timespan]::FromSeconds($_.Key).ToString("hh\:mm")
                Value = $_.Value
            }
        }
    }
} | Export-Csv -Path .\sample.csv
# Use NoTypeINformation to remove #TYPE from headers in < Powershell 6

Set-Content -Path .\sample.csv -Value ((Get-Content -Path .\sample.csv) -replace '"')

sample.csv

Inverter,Second,Value
inverter/1 Current_DC_String_2,00:05,0
inverter/1 Current_DC_String_2,00:10,0
inverter/1 Current_DC_String_2,00:15,0
inverter/1 Current_DC_String_2,00:00,0
inverter/1 Current_DC_String_1,00:05,0
inverter/1 Current_DC_String_1,00:10,0
...

Performance Improvement

As mklement0 explained, when using .NET types or [PSCustomObject], member enumeration is much faster than using pipelines. You can find out more from this helpful answer.

Below is simple usage of the improvement that can be made with foreach enumeration instead of Foreach-Object.

$json = Get-Content -Path .\sample.json | ConvertFrom-Json -AsHashtable

$csvRows = @()

foreach ($inverter in $json.Body.GetEnumerator()) {

    foreach ($outerValue in $inverter.Value.Data.GetEnumerator()) {

        foreach ($innerValue in $outerValue.Value.Values.GetEnumerator()){
            $csvRowData = [PSCustomObject]@{
                Inverter = "$($inverter.Key) $($outerValue.Key)"
                Second = [timespan]::FromSeconds($innerValue.Key).ToString("hh\:mm")
                Value = $innerValue.Value
            }

            $csvRows += $csvRowData;
        }
    }
}

$csvRows | Export-Csv -Path .\sample.csv

Set-Content -Path .\sample.csv -Value ((Get-Content -Path .\sample.csv) -replace '"')

mklement0 · Accepted Answer · 2020-03-11T23:18:32.370

Your input file is a JSON file, not a CSV file.

In order to flatten its object graph into the CSV row-column structure you need, nested loops are required:

# Parse the JSON file into custom objects.
$fromJson = Get-Content -Raw file.json | ConvertFrom-Json

& {
  foreach ($inverter in $fromJson.Body.psobject.Properties.Name) {
    $date =  $fromJson.Body.$inverter.Start
    if ($date -is [datetime]) { $date = $date.ToString('yyyy-MM-dd') }
    else                      { $date = ($date -csplit 'T')[0] }
    foreach ($measurement in $fromJson.Body.$inverter.Data.psobject.Properties.Name) {
      foreach ($valueProp in $fromJson.Body.$inverter.Data.$measurement.Values.psobject.Properties) {
        [pscustomobject] @{
          Inverter  = "$inverter $measurement"
          TimeStamp = $date + ' ' + 
                      [timespan]::FromSeconds([int] $valueProp.Name).ToString('hh\:mm')
          Value     = $valueProp.Value
        }
      }
    }
  } 
} | ConvertTo-Csv  # output CSV data as an array of *strings*; 
                   # to save to a *file*, use something like:
                   # Export-Csv -NoTypeInformation out.csv

Note how .psobject.Properties is used to reflect on a given object's properties; .psobject is a normally hidden property available on any object, and it provides reflection information more conveniently and faster than the Get-Member cmdlet does.

Also note how the timestamps in your JSON are parsed by ConvertFrom-Json depends on the PowerShell edition (version):

Windows PowerShell parses them as strings, so it's sufficient to split the string by T and take what comes before it.
PowerShell [Core] parses them as [datetime] instances, expressed in local time, so they're only guaranteed to result in the same calendar day if the local time zone is the same as the one implied by the UTC offset in the JSON values (+11:00).

Optional reading: performance considerations:

Note the use of nested foreach loops over the use of the ForEach-Object cmdlet in the pipeline for better performance. See this answer for background information.

With small input files that may not matter, however, and RoadRunner's helpful hashtable-based alternative, which uses nested pipeline, may well be fast enough in practice - and it too could be made to use foreach loops instead (update: it now does, in a second command).

Parsing the JSON into hashtables ([hashtable], aka System.Collections.Hashtable) via
-AsHashtable:

has the advantage of requiring less memory (the internal storage of the [pscustomobject] instances that ConvertFrom-Json outputs by default is somewhat inefficient).
has the potential disadvantage of not preserving the input order of properties, given that [hashtable] entries are inherently unordered; in the case at hand, this is not a concern, however, given that different output objects with a fixed property order are created.

Convert JS format csv to a delimited CSV using powershell

2 Answers2