CODEWITHSUNDEEP
powershellcsv
jafeth
jafeth

Reputation: 33

Append filename to CSV in new column

I have a lot of csv files stored in a directory. In each csv file need to add the name as column this through powershell.

Example

File location: <SERVERNAME>\Export\FILENAME1.CSV

Contents:

1232;Description;a1
1232;Description;a2

The result must be:

1232;Description;a1;FILENAME1.CSV
1232;Description;a2;FILENAME1.CSV

Can someone help me with this?

Upvotes: 1

Views: 7668

Answers (2)

George Ou
George Ou

Reputation: 103

Mark Wragg's PowerShell code works, but I had to change the delimiter to , instead of ; so that it opens in Excel. I'm trying to figure out how to append the filename to the first column instead of the last because my files don't have the same number of fields so they don't align.

Upvotes: 2

Mark Wragg
Mark Wragg

Reputation: 23425

The following will append a Filename column to each .CSV file in a directory:

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

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

Explanation:

  • Uses Get-ChildItem to get all files named *.csv
  • Iterates through each file with ForEach-Object and uses Import-CSV to load their contents as a PowerShell object
  • Records the name of the file in $FileName
  • Uses Select-Object to add a calculated property with the name Filename and the value of the $FileName variable
  • Uses Export-CSV to write back over the original file. The -NTI (NoTypeInformation) switch is used to ensure the PowerShell object header line is not included.

Upvotes: 7

Related Questions