Trim header and/or footer rows from text file

Description

Occasionally one comes across a text file where the first few rows contain header information, followed by a standard fixed-width or delimited layout for the actual records. Access's standard text file importing and linking mechanism, provided by the Jet database engine, can't handle this.

If it's a one-off import, I find it simplest to use a Perl one-liner. With Perl installed on your system, executing this

     perl -i.bak -ne "print if $. > 5" filespec

at the Windows command prompt will dump the first 5 lines of the file, leaving the original file renamed with ".bak". See also TopAndTail.pl, which can remove footer lines as well as header lines.

If it's a regular import that needs to be done under program control in VBA, use the TrimFileHeader() function below, or the TrimFileHeaderAndFooter() function further down the page.

Function to remove header lines

Function TrimFileHeader( _
  ByVal FileSpec As String, _
  ByVal LinesToTrim As Long, _
  Optional ByVal BackupExtension As String = "") As Long

  'Removes the specified number of lines from the beginning
  'of a textfile.
  'Optionally leaves the original file with its extension
  'changed to BackupExtension.
  'Returns 0 on success, otherwise the number of the error.

  Dim fso As Object 'Scripting.FileSystemObject
  Dim fIn As Object 'Scripting.TextStream
  Dim fOut As Object 'Scripting.TextStream
  Dim fFile As Object 'Scripting.File
  Dim strFolder As String
  Dim strNewFile As String
  Dim strBakFile As String
  Dim j As Long

  On Error GoTo Err_TrimFileHeader

  Set fso = CreateObject("Scripting.FileSystemObject")

  With fso
    'Handle relative path in Filespec
    FileSpec = .GetAbsolutePathName(FileSpec)
    strFolder = .GetParentFolderName(FileSpec)
    strNewFile = .BuildPath(strFolder, fso.GetTempName)
    'Open files
    Set fIn = .OpenTextFile(FileSpec, 1) '1=ForReading
    Set fOut = .CreateTextFile(strNewFile, True)

    'Dump header
    For j = 1 To LinesToTrim
      fIn.ReadLine
    Next

    'Read and write remainder of file
    Do While Not fIn.AtEndOfStream
      fOut.WriteLine fIn.ReadLine
    Loop

    fOut.Close
    fIn.Close

    'Rename or delete old file
    If Len(BackupExtension) > 0 Then
      strBakFile = .GetBaseName(FileSpec) _
        & IIf(Left(BackupExtension, 1) <> ".", ".", "") _
        & BackupExtension
      If .FileExists(.BuildPath(strFolder, strBakFile)) Then
        .DeleteFile .BuildPath(strFolder, strBakFile), True
      End If
      Set fFile = .GetFile(FileSpec)
      fFile.Name = strBakFile
      Set fFile = Nothing
    Else
      .DeleteFile FileSpec, True
    End If

    'Rename new file
    Set fFile = .GetFile(strNewFile)
    fFile.Name = .GetFileName(FileSpec)
    Set fFile = Nothing
    Set fso = Nothing

  End With
  'normal exit
  TrimFileHeader = 0
  Exit Function
Err_TrimFileHeader:
  TrimFileHeader = Err.Number
End Function

Function to Trim Header and Footer Lines