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

Question

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?

This should work : $x | Add-Member -NotePropertyName FileName -NotePropertyValue $Fname; $x | Add-Member -NotePropertyName Emp# -NotePropertyValue $count — jdweng, May 16 '23 at 10:03
you're adding to `$data2` (the collection being enumerated) instead of to `$x` (the item in the collection) — Santiago Squarzon, May 16 '23 at 12:07
perhaps I don't understand where to place your code but it doesn't seem to do anything — ljg, May 16 '23 at 12:20
You should initialize $count to zero before entering the loop. — Walter Mitty, May 16 '23 at 13:00
You should leave out the $count = in front of the $count++. ++ stores the incremented value back in the variable. — Walter Mitty, May 16 '23 at 13:01

score 0 · Accepted Answer · answered May 16 '23 at 12:19

0

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

answered May 16 '23 at 12:19

Santiago Squarzon

41,465
5
14
37

1

Perfect, thank you so much. Brilliant solution in my eyes. – ljg May 16 '23 at 12:22
@ljg glad it was helpful – Santiago Squarzon May 16 '23 at 12:24
@ljg do you want me to add how it would be done with `Add-Member` ? I wouldn't recommend that solution tho, it is very inefficient – Santiago Squarzon May 16 '23 at 12:36

iRon · Answer 2 · 2023-05-16T15:41:06.423

Agree, it shouldn't be that complicated...
Anyways, using this Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?) which is not standard PowerShell equipment!. You might use this semantic sugar:

Import-Csv $filePath |Join (,'Test' * 4) |Join (1..4) -Name FileName, Empl#

Employee State FileName Empl#
-------- ----- -------- -----
Sally    ME    Test         1
Bob      FL    Test         2
Pete     ID    Test         3
Martha   CA    Test         4

Explanation
The syntax above takes advantage of the following Join-Object features:

When the -on parameter is omitted, the objects are joined side-by-side.
An array of scalars, can be added to a PowerShell object, were it gets the property name <Value> by default
Multiple lists can be joined by chaining the join commands
By default, if the left - and the right object contain the same (unrelated) property the values will be joined in a PSObject[].
The -Discern (alias -Name) parameter can be used to split the joined (PSObjext[]) properties (in this case the ` property) over multiple properties (aka columnns).

score 0 · Answer 3 · answered May 16 '23 at 19:54

I note that there is already an accepted answer. This answer provides an alternative to Select-Object for constructing new objects on the fly. Some people might find it easier to follow. I don't know how this compares with the accepted answer when it comes to speed.

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

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

Import-Csv $filePath |
    % {
        [pscustomobject]@{
            Employee = $_.Employee
            State = $_.State
            Filename = $Fname
            "Emp#" = $counter++
            }
        } |
    export-csv $out -NoTypeInformation

NOTES:

I defined $datadir for my own local testing purposes. It was a free variable in your posted attempt.
I used your code, for the most part, anywhere that it makes no difference.
The variable $counter is just an ordinary variable. I initialized it to 1, and used increment after. This is just a style choice.
% is shorthand for Foreach-Object.
Within the loop for each record, I build a hashtable explicitly, then cast it as a [PSCustomObject]. This gets piped to Export-Csv.

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

3 Answers3