Fetch specific data from nested JSON-file, then append it to a CSV-file

Question

I have written a PowerShell-script, which reads specific data-pieces out of a given JSON-file. Then reassembles the read data. Finally prepends the data to a existing CSV-file by adding it to the top of the file.

My script:

$srcFile = ".\output copy.json"
$targetFile = ".\demo1.csv"
# Exit the script, if one of the files is missing.
if (!(Test-Path $srcFile)) {
    Write-Warning "${srcFile} is missing."
    return
} elseif (!(Test-Path $targetFile)) {
    Write-Warning "${targetFile} is missing."
    return
}
# Read JSON-Report in. 
$json = Get-Content -Path $srcFile | ConvertFrom-Json
# Fetch the relevant data from the nested JSON-structure.
$tests = $json.stats.tests
$passed = $json.stats.passes
$failures = $json.stats.failures
# Date-format (YYYY-MM-DDThh:mm:ss) not usable. Has to be converted.
$dateSegms = $json.stats.start.Split("T")[0].Split("-")
# Becomes: DD.MM.YYYY
$dateValid = $dateSegms[2] + "." + $dateSegms[1] + "." + $dateSegms[0]
$newEntry =  "${tests};${passed};${failures};${dateValid}"
# Read the existing CSV-file in.
$existingCsv = Get-Content -Path $targetFile
# The entries have to be ordered descending.
$sUpdatedCsv = $newEntry + " " + $existingCsv
$aUpdatedCsv = $sUpdatedCsv.Split(" ")
# Remove the content of the CSV-file. 
Clear-Content $targetFile
# Write the updated data to the CSV-file.
foreach ($line in $aUpdatedCsv) {
    Add-Content -Path $targetFile "${line}"
}
# Delete the JSON-report.
Remove-Item -Path $srcFile

I'm not the most experienced PowerShell-programmer and would like to know where and how my code could be improved. Especially concerning my date-conversion.

Are there better ways in PowerShell to convert a date in the given format "YYYY-MM-DDThh:mm:ss" to the needed format "DD.MM.YYYY"?

The script produces the needed results. But are there flaws in my code?

All comments and answers concerning my approach welcomed.

As this i an input processing program, could you provide a minimal pair of expected input and output for reference? — ojdo
– ojdo, Commented Jan 10, 2023 at 14:39
@ojdo Sorry, but I'm not sure if I'm allowed to do so. Hope for your understanding. I don't want to get into trouble. — michael.zech
– michael.zech, Commented Jan 10, 2023 at 14:48
I am not asking you to provide a real dataset, but just the structure with made-up values for all contents. A one-line JSON that just has the required fields (stats, start as an ISO datetime etc.) so that your script works. — ojdo
– ojdo, Commented Jan 10, 2023 at 14:57

ojdo · Accepted Answer · 2023-01-10 15:31:24Z

Date conversion

# Date-format (YYYY-MM-DDThh:mm:ss) not usable. Has to be converted.
$dateSegms = $json.stats.start.Split("T")[0].Split("-")
# Becomes: DD.MM.YYYY
$dateValid = $dateSegms[2] + "." + $dateSegms[1] + "." + $dateSegms[0]

That can be written simpler as

$startDate = [Datetime]::ParseExact($json.stats.start, "yyyy-MM-ddTHH:mm:ss")
$dateValid = $startDate.ToString("dd.MM.yyyy")

Functions

Your whole script could be separated into some suggestively named functions, yielding a top-level structure like:

$testResultFilename = ".\output copy.json"
$targetFilename = ".\demo1.csv"

$newEntry = Read-Results $testResultFilename
Append-Entry -Entry $newEntry -TargetFile $targetFilename
Remove-Item -Path $testResultFilename

Where, for example Read-Results would contain all code to reliable convert the contents of the test results to the new entry line:

function Read-Results($srcFile) {
    if (!(Test-Path $srcFile)) {
         throw "${srcFile} is missing."
    } 

    $json = Get-Content -Path $srcFile | ConvertFrom-Json

    $tests = $json.stats.tests
    $passed = $json.stats.passes
    $failures = $json.stats.failures
    
    $startDate = [Datetime]::ParseExact($json.stats.start, "yyyy-MM-ddTHH:mm:ss")
    $dateValid = $startDate.ToString("dd.MM.yyyy")
    
    return "${tests};${passed};${failures};${dateValid}"
}

Stack Exchange Network

Fetch specific data from nested JSON-file, then append it to a CSV-file

1 Answer 1

Date conversion

Functions

You must log in to answer this question.

Hot Network Questions

Fetch specific data from nested JSON-file, then append it to a CSV-file

1 Answer 1

Date conversion

Functions

You must log in to answer this question.

Related

Hot Network Questions