Removing empty rows from csv

Question

_{Saturday, September 14, 2013 1:56 AM}

Hi,

I have a csv, where I need to remove two blank rows which show up after the column name, dotted line and the string " rows affected"

COL A COL B
                           --> remove

                            --> remove

       --> remove
(36644 rows affected)   --> remove
3400 1
1600 2
1600 3
1600 4

How can i format this in powershell?

Thanks

All replies (12)

_{Monday, September 16, 2013 7:37 PM ✅Answered | 1 vote}

OK, it's that comma in the "" line that's causing you trouble.  It wasn't in the original post, and wasn't accounted for in the code.  Try this:

$pathToCsv = '.\test.csv'

$content = Get-Content -Path $pathToCsv
$content -notmatch '(^[\s,-]*$)|(rows\s*affected)' | Set-Content -Path $pathToCsv

_{Saturday, September 14, 2013 2:25 AM}

Strictly speaking, that's not a csv file (no commas, no quotes around column names with spaces, etc).  However, this should do the trick:

$pathToCsv = '.\test.csv'

$content = Get-Content -Path $pathToCsv
$content -notmatch '(^[\s-]*$)|(rows\s*affected)' | Set-Content -Path $pathToCsv

_{Saturday, September 14, 2013 2:33 AM}

Hi,

If you know you're always going to skip exactly 4 rows, this will do the trick:

Get-Content .\input.csv | 
    Where { $_.ReadCount -lt 2 -or $_.ReadCount -gt 5 } | 
        Set-Content .\output.csv

EDIT: Hah, slow again. Why is it that everyone seems to love regexes recently? I should finally learn...

Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,760+ strong and growing)

_{Saturday, September 14, 2013 4:58 PM | 1 vote}

Here is another way without Regex, but therefore a bit longer than that of David:

foreach($line in [System.IO.File]::ReadLines($sourceFile)){
    if ((-not ($line.Contains("") -or $line.Contains("affected"))) -and $line.Length -gt 0){
        $line >> $destPath
    }
}

wizend

_{Saturday, September 14, 2013 5:45 PM}

Hi David,

Is there a way, I can remove the " " from the csv file?

Thanks

_{Saturday, September 14, 2013 10:10 PM}

The command I posted should already do that.  I copied and pasted your example data into a file, and it worked fine.

This regular expression:

$content -notmatch '(^[\s-]*$)|(rows\s*affected)'

looks for any line that does not contain "rows affected", and does not consist only of whitespace and the "-" character.  If that line is still in your file after running my code, it must contain some other character that isn't in the sample you've posted.

If you're having trouble finding the difference, try copying and pasting that line from your file to this thread, and we'll take a look.

_{Monday, September 16, 2013 7:34 PM}

Hi David,

The file looks something like this. I need to remove ,

PlazaID,LaneNumber

,

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

001700 , 17

001700 , 8

(5961 rows affected)

Thanks

_{Monday, September 16, 2013 7:59 PM}

Hi David,

Thank you very much, I really appreciate your help.

Regards,

Arnab

_{Friday, July 8, 2016 11:12 AM}

Thanks for the help.It really helped me in editing csv file

_{Friday, April 21, 2017 3:57 PM}

that did the trick.
 I built a script to create new users recently hr started sending me the csv file with blank lines.

I wanted to remove these rather than have to check each file.

here is a sample of what I was receiving for input. the blank lines were creating problems. this removed them. thanks

Name,Email,ADID,Password,Date submitted
firstname lastname,[email protected],newuser1234,,4/20/207
 , , ,,
 , , ,,
 , , ,,

_{Friday, April 21, 2017 5:53 PM}

select-object -skip 4

https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/select-object

Chris

_{Tuesday, November 21, 2017 5:15 PM}

Thank you!  This really helped!!!