I have piece of code like below.

$month = Get-Date -Format "yyyy_MM"
$csv_location = "C:\brivo\csv\"   $month   ".csv"
if (!(Test-Path $csv_location))
{
    $newcsv = {} | Select "Time","Name","Surname","Email","Telephone","Company","Department","Address","Postcode","City","State","Country" | Export-Csv $csv_location -NoTypeInformation
}

ForEach($line in $lines){
    Try
    {
        $line = $line.Trim()
        $file = "C:\brivo\json\"   $line
        $data = Get-Content $file | ConvertFrom-Json
    
        $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm"

        $userline = $timestamp,$data.name,$data.surname,$data.email,$data.telephone,$data.company_name,$data.department,$data.address,$data.postcode,$data.city,$data.state,$data.country
        $userline | out-file $csv_location -Append
    }
    Catch [Exception]
    {
        Write-Host $_.Exception | format-list -force
    }
}

where first part is creating csv file if not exist with headers. and in second part $lines is file names like 123.json, 456.json... and all those json files has content like below.

{
    "name": "kamal",
    "sur_name": "wewetwe",
    "email": "[email protected]",
    "telephone": "311234544567",
    "company_name": "",
    "department": "",
    "address": "qwe",
    "postcode": "1234 ad",
    "city": "qwe",
    "state": "GR",
    "country": "NL"
}

what I want is to append all this json data to csv file. I have tried like above but it adds data in 1st column itself.

CodePudding user response：

• 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.}

CodePudding user response：

**this is some example **

 #add your json files on temp dir
    $a= Get-ChildItem C:\Temp\PatchingServer*
    foreach($g in $a){
        $j=gc $g
        $f=$j| ConvertFrom-Json    
        $obj=New-Object PSobject
        $obj | Add-Member Noteproperty "Port" $f.Port       
        $obj | Add-Member Noteproperty "ApplicationName" $f.ApplicationName
        $obj | Add-Member Noteproperty "MaintenanceWindow" $f.MaintenanceWindow
        $obj | Add-Member Noteproperty "BusinessUnit" $f.BusinessUnit
        $obj | Add-Member Noteproperty "AppOwner"  $f.AppOwner
        $obj | Add-Member Noteproperty "AppID"    $f.AppID
        $obj | Add-Member Noteproperty "Location" $f.Location
        $obj | export-csv C:\Temp\Patching.csv  -NoTypeInformation -Append
    }