CODEWITHSUNDEEP
powershellexport-to-csv
Pillic
Pillic

Reputation: 35

Add new 1st column to CSV based on filename

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

Upvotes: 1

Views: 196

Answers (2)

Pillic
Pillic

Reputation: 35

Thanks to Wasif Hasan, I ended up with this code. The quotes doesnt need to be removed, as there are text fields in the CSV, that would breake if I try to remove the qoutes:

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

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

$delimiter = ';'

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

}

Upvotes: 0

wasif
wasif

Reputation: 15480

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.

Upvotes: 1

Related Questions