append rows in CSV using powershell

• Don't try to initialize your CSV file without data using Export-Csv, because that won't work:

  • The dummy object with $null property values created by Select-Object results not in just in a header row, but invariably also in a data row representing the $null values, which end up as empty strings; in other words, you'll get a data row that looks like this: ,,,,,,,,,,,

  • Instead, make sure that the properties of the objects representing data rows you pass to Export-Csv are named for the desired CSV columns.

• Don't try to construct your data rows as an array of values to save to the CSV file as plain text;^[1] instead, construct a [pscustomobject] for each data row, which Export-Csv automatically converts to a data row, with the property names serving as column names (as you attempted with your header row).

  • As the code below shows, you can construct an object in each iteration and pipe it to a single Export-Csv call for efficiency.
    • Use the -Encoding parameter to control the output character encoding as needed; notably, in Windows PowerShell the default encoding is ASCII(!).
  • -Append is still used in that single call, because it looks like you want to append to a preexisting target file, if present.
  • Important:
    • The first object sent to Export-Csv locks in the list of columns and their names based on its properties; subsequent objects are expected to have the same set of properties (or a meaningful subset).
    • Similarly, when appending to a preexisting CSV file with -Append, the objects to append must match the existing columns, although you can override this requirement with -Force.

$month = Get-Date -Format "yyyy_MM"
$csv_location = "C:\brivo\csv\" + $month + ".csv"

# $lines is assumed to be an array of your input JSON file names.
$lines | ForEach-Object {
    Try
    {
        $file = "C:\brivo\json\" + $_.Trim()
        $data = Get-Content -ErrorAction Stop -Raw $file |
                  ConvertFrom-Json -ErrorAction Stop
    
        # Construct and output an object with the desired values and the
        # properties named for the desired CSV columns.
        [pscustomobject] @{
          Time = Get-Date -Format 'yyyy-MM-dd HH:mm'
          Name = $data.name
          Surname = $data.surname
          Email = $data.email
          Telephone = $data.telephone
          Company = $data.company_name
          Department = $data.department
          Address = $data.address
          Postcode = $data.postcode
          City = $data.city
          State = $data.state
          Country = $data.country
        }

      }
    Catch
    {
        Write-Host $_.Exception | format-list -force
    }
} | Export-Csv -NoTypeInformation -Append $csv_location

^{[1] If you send an array of values to Out-File, each value becomes its own line in the output file. While you could address that with $userline -join ',', such plain-text processing is brittle, because values with embedded , chars. wouldn't be handled correctly.}