This is my first post to a db forum, any advice would be greatly apprecated on this one. I am a US Civilian working at various locations in Afghanistan. I have solid PC skills with day to day issues, but a novice at best when it comes to Access and any sort of code - but I have dabbled.
I need to consolidate 1,500+ files (2kb each) from three seperate laptops to a single location on a daily basis. I assume access would be the best place to create this database, but I welcome advice.
Each file is an archive file from global positioning software -- and the file has no extension associated. File will not allow me to permenantly associate the file type with Word/Excel/Notepad. I can open and maniuplate by hand, but that is extremely labor intensive.
Files are saved by Julien Date in folder generated automatically by the software. Sample path to folder which contains 1,000+ individual records generated daily -
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:
Fields: Header, Text
2. In an independant module (i.e. not a module linked to a form or to a report), paste the following 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) & "'"
strSQL = "INSERT INTO Tbl_GPS_Data ( Header, CON, ID, LT, LN, DT, BN, TR, PL, Trailer ) " & _
"VALUES ( " & strSQL & " );"
CurrentDb.Execute strSQL, dbFailOnError
strFileName = Dir
You call the function by passing the name of the folder where the files are stored. Ex:
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.