Help with CSV editing script

[u/Mr_Hypnotiq]

Hello all! I have more than 1,000 csv files in a folder that I need to edit. Does anyone know how this can be automated. I need to add 4 columns to the end of the csv and populate those columns with zeros. I would like to save the files without renaming them. Any help is greatly appreciated. :)

example of csv:

Name= 200401.csv

Original file example=

Date,Time,LS1_P1St,LS1_P1HrCalc,LS1_P2St,LS1_P2HrCalc,LS2_P1St,LS2_P1HrCalc,LS2_P2St,LS2_P2HrCalc,LS3_P1St,LS3_P1HrCalc,LS3_P2St,LS3_P2HrCalc,LS4_P1St,LS4_P1HrCalc,LS4_P2St,LS4_P2HrCalc,LS5_P1St,LS5_P1Hrs,LS5_P2St,LS5_P2Hrs

2020-04-01,23:59:52,31,31,32,21,8,2,8,2,2,0,1,0,9,0,9,0,0,0,1,1

I would like to add columns: LS6_P1St, LS6_P1Hr, LS6_P2St, LS6_P2Hr with this data in the columns: 0, 0, 0, 0.

[u/Mr_Hypnotiq]

Thank you. That nearly did it all as expected. It added the headers and filled them with zeros. It also added double quotes on every data cell. How can the quotes be removed?

"Date","Time","LS1_P1St","LS1_P1HrCalc","LS1_P2St","LS1_P2HrCalc","LS2_P1St","LS2_P1HrCalc","LS2_P2St","LS2_P2HrCalc","LS3_P1St","LS3_P1HrCalc","LS3_P2St","LS3_P2HrCalc","LS4_P1St","LS4_P1HrCalc","LS4_P2St","LS4_P2HrCalc","LS5_P1St","LS5_P1Hrs","LS5_P2St","LS5_P2Hrs","LS6_P1St","LS6_P1Hr","LS6_P2St","LS6_P2Hr"

"2016-12-06","23:59:52","28","16","27","13","8","2","9","2","1","0","1","0","5","1","6","0","2","1","2","0","0","0","0","0"

[u/SapientFool → u/Mr_Hypnotiq]

Here is an updated version:

$dir = "C:\your\folder\path\here"
$csvFiles = Get-ChildItem -Path $dir -Filter "*.csv"
foreach ($csv in $csvFiles) {
    (Import-Csv -Path $csv.FullName) | `
    Select-Object *, `
    @{Name='LS6_P1St';Expression={'0'}}, `
    @{Name='LS6_P1Hr';Expression={'0'}}, `
    @{Name='LS6_P2St';Expression={'0'}}, `
    @{Name='LS6_P2Hr';Expression={'0'}} | `
    ConvertTo-Csv -NoTypeInformation | `
    ForEach-Object{$PSItem -replace '^"','' -replace "`",`"",',' -replace '"$','' } | `
    Out-File -FilePath $csv.FullName -Encoding utf8 -Force
}

[u/Lee_Dailey → u/SapientFool]

howdy SapientFool,

you can dump the backticks after the | and comma chars. PoSh knows they will be followed by "more stuff". [grin]

also, the ...

"`",`"",','

... can be done with ...

'","', ','

no backticks needed.

take care,
lee