Use powershell to remove last row in .csv file

Question

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.

mklement0 · Accepted Answer · 2018-07-20 21:07:02Z

7

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.

edited Jul 20, 2018 at 21:07

answered Jul 20, 2018 at 20:53

mklement0

452k68 gold badges728 silver badges987 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maximilian Burszley Over a year ago

Huh. -SkipLast has no documentation (even if it is obvious by the parameter name).

mklement0 Over a year ago

@TheIncorrigible1: Good point: it isn't described, but it is listed (as part of the syntax diagram in the v6 docs; in the v5.1 and v5.0 documents, it also has a parameter-details paragraph in the body, but still lacks a description); see github.com/PowerShell/PowerShell-Docs/issues/2716

Robert Dyjas Over a year ago

@TheIncorrigible1, I made PR for that and now -SkipLast description is live (still yet to be merged for v5). Just FYI

user6811411 · Accepted Answer · 2018-07-20 20:43:19Z

1

Import the csv
get the item count
Select the first (count - 1) Rows
export

$FileIn = '.\file.csv'
$FileOut= '.\file2.csv'
$csv = Import-Csv $FileIn
$csv | Select-Object -First ($csv.count -1) |
    Export-Csv $FileOut  -NoTypeInformation

answered Jul 20, 2018 at 20:43

user6811411

Comments

Theo · Accepted Answer · 2018-07-20 20:44:51Z

1

or do

$x = @(Get-Content -Path $file)
($x[0..($x.Length - 2)]) | Out-File -FilePath $file

to save from doing the [array]::Reverse() twice

answered Jul 20, 2018 at 20:44

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Comments

Maximilian Burszley · Accepted Answer · 2018-07-20 20:22:57Z

0

You can load the file into an object, remove the last line, and then re-write it:

$f = @(Get-Content -Path $file)
[array]::Reverse($f)
$f = $f | Select-Object -Skip 1
[array]::Reverse($f)
$f | Out-File -FilePath $file

Note: this is extremely lazy (and performs poorly) on my part because I didn't spend more time thinking about it, but it gets the job done.

answered Jul 20, 2018 at 20:22

Maximilian Burszley

19.9k7 gold badges39 silver badges68 bronze badges

Comments

WayneA · Accepted Answer · 2018-07-20 20:52:55Z

0

depending on your version of powershell you can just use the skiplast parameter of Select-Object

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

answered Jul 20, 2018 at 20:52

WayneA

3391 silver badge7 bronze badges

Comments

Lars Rönnbäck · Accepted Answer · 2023-08-17 12:43:56Z

We had some additional requirements, such as being able to check several files in a folder and making sure that the number of columns in the first row matches the number of columns in the last row. Sometimes files for us break during transfer and leave a final incomplete row in the file. Posting our solution here in case it may help someone else:

$path = "."
$enc = "utf8" # file encoding
$delim = '\t' # column separator, tab in this example
$terminator = "`n" # LF (unix style row terminators)
$pattern = "^.*Example\.txt$"
# ----===================================================================================----
$files = @(Get-ChildItem FileSystem::"$path" | Where-Object {$_.Name -match $pattern})
ForEach ($file in $files) {
    $filename = $file.FullName
    # count number of columns in the first row and then compare with last row
    $head_count = ((Get-Content $filename -Head 1) -split $delim).Count
    $tail_count = ((Get-Content $filename -Tail 1) -split $delim).Count
    If($head_count -ne $tail_count) {
        Write-Output "MODIFYING: $filename ($head_count head and $tail_count tail columns)"
        Copy-Item $filename -Destination "$filename.original"
        (Get-Content "$filename.original" | Select -SkipLast 1) -join $terminator | Out-File -NoNewline -Encoding $enc $filename
    }
    Else {
        Write-Output "NO ACTION: $filename ($head_count head and $tail_count tail columns)"
    }
}

Collectives™ on Stack Overflow

Use powershell to remove last row in .csv file

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related