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