Ignoring header when importing text files to access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to import data from a text file, but the file has several rows of
header information that is getting in the way. My field names are in the
10th row of the text file and the data begins in the 11th row. They are tab
delimited files. The files are very regular otherwise, everything is always
in the same place and field names are always the same. I have many of these
files to import, so I would like to avoid having to open each file in excel
to lop off the first 9 rows, then saving as a new txt file in order to avoid
losing the original header info (never know when I might need it). On import
I am creating a new table for each imported file.

Anyone have any suggestions?

Thanks,

tjw
 
You could use VBA code to create a new text file from the original by
opening the original text file, create a new text file, read in the first 10
rows, then read the 11th row and write it to the new text file, and continue
read/write loop until the file has been fully read. Then close the new
textfile so that you can import the data from it.

Alternatively, if the original file's first 10 rows are just data that
correspond to the data fields in format for the actual data rows, import the
text file into a temporary table, then use append query to copy the desired
data to the permanent table.
 
Here's a little function that should do the job. It has not been
thoroughly tested and you use it at your own risk.



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, 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
 
Thanks for the help. I'll give it a try.

tjw

John Nurick said:
Here's a little function that should do the job. It has not been
thoroughly tested and you use it at your own risk.



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, 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
Click to expand...
 
Back
Top