none
Missing Value in Exported CSV file using Powershell RRS feed

  • Question

  • 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

All replies

  • It is not possible to understand what you are trying to do.  Why do you want to add the header columns to each row?

    Without some idea what the xl looks like and what you are trying to add to it we cannot guess at this.


    \_(ツ)_/

    Friday, September 22, 2017 1:14 AM
    Moderator