how to prepend filename to every record in a csv?

Question

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.

Possible duplicate of [Add Column to CSV Windows PowerShell](https://stackoverflow.com/questions/17022017/add-column-to-csv-windows-powershell) — Jeff Zeitlin, Sep 03 '19 at 17:26
@JeffZeitlin would love to know more about why you think this is duplicative? — Alex Gordon, Sep 03 '19 at 17:30
Have you _looked_ at the duplicate? It seems to me that the only difference between what you're asking and what the proposed duplicate is doing is _where_ in the record you're seeking to add the new property... — Jeff Zeitlin, Sep 03 '19 at 17:33
The same technique will work. In your case, the "computed" value is the file name that the data is coming from, and you'll simply put that value _before_ the * in the `Select-Object`. Read and understand; if you simply blindly accept code that you do not understand as an answer, how are you going to know that it's not malicious? — Jeff Zeitlin, Sep 03 '19 at 17:39
im sorry it might be obvious to you, but i have almost no experience with powershell. perhaps there's also a big intelligence gap between the two of us — Alex Gordon, Sep 03 '19 at 17:41

score 1 · Answer 1 · answered Sep 03 '19 at 18:26

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.

score 1 · Answer 2 · answered Sep 03 '19 at 19:43

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
}

how to prepend filename to every record in a csv?

2 Answers2