How to Export-Csv with variables?

Question

I'm trying to write 8 variables into an CSV file with PowerShell, but it just ends up as ,,,,,,, instead of var1,var2,var3,var4,var5,var6,var7,var8

My code is as follows:

$newRow = "{0},{1},{2},{3},{4},{5},{6},{7}" -f $var1,$var2,$var3,$var4,$var5,$var6,$var7,$var8
$newRow = $newRow -Replace "`t|`n|`r",""
$newRow = $newRow -Replace " ;|; ",";"
$newRow += "`n"
$newRow | Export-Csv -Path $file -Append -noType -Force

Without -Force I get the following error message:

Export-Csv : Cannot append CSV content to the following file: C:\result.txt. The
appended object does not have a property that corresponds to the following column:
var1. To continue with mismatched properties, add the -Force parameter, and then
retry the command.
At C:\Test.ps1:72 char:12
+     $newRow | Export-Csv -Path $file -Append -noType
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo          : InvalidData: (var1:String) [Export-Csv], InvalidOperationException
     + FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.PowerShell.Commands.ExportCsvCommand

---

EDIT:

Script:

$startInfo = New-Object System.Diagnostics.ProcessStartInfo
$startInfo.FileName = "powershell.exe"
$startInfo.Arguments = 'C:\zabbix\script\zabbix_vbr_job.ps1 "Discovery"'

$startInfo.RedirectStandardOutput = $true
$startInfo.UseShellExecute = $false
$startInfo.CreateNoWindow = $false
#$startInfo.Username = "DOMAIN\Username"
#$startInfo.Password = $password

$process = New-Object System.Diagnostics.Process
$process.StartInfo = $startInfo
$process.Start() | Out-Null
$discoveryJson = $process.StandardOutput.ReadToEnd()
$process.WaitForExit()
cls

$discovery = $discoveryJson | ConvertFrom-Json
$file = "C:\zabbix\script\result.txt"

function RunScript ($param, $id)
{
    $startInfo = New-Object System.Diagnostics.ProcessStartInfo
    $startInfo.FileName = "powershell.exe"
    $startInfo.Arguments = "C:\zabbix\script\zabbix_vbr_job.ps1 '$param' '$id'"

    $startInfo.RedirectStandardOutput = $true
    $startInfo.UseShellExecute = $false
    $startInfo.CreateNoWindow = $false

    $process = New-Object System.Diagnostics.Process
    $process.StartInfo = $startInfo
    $process.Start() | Out-Null
    $output = $process.StandardOutput.ReadToEnd()
    $process.WaitForExit()

    return $output
}

$fileContent = Import-csv $file

$NewCSVObject = @()
foreach($obj in $discovery.data)
{
    $index = [array]::indexof($discovery.data, $obj)
    Write-Host $index "/" $discovery.data.count
    #Write-Host (RunScript "Result" $obj.JOBID )
    $Result = RunScript "Result" $obj.JOBID
    #Write-Host $Result
    $RunStatus = RunScript "RunStatus" $obj.JOBID
    #Write-Host $RunStatus
    $IncludedSize = RunScript "IncludedSize" $obj.JOBID
    #Write-Host $IncludedSize
    $ExcludedSize = RunScript "ExcludedSize" $obj.JOBID
    #Write-Host $ExcludedSize
    $VmCount = RunScript "VmCount" $obj.JOBID
    #Write-Host $VmCount
    $Type = RunScript "Type" $obj.JOBID
    #Write-Host $Type
    $RunningJob = "RunningJob"#RunScript "RunningJob" $obj.JOBID
    #Write-Host $RunningJob

    #$newRow = New-Object PsObject -Property @{ JobID = $obj.JOBID ; Result = $Result ; RunStatus = $RunStatus ; IncludedSize = $IncludedSize ; ExcludedSize = $ExcludedSize ; VmCount = $VmCount ; Type = $Type ; RunningJob = $RunningJob }
    $newRow = "{0},{1},{2},{3},{4},{5},{6},{7}" -f $obj.JOBID,$Result,$RunStatus,$IncludedSize,$ExcludedSize,$VmCount,$Type,$RunningJob
    $newRow = $newRow -Replace "`t|`n|`r",""
    $newRow = $newRow -Replace " ;|; ",";"
    $newRow += "`n"
    #$newRow | Out-File $file
    #[io.file]::WriteAllText("C:\zabbix\script\test.txt",$newRow)
    Write-Host $newRow
    $newRow | Export-Csv -Path $file -Append -noType
    break
}
#cls
Write-Host $fileContent

CSV headers:

JobID,Result,RunStatus,IncludedSize,ExcludedSize,VmCount,Type,RunningJob

Answer 1

There is no point in using Export-Csv if you're building the CSV line by hand anyway.

Either change

$newRow | Export-Csv -Path $file -Append -noType -Force

into

$newRow | Add-Content $file

or build $newRow like this:

$newRow = New-Object -Type PSObject -Property @{
  'JobID'        = $var1
  'Result'       = $var2
  'RunStatus'    = $var3
  'IncludedSize' = $var4
  'ExcludedSize' = $var5
  'VmCount'      = $var6
  'Type'         = $var7
  'RunningJob'   = $var8
}

and the problem will disappear.

---

The reason for this behavior is that Export-Csv is for transforming objects into a tabular string representation of their properties. Essentially, an object

@{
  propertyA: 'foo'
  propertyB: 23
}

becomes

propertyA,propertyB
"foo","23"

If you're already building a string, the resulting (string) object has just a single property (Length), which doesn't match any of the properties from your existing CSV. Hence the error you're getting without -Force. Even if you use -Force, the properties written to the CSV are determined from the first item in the existing CSV. Properties that are not present in this set are omitted from the output, and properties from that set that are not present in the object are filled with null values.