Sounds like you need to write a program to open each file and parse the
information
here is an example you can use as an analogy:
****************
table structure for Companies:
CompID, autonumber
CompCode, text, 10 --> start at 2
Area, text, 10 --> start at 13
Country, text, 8 --> start at 24
IDNum, long integer --> start at 33, 10 places
Company, text, 50 --> start at 44
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function ImportTextFile(pFilename As String, pTablename As String) As Long
'written by Crystal Long
'(e-mail address removed)
'NEEDS reference to Microsoft DAO Library if you open recordset
'BASIC USEAGE
' assign this to a command button event
' =ImportFile("c:\path\filename.csv", "Tablename")
'RETURNS
' the number of records imported
On Error GoTo ImportTextFile_error
Dim mFileNumber, mLine As String, i As Integer
'you can open a recordset to put values into
Dim r As Recordset
Set r = CurrentDb.OpenRecordset(pTablename)
'alternately, you could use APPEND queries...
mFileNumber = FreeFile
Open pFilename For Input As #mFileNumber
'skip first two lines
Input #mFileNumber, mLine
Input #mFileNumber, mLine
i = 0
Do While Not EOF(mFileNumber)
Input #mFileNumber, mLine
'remove this line after you get your parsing stuff working right
' If MsgBox(mLine, vbOKCancel, "Line " & i) = vbCancel Then Stop
'put your parsing and assignment statements here
If Mid(mLine, 2, 10) <> "----------" Then
'i is the line number you are on
i = i + 1
r.AddNew
r!CompCode = Mid(mLine, 2, 10)
r!Area = Mid(mLine, 13, 10)
r!Country = Mid(mLine, 24, 8)
r!IDNum = CLng(Mid(mLine, 33, 10))
r!Company = Mid(mLine, 44, 34)
r.Update
End If
Loop
Close #mFileNumber
r.Close
Set r = Nothing
ImportTextFile = i
Exit Function
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " ImportFile: "
& pFilename
'press F8 to step through code and correct problem
Stop
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
to learn more about the INPUT statement (and related statements),
position your mouse pointer over the word in a module sheet and press F1
for context-sensitive help
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
here is code to loop through a directory:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub LoopThroughDirectory(pFilePath as string)
'crystal
'strive4peace2006 at yahoo dot com
'PARAMETERS
'pFilePath --> ie: "c:\data\whatever\"
On Error GoTo Proc_Err
Dim mPath As String, mFilename As String
'if path was passed, us it
'otherwise, use database directory folder
if len(trim(nz(pFilePath,""))) 0 then
mPath = trim(pFilePath)
else
mPath = currentproject.path
end if
'make sure path ends in slash
if right(mPath ,1) <> "\" then
mPath = mPath & "\"
end if
'or *.mdb or ...
mPath= mPath & "*.*"
mFilename = Dir(mPath)
Do While mFilename <> ""
'do something
'get next filename in the directory
mFilename = Dir
Loop
Proc_Exit:
On Error Resume Next
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " LoopThroughDirectory"
'delete or comment next line out after code is done
Stop: Resume
Resume Proc_Exit
End Sub
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
