How to add columns into multiple CSV files in a single directory without adding quotation marks?

Question

I have thousands of CSV files in a single directory. I'm looking for a way how to add 2 columns (including headers) to each file.

There are some conditions:

There is always 0 value in column #5
In column #6 I want to store file name without extension (ABC)

INPUT FILE EXAMPLE (FILENAME IS ABC.CSV)

HEADER1,HEADER2,HEADER3,HEADER4
04/22/2012,47.64,47.97,47.05
04/23/2012,47.6,48.2,47.4
04/24/2012,48.13,48.33,47.84
04/25/2012,47.81,48.14,47.59
04/26/2012,47.83,48.21,47.49
04/27/2012,47.2,47.31,46.84
04/28/2012,47.01,47.05,46.33

The code I've posted below has 1 problem,

It adds quotation marks ("04/22/2012","47.64","47.97","47.05","0","ABC") to every value in a new file.

OUTPUT FILE EXAMPLE I NEED

HEADER1,HEADER2,HEADER3,HEADER4,HEADER5,HEADER6
04/22/2012,47.64,47.97,47.05,0,ABC
04/23/2012,47.6,48.2,47.4,0,ABC
04/24/2012,48.13,48.33,47.84,0,ABC
04/25/2012,47.81,48.14,47.59,0,ABC
04/26/2012,47.83,48.21,47.49,0,ABC    
04/27/2012,47.2,47.31,46.84,0,ABC
04/28/2012,47.01,47.05,46.33,0,ABC




$files = Get-ChildItem ".\" -filter "*.csv"

for ($i=0; $i -lt $files.Count; $i++) {
$outfile = $files[$i].FullName + "out" 
$csv = Import-Csv $files[$i].FullName 
$newcsv = @()
foreach ( $row in $csv ) {
    $row | Add-Member -MemberType NoteProperty -Name 'HEADER 5' -Value '0'
    $row | Add-Member -MemberType NoteProperty -Name 'HEADER 6' -Value   $files[$i].BaseName
    $newcsv += $row
}
$newcsv | Export-Csv $files[$i].FullName -NoTypeInformation
}

And one more question. Because I have thousands of files in a directory, is this code efficient enough to do a task as fast as possible?

Somebody has already suggested me to improve code by Instead of looping thru the rows consider building te members with select

$csv = $csv | select-object *,@{n="HEADER5";e={0}},@{n="HEADER6";e={$file.BaseName}}

But I don't know how to implement his suggestion into my code.

In average there is about 300 rows per file. And I have about 4000 files in a single directory. — john50
– john50, Commented Jan 9, 2015 at 21:16
Is that trailing comma in the second data row a typo, or does that actually appear in the data? — mjolinor
– mjolinor, Commented Jan 9, 2015 at 21:19

mjolinor · Accepted Answer · 2015-01-09 21:56:41Z

2

Not tested:

$InputFolder = 'c:\SomeFolder'
$OutputFolder = 'c:\SomeOtherFolder'

Get-ChildItem $InputFolder -Filter *.* |
where {-not $_.psiscontainer} |
foreach {
 $FileName = $_.Name
 $BaseName = $_.Basename
 $data = Get-Content $_ -ReadCount 0
 "$($data[0]),Header5,Header6" | Set-Content $OutputFolder\$FileName
 $data[1..($data.Length -1)] -replace '$',",0,$BaseName" | 
  Add-Content $OutputFolder\$FileName
}

edited Jan 9, 2015 at 21:56

answered Jan 9, 2015 at 21:27

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

john50 Over a year ago

There is an error."Get-ChildItem : A parameter cannot be found that matches parameter name 'File' ". I would also need to write into same folder(rewrite those files, just to add 2 more columns).

mjolinor Over a year ago

What version are you running? Adjusted the answer for V2.

mjolinor Over a year ago

You can write them back to the same file by making $InputFolder and $OutputFolder the same. I intentionally made it not overwrite the files in the example so you can test without destroying the original data if it goes sideways.

john50 Over a year ago

I'm not sure if I checked it right, but in my registry it says RuntimeVersion v2.0.50727. But there is no Powershell "2" folder in my registry just "1" so i think it is powershell 1. I also use ps1 extension for my script files.

john50 Over a year ago

Thank you for your help. It works. Does it worth to upgrade to powershell V3 or V4? Would all my old scripts work under V3 or V4? Would those scripts run faster?

|

Hunter Eidson · Accepted Answer · 2015-01-09 22:09:58Z

1

I think my original answer depended too much on v3/v4 stuff, how about something like the following:

$files = Get-ChildItem ".\" | Where-Object { $_.Extension -eq ".csv" }

for ($i=0; $i -lt $files.Count; $i++) {
    $outfile = $files[$i].FullName + "out" 
    $csv = Import-Csv $files[$i].FullName 
    $newcsv = @()
    foreach ( $row in $csv ) {
        $row | Add-Member -MemberType NoteProperty -Name 'HEADER5' -Value '0'
        $row | Add-Member -MemberType NoteProperty -Name 'HEADER6' -Value   $files[$i].BaseName
        $newcsv += $row
    }

    ( $newcsv | ConvertTo-Csv -NoTypeInformation ) | Foreach-Object { $_ -replace  '"', '' } | Out-File $outfile

}

edited Jan 9, 2015 at 22:09

answered Jan 9, 2015 at 21:14

Hunter Eidson

1,91914 silver badges23 bronze badges

7 Comments

john50 Over a year ago

There is an error too. "Method invocation failed because [System.Object[]] doesn't contain a method name 'Replace'.

john50 Over a year ago

There are no quotation marks in my original files. For some reason those marks are added after adding 2 new columns into my files.

Hunter Eidson Over a year ago

The quotes are from the export-csv. (convertto-csv does it too).

john50 Over a year ago

I've tired to change your .Replace('"','') with Replace('"',"") but it didn't work either.

Hunter Eidson Over a year ago

made an edit to remove some newer features your version of powershell might handle poorly, I hope... :)

|

Collectives™ on Stack Overflow

How to add columns into multiple CSV files in a single directory without adding quotation marks?

2 Answers 2

9 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related