时间戳

首页 > 解决方案 > Export-CSV in Powershell just outputs numbers/length

powershell - Export-CSV in Powershell just outputs numbers/length

问题描述

I've looked into this issue as others have experienced it as well but I can't really make sense of the issue at hand in order to actually implement any fixes which have been maybe offered up. Here is what I am dealing with specifically.

I am fetching information from an API, parsing through and building my own hashtables which then get pushed into an array. I want to then export that array of hashes as a simple CSV. I have done virtually the same exact setup in another script and it worked without issue, I am unable to figure out what I am doing wrong here...

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

$user    = '*****'
$pass    = ConvertTo-SecureString '******' -AsPlainText -Force
$cred    = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass
$base    = '*********'
$url     = '*********'
$content = @()
$res     = $null

function fetch_data([String] $path) {
    $req        = Invoke-WebRequest -Credential $cred -Uri $path -Method GET
    $global:res = ConvertFrom-Json $req
}

function process_data([String] $arg) {
    fetch_data($arg)

    foreach($i in $res.data) {
       $subscription = @{
            'id'                  = $i.id
            'name'                = $i.name
            'sub_name'            = $i.subscriptionFormReference.name
            'owner_id'            = $i.owner.targetName
            'owner_firstName'     = $i.owner.firstName
            'owner_lastName'      = $i.owner.lastName
            'owner_recipientType' = $i.owner.recipientType    
       }

       foreach($x in $i.data.criteria.data) {
            $sub.lob_name   = $x.name
            $sub.lob_owner  = $x.operator
            $sub.lob_values = $x.value
       }
       $subscription = ConvertTo-Json $subscription
       $global:content.add($subscription)
    }

    if ($res.links.next) {
        $link = $base + $res.links.next
        process_data($link)
    }
}

process_data($url)


Write-Host $content


$content | Export-CSV "\\*******\TEST_CSV.csv" -NoTypeInformation

The outputted file is just a single column with numbers going down as long as the number of objects (hashes I built) that exist in the array above. I'm assuming this is the length of the object in string form maybe and that is what it is recognizing so it may be a formatting issue?

Now, if I print the array to the screen inside the script, I see all the hashtables without issue. The only diff being that they are {} rather than @{} like in the other script I mentioned earlier which works without issue.

Any ideas?

标签： powershellcsvexport-csv

解决方案

  • 首先也是最重要的：在将它们添加到 之前不要调用ConvertTo-Json您的$subscription实例$content，因为这会将它们转换为string，并传递一个 string 以Export-Csv简单地导出[string]类型的唯一属性，.Length- 这就是您所看到的。

  • 但是，正如Lee_Daily在评论中指出的那样，即使按原样使用$subscription哈希表实例也不能解决问题，因为当您将哈希表传递给Export-Csv;时，它并没有有意义地序列化它。[pscustomobject]必须使用一个实例来代替，（在 PSv3+ 中）您可以通过将哈希表文字转换为;[pscustomobject]来方便地创建它。也就是说，使用$subscription = [pscustomobject] @{ ... }而不是$subscription = @{ ... }

  • 如果您的代码在script中运行，$global:content则不会引用script的（顶级）$content变量，而是如范围说明符所建议的那样，引用该名称的全局变量。

  • 但是，即使您以正确的变量为目标 - 使用$script:content-调用.Add()来增长数组也不会起作用，因为数组是固定大小的集合。

  • 但是，即使您通过使用+=而不是解决此问题.Add()，以这种方式“增长”数组也是低效的，因为 PowerShell每次都需要重新创建数组。

    • 相反，只需让您的process_data函数直接输出$subscription对象（哈希表） ，您可以将其收集到 PowerShell为您隐式创建的数组中，甚至可以直接通过管道传输到Export-Csv.

把它们放在一起：

# ...

function process_data([String] $arg) {
  fetch_data $arg # Note: Don't put (...) around function arguments.

  foreach($i in $res.data) {
    # Use a [pscustomobject] cast to create a custom object from the hashtable.
    $subscription = [pscustomobject] @{
          'id'                  = $i.id
          'name'                = $i.name
          'sub_name'            = $i.subscriptionFormReference.name
          'owner_id'            = $i.owner.targetName
          'owner_firstName'     = $i.owner.firstName
          'owner_lastName'      = $i.owner.lastName
          'owner_recipientType' = $i.owner.recipientType    
     }

     foreach($x in $i.data.criteria.data) {
          $subscription.lob_name   = $x.name
          $subscription.lob_owner  = $x.operator
          $subscription.lob_values = $x.value
     }
     # Directly output the $subscription custom object in each iteration.
     # This effectively outputs a collection (array) of objects.
     $subscription
  }

  if ($res.links.next) {
      $link = $base + $res.links.next
      process_data($link)
  }

}

# Call the function and collect the objects in an array.
$content = process_data $url # Note: Don't put (...) around function arguments

Write-Host $content

$content | Export-CSV "\\*******\TEST_CSV.csv" -NoTypeInformation

推荐阅读