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

Can you please post the script in its entirety, so that we can see what is contained in $file and the values of the $vars are? It might also help to know what (if anything) is already in the file at $file — Bassie
Please see the link for how to create a Minimal, complete and verifiable example: stackoverflow.com/help/mcve. For example, I have no idea what the value of $discovery is, nor what is contained in zabbix_vbr_job.ps1. Maybe a good place to start would be to check that $obj, $result etc actually hold the expected values when calling Export-Csv. As I can't check this myself, I have no idea whether the blank ,,,,,,, output is to be expected or not — Bassie
The variables hold the right data, I can Out-File just fine, but when I try to Export-CSV I just get ,,,,,,, — Son_Of_Diablo
Your script is doing all the work that Export-Csv does for you, separating values by commas, putting a newline at the end, etc. But your script is not providing Export-csv with the kind of object it needs. It needs an object with properties named var1, var2, var3, etc., assuming that the existing csv file has those variable names in its header. — Walter Mitty

Ansgar Wiechers Ansgar Wiechers · Accepted Answer · 2017-03-07T12:24:02

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.

How to Export-Csv with variables?

1 Answers