Add new 1st column to CSV based on filename

Question

I have a folder with 500 CSVs each one with app 80.000 rows. I need to add the date (based on the filename) in the 1st column.

The script that I took from various samples does almost what it should. It creates a new file for each in the folder with a new column at the 1st place with the right header name but all rows in this column are empty.

Filename sample: 2020-03-31_xxxxxxxx.csv

It would be great if some one could have a look at this.

$items = Get-ChildItem -Path "C:\Temp\Data\*.csv"

for ($i=0; $i -lt $items.Count; $i++)

{

$outfile = $items[$i].FullName + "out"

Import-Csv $items | 

Select-Object @{Name='Date';Expression={Get-ChildItem -Name $items.Name.SubString(0,9)}},* | 
Export-Csv -NoTypeInformation -delimiter ";” $outfile

}

Also in the export should be a converting for not having the "" in the CSV, but I am also not able to add this, without getting errors.

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

Answer 1

Try this:

$items = Get-ChildItem -Path "C:\Temp\Data\*.csv" 
for ($i=0; $i -lt $items.Count; $i++) { 

$outfile = $items[$i].FullName + "out" 

Import-Csv $items[$i] | Select-Object @{n='Date';e={$items[$i].Fullname.ToString().Split("_")[0]}},* | Export-Csv -NoTypeInformation -delimiter ";” -Force $outfile 
}

You were not selecting the date, but you were getting the child item info of each file. So you were getting ugly formatted csv. Also you were importing all csv at a time not a single one.