Search code examples
powershellcsvscripting

Use powershell to remove last row in .csv file

I am currently in need of a solution for one of our automation scripts. As part of the process it grabs the file from a source and it has to remove last row in the .csv and save it again as csv file(not sure why this has to be done, as file already comes as .csv, but without it - it bugs out). The problem is that I can't seem to figure out how to remove the last row. I came up with few solutions but most only work on .txt file or objects. What I have now is this:

Import-Csv $file | where {$_.'Search Status' -ne "Blank line"} | Export-Csv "\file.csv" -notypeinfo

It does delete the last line that has ' Blank Line' written in it, but it seems to just remove the text and when my other script parses the file it fails to do so properly. So to sum up - I need a script that would take this file, remove last row completely and save it again as .csv as 'export-csv' doesn't seem to do the job.

Solution

  • In PSv5+ you can use Select-Object's -SkipLast parameter:

    Get-Content $file | Select-Object -SkipLast 1 | Set-Content out.csv -Encoding UTF8

    Note that I'm simply using Get-Content rather than Import-Csv, as there's no good reason to incur the overhead of Import-Csv (construction of a custom object for each input line) and subsequent Export-Csv (serialization of each custom object to a comma-separated list of values in a single string) if the only task required is to drop a line as a whole.

    Also note that I've included an -Encoding argument to illustrate the point that you may want to control the output encoding explicitly, because the input encoding is never preserved in PowerShell.
    Set-Content will default to "ANSI" encoding on Windows PowerShell, and BOM-less UTF-8 on PowerShell Core.

    In PSv4-, you can employ the following technique to emulate -SkipLast 1; it allows you to do the same in a single pass too, and without needing to load the entire input file into memory:

    Get-Content $file | ForEach-Object { $prev = $null } {      
  if ($null -ne $prev) { $prev }
  $prev = $_
} | Set-Content out.csv -Encoding UTF8

    The above simply delays the output of the pipeline input object by one iteration, which effectively omits the last input object.