-1

To delete empty rows in multiple CSV files by using Powershell. this is the code I am trying with for each loop. But it is writing empty files. The resulting file should open in excel and notepad++ with proper format.

Get-ChildItem $paramDest -Filter *Test*.csv | ForEach-Object {
$content = Get-Content $_.FullName | Where { $_.Replace("","","").trim() -ne "" }
Out-File -InputObject $content -FilePath $_.FullName
}

enter image description here

I have some other set of files, the code should work for both these kinds of files. I am okay to have 2 separate codes for these 2 separate files.

Here another file sample format

enter image description here

James Z
  • 12,209
  • 10
  • 24
  • 44
DAR
  • 47
  • 6

1 Answers1

1

Try with this

Input

enter image description here

Command

Import-Csv -Path "C:\sample.csv" | Where-Object { $_.PSObject.Properties.Value -ne '' } | Export-Csv -Path "C:\Sample_clean.csv" -NoTypeInformation

Output*

enter image description here

Edit: To update Multiple files, thanks to @Santiago Squarzon

Get-ChildItem $paramDest -Filter *Test*.csv | 
Foreach-Object { 

# get the content of file
$content = Get-Content $_.FullName 

# replace all "," with empty string and exclude those lines and save the output to original file
$content  | where { $_.Replace('","','').trim('"') -ne '' } | Set-Content $_.FullName 
}
Bharat
  • 1,192
  • 7
  • 14
  • I have multiple files, tried the below code, not working as expected $paramDest = "C:\Test\" Get-ChildItem $paramDest -Filter *.csv | ForEach-Object { Import-Csv -Path $_.FullName | Where-Object { $_.PSObject.Properties.Value -ne '' } | Export-Csv -Path $_.FullName -NoTypeInformation } – DAR Jul 30 '21 at 05:10
  • Do you want to run this line for many file? this sample is for a file and it works perfectly – Bharat Jul 30 '21 at 05:17
  • @user10260125 updated the solution for multiple files – Bharat Jul 30 '21 at 06:14