How to efficiently delete the last line of a multiline string when the line is empty/blank?

Question

I'm trying to delete blank line at the bottom from the each sqlcmd output files, provided other vendor.

$List=Get-ChildItem * -include *.csv
foreach($file in $List) {
    $data = Get-Content $file                   
    $name = $file.name
    $length = $data.length -1                   
    $data[$length] = $null                      
    $data | Out-File $name -Encoding utf8 
}

It takes bit long time to remove the blank line. Anyone knows a more efficient way?

Answer 1

Using Get-Content -Raw to load files as a whole, as a single string into memory and operating on that string will give you the greatest speed boost.

While that isn't always an option depending on file size, you mention sqlcmd files, which can be assumed to be small enough.

Note:

• By blank line I mean a line that is either completely empty or contains whitespace (other than newlines) only.

• The trimmed string will not have a final terminating newline following the last line, but if you pass it to Set-Content (or Out-File), one will be appended by default; use -NoNewline to suppress that, but not that especially on Unix-like platforms even the last line of text files is expected to have a trailing newline.

• Trailing (or leading) whitespace on a non-blank line is by design not trimmed, except where noted.

• The solutions use the -replace operator, which operates on regexes (regular expressions).

Remove all trailing blank lines:

^{Note: If you really want to remove only the last line if it happens to be blank, see the second-to-last solution below.}

(Get-Content -Raw $file) -replace '\r?\n\s*$'

In the context of your command (slightly modified):

Get-ChildItem -Filter *.sqlcmd | ForEach-Object {
 (Get-Content -Raw $_.FullName) -replace '\r?\n\s*$' |
   Set-Content $_.FullName -Encoding utf8 -WhatIf # save back to same file
}

^{Note: The -WhatIf common parameter in the command above previews the operation. Remove -WhatIf once you're sure the operation will do what you want.}

If it's acceptable / desirable to also trim trailing whitespace from the last non-blank line, you can more simply write:

(Get-Content -Raw $file).TrimEnd()

---

Remove all blank lines, wherever they occur in the file:

(Get-Content -Raw $file) -replace '(?m)\A\s*\r?\n|\r?\n\s*$'

Here's a conceptually much simpler version that operates on the array of lines output by Get-Content without -Raw (and also returns an array), but it performs much worse.

@(Get-Content $file) -notmatch '^\s*$'

^{Do not combine this with Set-Content / Out-Content -NoNewline, as that will concatenate the lines stored in the array elements directly, without line breaks between them. Without -NoNewline, you'll invariably get a terminating newline after the last line.}

---

Remove only the last line if it is blank:

(Get-Content -Raw $file) -replace '\r?\n[ \t]*\Z'

Note:

• [ \t] matches spaces and tabs, whereas \s more generally matches all forms of Unicode whitespace, including that outside the ASCII range.

• An optional trailing newline at the very end of the file (to terminate the last line) is not considered a blank line in this case - whether such a newline is present or not does not make a difference.

---

Unconditionally remove the last line, whether it is blank or not:

(Get-Content -Raw $file) -replace '\r?\n[^\n]*\Z'

Note:

• An optional trailing newline at the very end of the file (to terminate the last line) is not considered a blank line in this case - whether such a newline is present or not does not make a difference.

• If you want to remove the last non-blank line, use
  (Get-Content -Raw $file).TrimEnd() -replace '\r?\n[^\n]*\Z'

Answer 2

try replacing with this line. you will not have blank lines in your array value $data.

$data = get-content $file.FullPath | Where-Object {$_.trim() -ne "" }