Converting JSON to CSV using powershell

Question

I have a very large JSON Lines File with 4.000.000 Rows, and I need to convert several events from every row. The resulted CSV File contains 15.000.000 rows. How can I optimize this script?

I'm using Powershell core 7 and it takes around 50 hours to complete the conversion.

My Powershell script:

$stopwatch = [system.diagnostics.stopwatch]::StartNew()
$totalrows = 4000000
$encoding = [System.Text.Encoding]::UTF8 
$i = 0
$ig = 0
$output = @()
$Importfile = "C:\file.jsonl"
$Exportfile = "C:\file.csv"
if (test-path $Exportfile) {
 Remove-Item -path $Exportfile
}
foreach ($line in [System.IO.File]::ReadLines($Importfile, $encoding)) {
 $json = $line | ConvertFrom-Json
 foreach ($item in $json.events.items) {
 $CSVLine = [pscustomobject]@{
 Key = $json.Register.Key
 CompanyID = $json.id
 Eventtype = $item.type
 Eventdate = $item.date
 Eventdescription = $item.description
 }
 $output += $CSVLine
 }
 $i++
 $ig++
 if ($i -ge 30000) {
 $output | Export-Csv -Path $Exportfile -NoTypeInformation -Delimiter ";" -Encoding UTF8 -Append
 $i = 0
 $output = @()
 $minutes = $stopwatch.elapsed.TotalMinutes
 $percentage = $ig / $totalrows * 100
 $totalestimatedtime = $minutes * (100/$percentage)
 $timeremaining = $totalestimatedtime - $minutes
 Write-Host "Events: Total minutes passed: $minutes. Total minutes remaining: $timeremaining. Percentage: $percentage"
 }
}
$output | Export-Csv -Path $Exportfile -NoTypeInformation -Delimiter ";" -Encoding UTF8 -Append
$stopwatch.Stop()

Answer 1

There's a good article about performance to get your started: Slow Code: Top 5 ways to make your Powershell scripts run faster; at first sight, here are applicable both parts of Problem #2 & #3: Appending stuff:

• Appending to files: Export-CSV -Append repeats roughly 133 times (= 4E+6/30E+3 i.e. 4E+6 rows written in chunks of 30E+3 rows each) which could considerably worsen performance for big output file;
• Appending to arrays: (heart of the matter) when the += operator is used, it's actually destroying the array and creating a new one (source).

To eliminate appending to arrays, you can use .NET array list object as follows (merely a hint): replace

• $output = @() with $output = [System.Collections.ArrayList]::new(), and
• $output += $CSVLine with [void]$output.Add($CSVLine).

Note: do not compute one-purpose variable $CSVLine at all; instead, use

[void]$output.Add(
 [pscustomobject]@{
 Key = $json.Register.Key
 CompanyID = $json.id
 Eventtype = $item.type
 Eventdate = $item.date
 Eventdescription = $item.description
 })

Read the remark about the generic List<T> class and Performance Considerations as well:

In deciding whether to use the List<T> or ArrayList class, both of which have similar functionality, remember that the List<T> class performs better in most cases and is type safe. If a reference type is used for type T of the List<T> class, the behavior of the two classes is identical.