# Powershell to break apart large flat files (e.g. csv)

I created the below script to help break apart large flat files; to save myself effort opening files which are several gigs in a text editor and manually split them.

So far I've only tested on sample files, so I can't yet comment on the performance, but wanted to share my work so far / get feedback on whether I've missed any good opportunities for optimisation.

In the process section of the Split-FlatFileSub routine I've used writeline in several places; I could have put this statement later on, but would have had to add additional statements to handle potential header rows; which I did not want to do in the interests of performance (i.e. I've tried to ensure that the standard loop has as little as possible to do; with more involved operations only happening when the next file's required, as this will be less often).

clear-host

function Split-FlatFile {
[CmdletBinding()]
param (
[Parameter(Mandatory = $true)] [string]$Filename
,
[Parameter(Mandatory = $false)] [switch]$NoHeader        #assume header by default
,
[Parameter(Mandatory = $false)] [int]$PageSize = 1048576 #includes the header row / is the excel 2007+ row count
)
begin {
[long]$LinesPerPage =$PageSize
if (-not $NoHeader.IsPresent) {$LinesPerPage--
}
if ($LinesPerPage -le 0) { throw "You need to be able to have at least 1 line per page! Current lines per page is:$LinesPerPage"
}
if (-not (test-path -Path $Filename)) { throw "File '$Filename' does not exist"
}
$file = Get-Item$Filename
[string]$OutputFilenameFormat = join-path -path ($file.PSParentPath -replace 'Microsoft\.PowerShell\.Core\\FileSystem\:\:(.*)','$1') -ChildPath ("{0}.{1}{2}" -f$file.BaseName,'{0:000000}',$file.Extension) } process { [System.IO.File]::ReadLines($sourceFN) 
| Split-FlatFileSub -OutFilenameFormat $OutputFilenameFormat -NoHeader:$NoHeader -LinesPerPage $LinesPerPage } } #not called directly by the user; but invoke via the parent routine above function Split-FlatFileSub { [CmdletBinding()] param ( [Parameter(Mandatory =$true, ValueFromPipeline = $true)] [string]$Line
,
[Parameter(Mandatory = $true)] [string]$OutFilenameFormat
,
[Parameter(Mandatory = $false)] [switch]$NoHeader        #assume header by default
,
[Parameter(Mandatory = $true)] [int]$LinesPerPage = 1048575 #excludes the header row / is the excel 2007+ row count - 1
)
begin {
[string]$header =$null
[long]$currentLineNo =$LinesPerPage + 1
[long]$currentFileNo = 1 [string]$fn = ($OutputFilenameFormat -f$currentFileNo)
[System.IO.TextWriter]$writer = [System.IO.File]::CreateText($fn) #assumption: filename does not exist
}
process {
if($currentLineNo -lt$LinesPerPage) {
$currentLineNo++$writer.WriteLine($Line) #would writelineasync be better? Experiment later } else { if($currentLineNo -eq $LinesPerPage) {$currentFileNo++
$fn = ($OutputFilenameFormat -f $currentFileNo)$writer.Dispose()
[System.IO.TextWriter]$writer = [System.IO.File]::CreateText($fn) #assumption: filename does not exist
$writer.WriteLine($Header)
$writer.WriteLine($Line)
} else {
if (-not $NoHeader.IsPresent) {$header = $Line$writer.WriteLine($Header) } }$currentLineNo = 0
$fn } #I could do the$writer.writeline here, but that would mean more logic to check if already written (i.e. for first line)
#probably a cleaner way to skip the header line; but for now not worth worrying about
}
end {
$writer.Dispose() } } [string]$SourceFn = 'c:\temp\raw\FlatFileSplitterTestDoc.dat'
Split-FlatFile -Filename $SourceFn -PageSize 10 #simple demo to test with before throwing huge docs at this  • FYI: Ran the script over a 3.5GB file with defaults (Header / PageSize=1048576), creating 8 x ~475MB files. Computer was a VM with 2 x 3.2GHz cores and 8GB ram. This took ~8 minutes. Not blinding speed, but faster and easier than doing this manually. When I get some free time I'll revisit to focus on making this performant. Nov 26, 2015 at 8:51 • Related post: stackoverflow.com/questions/4192072/… Nov 26, 2015 at 8:52 ## 1 Answer [Reflection.Assembly]::LoadWithPartialName("System.IO") | out-null  I'm almost certain that this statement is not needed at all. You should be able to reference [System.IO.File] without loading an assembly. [System.IO.File]::ReadLines($sourceFN) |
Split-FlatFileSub -OutFilenameFormat $OutputFilenameFormat -NoHeader:$NoHeader -LinesPerPage \$LinesPerPage


Try to avoid using backtick  as a line continuation character. It's hard to see, and usually unnecessary. In the case of a pipeline, you can end the line with the pipe | and then continue on the next line; it tends to be a lot more clear.

#not called directly by the user; but invoke via the parent routine above
function Split-FlatFileSub {


You could define this function inside the other function, to truly use it as an internal function. If you're writing a module you could just not export it.