Here's a solution, but you should consider it more like a sketch than anything else. It can be refined (and probably needs to be).
1. Create a table, like this:
Code:
Name: Tbl_GPS_Data
Fields: Header, Text
CON, Text
ID, Text
LT, Text
LN, Text
DT, Text
BN, Text
TR, Text
PL, Text
Trailer, Text
PrimaryKey: BN
2. In an independant module (i.e. not a module linked to a form or to a report), paste the following code:
Code:
Function Import_GPS_Data(ByVal FilePath As String)
Dim strFileName As String
Dim strLine As String
Dim varElements As Variant
Dim strSQL As String
Dim intFHandle As Integer
Dim i As Integer
If Right(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
strFileName = Dir(FilePath & "*.")
Do While Len(strFileName)
intFHandle = FreeFile
Open FilePath & strFileName For Input As #intFHandle
Do Until EOF(intFHandle)
Line Input #intFHandle, strLine
varElements = Split(strLine, "|")
strSQL = ""
For i = 0 To UBound(varElements)
If Len(strSQL) > 0 Then strSQL = strSQL & ", "
strSQL = strSQL & "'" & varElements(i) & "'"
Next i
strSQL = "INSERT INTO Tbl_GPS_Data ( Header, CON, ID, LT, LN, DT, BN, TR, PL, Trailer ) " & _
"VALUES ( " & strSQL & " );"
CurrentDb.Execute strSQL, dbFailOnError
Loop
Close #intFHandle
strFileName = Dir
Loop
End Function
You call the function by passing the name of the folder where the files are stored. Ex:
Code:
Import_GPS_Data "C:\Documents and Settings\SinnDHo\My documents\Access\Kabul"
3. You probably should implement an error handling mechanism into the function.
4. The final destination of the imported data should probably be a table with a more proper structure, not every field defined as Text. You would then write another function that can read data from the import table (Tbl_GPS_Data), format the data and store them into another table.
dBforums
>
Konfused in Kabul
07-16-11, 05:51
Konfused in Kabul
Linear Mode
