Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Importing specific fields from a Text File

    Not exactly sure where to start with this one. I have an existing process in my database that imports data from a .txt file. The file is NOT in a 'import friendly' format (e.g.: not a common delimitter or anything like that). I should also mention that I did not create this process - simply copied it over from a prior database, so I'm somewhat guessing on what it's doing. But essentially it looks like the code is going through the .txt file line-by-line, and using the "icrit" variable (which is grabbing the first 4 characters of each line, after the leading spaces are trimmed off) to look for certain reoccurring words - "ID:", "NAME:"


    Once it finds one of those words, it's entering the corresponding data into the respective table.
    There's also a recurring section on the .txt file labeled as "GROUPS" followed by a series of dashes.. What I'm attempting to figure out is how to incorporate those groups into the import process. They vary from user to user, so one user could show "GROUP 003" and others might show "GROUP 0008". Is this possible?

    Below is a snip of the code, and I also attached a screenshot of the ".txt" file itself. What you see in the screenshot is essentially what it looks like for all 200+ users on the file - but like I said, the actual list of "GROUPS" (which is circled in blue on the screenshot) may vary - both in the name of the groups and the number of groups listed for each user on the file.

    Code:
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    
    
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    
    
    Dim icrit As String
    Dim strFile As String
    Dim varLine As Variant
    Dim strSpec As String
    Dim strTable As String
    Dim fs, f, ts, s
    Dim ct As Integer
    Dim X As Integer
    Dim strUID As String
    Dim strnme As String
    Dim strldte As String
    Dim strsec As String
    Dim strTable1 As String
    Dim strTable2 As String
    Dim intCounter As Long
    
    
    DoCmd.SetWarnings False
    
    
    
    
    On Error Resume Next
    
    
    Set db = CurrentDb()
        
    strFile = "\\corpnt01\Global_SH\IC\users.txt"
    strTable = "tblUID_Import"
    strTable1 = "tblNME_Import"
    
    
    X = 1
    
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFile)
    Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
    
    
        With ts    'loops through records
        
            Do While ts.AtEndOfStream <> True
        
        varLine = Trim(ts.ReadLine) 
         
        intCounter = 1
            
        icrit = (Mid(varLine, 1, 4)) 
    
    
        If icrit = "ID: " Then  
        
            strUID = Trim(Mid(varLine, 4, 40))
    
    
        Set rec = db.OpenRecordset(strTable, dbOpenDynaset) 
        
        With rec
    
    
        ct = rec.RecordCount
    
    
            If ct = 0 Then
            GoTo LoadIt
            End If
            
    LoadIt:
            .AddNew
                ![SGIU_Import_ID] = X
                ![SGIU_User_ID] = strUID
           .Update
         
        End With
        
      
        ElseIf icrit = "NAME" Then  
        
              strnme = Trim(Mid(varLine, 6, 40))
    
    
        Set rec = db.OpenRecordset(strTable1, dbOpenDynaset)
        
        With rec
        ct = rec.RecordCount
            If ct = 0 Then
            GoTo LoadIt1
            End If
    
    
    LoadIt1:
            .AddNew
                ![SGIN_Import_ID] = X
                ![SGIN_User_Name] = strnme
           .Update
        
        End With
        
     
        End If
        End If
        
    Loop
    
    
    End With
    Attached Thumbnails Attached Thumbnails sg txt file screenshot.PNG  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure what you are asking....

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So you only want to extract the data from the lines of the text file when the left part equals "Name:", "ID:" or the line has ("GROUP 003" or "GROUP 0008" or "GROUP XXXX") ? (where XXXX is a 3 or 4 digit number)
    This is not hard... I did something like this recently... see the code at https://www.accessforums.net/showthr...992#post426992





    There are issues in the snippet of code you posted.
    - there is an extra "End If' statement
    - the variable "X" is never incremented
    - the variable intCounter is never incremented and never used
    - the "Set rec = db.OpenRecordset(strTable, dbOpenDynaset)" statements are in the wrong place.

    Here is the relevant code: (I deleted non essential lines for this example snippet)
    Code:
        Do While ts.AtEndOfStream <> True
            varLine = Trim(ts.ReadLine)
            If icrit = "ID: " Then
                Set rec = db.OpenRecordset(strTable, dbOpenDynaset)
            ElseIf icrit = "NAME" Then
                Set rec = db.OpenRecordset(strTable1, dbOpenDynaset)
            End If
        Loop
    Lets say in the text file there are 50 lines that have "Name:" and 50 lines that have "ID:" at the beginning.
    Q: How many times will each of the open recordset "rec" lines be opened?

    A: Because the "Set rec = ...." line is inside the "DO While" loop, the recordset will be opened 100 times. (50 for the "ID:" lines and 50 for the "Name:" lines)


    -----------------------------------------------------
    I looked at the code a little more and I am confused as to what the code is supposed to do.

    There are lines
    Code:
                    Set rec = db.OpenRecordset(strTable, dbOpenDynaset)
    
                    With rec
                        ct = rec.RecordCount
                        If ct = 0 Then
                            .AddNew
                            ![SGIU_Import_ID] = X
                            ![SGIU_User_ID] = strUID
                            .Update
                            '     GoTo LoadIt
                        End If
                    End With
    Code:
                    Set rec = db.OpenRecordset(strTable1, dbOpenDynaset)
                    With rec
                        ct = rec.RecordCount
                        If ct = 0 Then
                            .AddNew
                            ![SGIN_Import_ID] = X
                            ![SGIN_User_Name] = strnme
                            .Update
                            '                        GoTo LoadIt1
                        End If
    
                    End With

    You open the recordset.
    If the record count is 0 (no records in the recordset/table) you add a record.
    What is supposed to happen if there are 1 or more records in the recordset/table??


    I am REALLY confused now!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-09-2015, 12:10 PM
  2. Replies: 1
    Last Post: 07-02-2014, 12:42 PM
  3. Importing text file with blank fields changes to null
    By Egoyret in forum Import/Export Data
    Replies: 9
    Last Post: 11-10-2011, 01:51 PM
  4. Replies: 6
    Last Post: 11-05-2011, 09:01 AM
  5. Replies: 1
    Last Post: 11-05-2010, 04:31 PM

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
  •  
Other Forums: Microsoft Office Forums