Missing Value in Exported CSV file using Powershell

• Question

• 

  

  0

  Sign in to vote

  I was wondering if anyone can help me with the code below. It creates a new excel file on the same folder where the original file is located but on the last column which is supposed to be labeled as Comment Score, it does not return the corresponding values. The original file contains columns with date as headers (these are the columns I am having issues with). Many of the cells are blank. Tried using $null to no avail.

  It works on Windows 7 but not in Windows 10.

  $file = Get-ChildItem -Filter "*.xlsx"
  $datas = Import-Excel $file.FullName
  $export = $file.BaseName + " vertical.csv" 
  
  foreach ($data in $datas)
  {
      $dates = ($data | gm -MemberType NoteProperty | where {$_.name -notlike "*[int]*"}).name
      $dates = $dates | ForEach-Object {[datetime]::Parse("$_",(Get-Culture))} | sort | ForEach-Object { $_.ToString('M/d/yyyy') }
      
      foreach ($date in $dates)
      {
          $obj = New-Object -TypeName psobject
          $obj | Add-Member -MemberType NoteProperty -Name "Prod Start Date" -Value $data.'Prod Start Date'
          $obj | Add-Member -MemberType NoteProperty -Name "Market" -Value $data.Market
          $obj | Add-Member -MemberType NoteProperty -Name "Email" -Value $data.EMail
          $obj | Add-Member -MemberType NoteProperty -Name "RaterID" -Value $data.RaterID
          $obj | Add-Member -MemberType NoteProperty -Name "Rater Name" -Value $data.'Rater Name'
          $Obj | Add-Member -MemberType NoteProperty -Name "Status" -Value $data.Status
          $obj | Add-Member -MemberType NoteProperty -Name "Report Date" -Value $date
          $obj | Add-Member -MemberType NoteProperty -Name "Comment Score" -Value $data.$date
          $obj | Export-Csv ".\$export" -Append -NoTypeInformation
      }
  }

  
  

  Hoping someone can help.

  Thursday, September 21, 2017 11:02 PM