CODEWITHSUNDEEP
powershellcommand-line
Alex Gordon
Alex Gordon

Reputation: 60691

how to prepend filename to every record in a csv?

How do we prepend the filename to ALL the csv files in a specific directory?

I've got a bunch of csv files that each look like this:

ExampleFile.Csv

2323, alex, gordon
4382, liza, smith

The output I'd like is:

ExampleFile.Csv, 2323, alex, gordon
ExampleFile.Csv, 4382, liza, smith

How do we prepend the filename to ALL the csv files in a specific directory?

I've attempted the following solution:

Get-ChildItem *.csv | ForEach-Object {
    $CSV = Import-CSV -Path $_.FullName -Delimiter ","
    $FileName = $_.Name

    $CSV | Select-Object *,@{E={$FileName}} | Export-CSV $_.FullName -NTI -Delimiter ","
}

However, this did not work because it was altering the first row. (My data does not have a header row). Also, this script will append to each record at the end rather than prepend at the beginning.

Upvotes: 0

Views: 586

Answers (2)

AdminOfThings
AdminOfThings

Reputation: 25001

If your files do not have headers and the column count is unknown or unpredictable, you can read each line with Get-Content, make the changes, and then use Set-Content to make the update.

Get-ChildItem *.csv | ForEach-Object {
    $Filename = $_.Name
    $Fullname = $_.FullName
    $contents = Get-Content -Path $Fullname | Foreach-Object {
        "{0}, {1}" -f $Filename,$_
    }
    $contents | Set-Content -Path $Fullname
}

Upvotes: 1

Dusty Vargas
Dusty Vargas

Reputation: 945

You're missing the column header name I think. Take a look at the duplicate (or original, rather) and see Shay's answer. Your Select-Object should look like:

$CSV | Select-Object @{Name='FileName';Expression={"$filename"}},* | Export-Csv -Path $FileName -NoTypeInformation -Delimiter ','

That worked fine for me with multiple CSVs in a directory when using the rest of your sample code verbatim.

Upvotes: 1

Related Questions