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

    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.

    Example:

    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:

    GDMSLOC_2011188_000127_9981_6
    GDMSLOC_2011188_000227_9981_5
    GDMSLOC_2011188_000328_9981_6
    ...etc.

    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.

    Restraints:

    -- 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!
    Thanks
    Carl

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    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:
    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.
    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
  •