Remove Empty Second Line From Very Large CSV File in Windows 10

Question

I have an very large CSV file (data for all the taxi trips in NYC during March). The second line is empty which angers PostgreSQL's import. I can run

(gc file.csv) | ? {$_.trim() -ne "" } | set-content file_trimmed.csv

to get rid of the offending line, but since it checks every line it takes a very longtime to run (the file is on an external drive and my computer is a potato).

Anyway to make this faster?

Answer 1

Use

gc file.csv | ? {$_.trim() -ne "" } | set-content file_trimmed.csv

What's wrong in the original command (paraphrased Delete all blank lines from a text file using PowerShell in Tim Curwick's PowerShell blog):

The parentheses around the Get-Content statement force it to finish loading the whole contents into an object before sending them down the pipeline. (If we are writing to a different file than we were reading from, we could speed up the command by eliminating the parentheses, thus allowing us to read from the one and write to the other simultaneously.)

Test script 1264263.ps1 measures merely reading a large file and omits writing to an output one:

param (
        [Parameter()][string]$file = 'green_tripdata_2014-03.csv'
)

Push-Location 'D:\test'

#$file = 'green_tripdata_2014-03.csv'
"$file`: {0:N3} KiB" -f $((Get-Item $file).Length /1024 )

' GC $file                          :' + ' {0:N7} sec' -f (Measure-Command {
    $y = Get-Content $file
}).TotalSeconds

Start-Sleep -Seconds 1
' GC $file  | ? {$_.trim()}         :' + ' {0:N7} sec' -f (Measure-Command {
    $y = (Get-Content $file | 
        Where-Object {$_.trim()}) #| Set-Content "$file2"
}).TotalSeconds

Start-Sleep -Seconds 1
' GC $file  | ? {$_.trim() -ne ""}  :' + ' {0:N7} sec' -f (Measure-Command {
    $y = (Get-Content $file | 
        Where-Object {$_.trim() -ne "" }) #| Set-Content "$file2"
}).TotalSeconds

Start-Sleep -Seconds 1
'(GC $file) | ? {$_.trim() -ne ""}  :' + ' {0:N7} sec' -f (Measure-Command {
    $y = (Get-Content $file) | 
        Where-Object {$_.trim() -ne ""} #| Set-Content "$file2"
}).TotalSeconds

Pop-Location

Output shows that improved command (case #3) could work cca 10 times faster than the original one (case #4):

PS D:\PShell> D:\PShell\SU\1264263.ps1
green_tripdata_2014-03.csv: 197,355.560 KiB
 GC $file                          : 27.4584778 sec
 GC $file  | ? {$_.trim()}         : 59.2003851 sec
 GC $file  | ? {$_.trim() -ne ""}  : 61.0429012 sec
(GC $file) | ? {$_.trim() -ne ""}  : 615.8580773 sec
PS D:\PShell>