Import .txt files w/headers

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Hi,

How do I import 70 .txt files into Access, where the first
2 lines in each file are header lines that I do not need?
The data needed starts on line 3 of all the files. All
the files need to be appended.

I believe this needs to be done in VB. I am very new to
coding and would appreciate very specific details.


Thanks in advance....Jan
 
You can combine the two ideas show below - the first shows how to import all
files in a folder.
The second how to import a file and bypass the wizard.
(You can't use the wizard if you need to skip the first 2 rows!)
Maybe you can just open each file, delete the first 2 rows and then import
the rest using the wizard!)
Joe



How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

----------------------------------------------------------------------------
----
Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Here's one way to approach this (assumes each text file is comma-delimited
and that the order of data are the same as the field order in the table):


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableToWhichDataWillBeAppended", _
dbOpenDynaset, dbAppendOnly)
strFile = Dir("C:\MyFolderName\*.txt")
Do While strFile <> ""
intFile = FreeFile()
' open the text file in order to read the data
Open "C:\MyFolderName\" & strFile As #intFile For Input
' discard the first two lines in the text file
Line Input #intFile, strLine
Line Input #intFile, strLine
' append each record from the text file to the table
Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ",")
rst.AddNew
For intFields = LBound(varArray) To UBound(varArray)
rst.Fields(intFields - LBound(varArray)).Value = _
varArray(intFields)
Next intFields
rst.Update
Loop
Close #intFile
strFile = Dir()
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Back
Top