Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Question Unanswered: Konfused in Kabul

    Hello All

    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.

    My situation:
    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 -

    C:\Users\carl.spackler\Documents\converge\archives samples\2011_188

    Once I reach the folder above, individuals files (no extension) are:


    Opening in notepad, the string is presented as shown below:

    L:|CON=998|ID=VFH57|LT=34.4078330994|LN=70.4883346 558|DT=20110707000120|BN=HTH_893|TR=HTH-TAPESTRY::TAPOWNED|PL=150|

    Desired Outcome:

    I need each record to be stored in a table with the "BN=" value as my Key Field.


    -- I am the IT resource
    -- My training resource todate has been "F1"
    -- I do not have the ability to alter the way the files are saved to the archive, I can only access once it has been archived

    Sorry this is such a wordy question - my background is not in IT, so I really don't know what the key information required for resolution would be.

    Any guidance is welcome!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    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:
    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:
    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
            Close #intFHandle
            strFileName = Dir
    End Function
    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.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts