powershellrowexport-csv

Powershell export-csv .... adding row number to output just can't be that complicated

Folks,

In the past I've struggled for hours trying to do something in Powershell and then have someone here answer with the simplest one line answer. I'm hoping for the same now after spending hours on this.

I want to import a csv file and export the data with an added column for row number.

Input Data

"Employee","State"
"Sally","ME"
"Bob","FL"
"Pete","ID"
"Martha","CA"

Desired Output

"Employee","State","FileName","Emp#"
"Sally","ME","test","1"
"Bob","FL","test","2"
"Pete","ID","test","3"
"Martha","CA","test","4"

One thing I've tried out of my dozens of attempts:

$filePath = "$dataDir\test.csv"
$out = "$dataDir\test_out.csv" 

$Fname = (Split-Path -Path $filePath -Leaf).Split(".")[0];

$data2 = Import-Csv $filePath 
foreach ($x in $data2) 
            {$count=$count++
             $data2 | Add-Member -MemberType NoteProperty -Name FileName -Value $Fname -Force
             $data2 | Add-Member -MemberType NoteProperty -Name Emp# -Value $count -Force
            }
$data2 | export-csv $out -NoTypeInformation

All this does is put a 0 in the Emp# column. Help please?

Solution

  • Recreating the objects with Select-Object might just be easier than attaching new properties:

    $filePath = Join-Path $dataDir -ChildPath 'test.csv'
$fileName = [System.IO.Path]::GetFileNameWithoutExtension($filePath)
$out = Join-Path $dataDir -ChildPath 'test_out.csv'
$i = @{ Counter = 0 }

Import-Csv $filePath |
    Select-Object *, @{ N='FileName'; E={ $fileName }}, @{ N='Emp#'; E={ (++$i.Counter) }} |
    Export-Csv $out -NoTypeInformation