Editing the first/last lines of a 1GB+ text file on Windows without loading the entire file into memory

Question

I have some flat-text data files ("CSV") with sizes up to 3GB and simply need to remove the first 3 lines of text, and add an empty line at the end. Since I have a lot of these files, I would like to find a fast way of doing this.

The problem with these first lines is that they are not CSV data, but random text that doesn't follow the column format. Because of this, SQL Server's Bulk Insert statement can't process these files.

One option would be to use a PowerShell script, but using Get-content or streams would always involve reading the entire file and completely outputting it again. Is there a way to directly modify the file on-disk, without loading it entirely into memory and recreating the file?

Preferably, I'm looking for a PowerShell way to do this. Although third-party tools might also be interesting...

Answer 1

Removing content from the beginning of a file requires rewriting the file.

You can use tail -n +4 input.csv > output.csv to remove the first three lines (requires 105 seconds for a 15 GB Wikipedia dump on my low-end server, i.e. about 150 MB per second). On Windows tail is available with Cygwin e.g.

Answer 2

I guess there's no way to not read the whole file in the memory, at least I don't know any.

$csv = gci "C:\location" -filter *.csv | % { 
    (Get-Content $_.FullName | select -skip 3) | Set-Content $_.FullName 
    Add-Content -path $_.FullName -value ""
}

This would be a PowerShell solution which requires to load the whole file into memory.

• search every csv from a location with gci,
• loop over the found csv files with foreach alias %,
• get their whole content (can take some time) with get-content,
• select everything but skip the first 3 lines select -skip
• and set that content to the file with set-content.
• the last line will add a new line to the file add-content

Edit: You can try to make this whole thing faster by adding the -ReadCount Parameter to your get-content call.

-ReadCount (int)

Specifies how many lines of content are sent through the pipeline at a time. The default value is 1. A value of 0 (zero) sends all of the content at one time.

This parameter does not change the content displayed, but it does affect the time it takes to display the content. As the value of ReadCount increases, the time it takes to return the first line increases, but the total time for the operation decreases. This can make a perceptible difference in very large items.

Edit2: I tested get-content with readcount. sadly i couldn't find a text file larger than 89mb. but the difference is already significant:

PS C:\Windows\System32> Measure-Command { gc "C:\Pub.log" -readcount 0 }


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 1
Milliseconds      : 22
Ticks             : 10224578
TotalDays         : 1.18340023148148E-05
TotalHours        : 0.000284016055555556
TotalMinutes      : 0.0170409633333333
TotalSeconds      : 1.0224578
TotalMilliseconds : 1022.4578




PS C:\Windows\System32> Measure-Command { gc "C:\Pub.log" -readcount 1 }


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 10
Milliseconds      : 594
Ticks             : 105949457
TotalDays         : 0.000122626686342593
TotalHours        : 0.00294304047222222
TotalMinutes      : 0.176582428333333
TotalSeconds      : 10.5949457
TotalMilliseconds : 10594.9457

so get-content $_.FullName -readcount 0 is the way to go

Answer 3

After digging a bit deeper, I think what this question boils down to is the following:

Is there a way to edit a file on a HDD formatted using NTFS, directly, in-place?

My answer would be that minor changes could be made by using Hex-editors that make direct changes on the harddrive level, but making huge changes like removing entire portions of the file would probably corrupt the filesystem. So the question again, boils down to:

Does NTFS support editing data-blocks assigned to a file without rewriting the entire file?

My guess is going to be... no. But I'd be interested to learn a bit more about the details of this...