none
Removing empty rows from csv

    Question

  • 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

    Saturday, September 14, 2013 1:56 AM

Answers

  • 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

    Monday, September 16, 2013 7:37 PM

All replies

  • 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
    

    • Proposed as answer by Wizend Saturday, September 14, 2013 4:43 PM
    Saturday, September 14, 2013 2:25 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 2:33 AM
    Moderator
  • 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 4:58 PM
  • Hi David,

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

    Thanks

    Saturday, September 14, 2013 5:45 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.

    Saturday, September 14, 2013 10:10 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:34 PM
  • 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

    Monday, September 16, 2013 7:37 PM
  • Hi David,

    Thank you very much, I really appreciate your help.

    Regards,

    Arnab

    Monday, September 16, 2013 7:59 PM
  • Thanks for the help.It really helped me in editing csv file
    Friday, July 08, 2016 11:12 AM
  • 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,address@email.com,newuser1234,,4/20/207
     , , ,,
     , , ,,
     , , ,,

    • Edited by Katacombz Friday, April 21, 2017 4:02 PM add content
    Friday, April 21, 2017 3:57 PM
  • select-object -skip 4

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


    Chris

    Friday, April 21, 2017 5:53 PM
  • Thank you!  This really helped!!!
    Tuesday, November 21, 2017 5:15 PM