Import Help (Access 2000 SR1)

Topic

New Reply

Can anyone assist with the following? I have a text file which is an export of Active Directory information. The ability to format the export is limited and produces the results similar to the example below. (See attached file for actual example.)

Members of local group [sharename-1subdirectory-1]:

John.Smith
Jane.Smith
John.Black

Members of local group [sharename-1subdirectory-2]:

Jane.Doe
John.Doe

I need to import this into a table in Access in the following format:

Field1: ShareName
Field2: UserName

Does anyone know if this is possible to achieve?

Reply | Quote

Replies

The following VBA routine uses DAO, so you must select Tools| References… in the Visual Basic Editor, and make sure that the reference to the Microsoft DAO 3.6 Object Library is ticked, then click OK.

The code assumes that you have imported or linked the text file as a table named MemberList, with one text field named Field1. It also assumes that you have already created an empty table tblMemberList with two text fields Field1 and Field2 of sufficient length to hold the longest sharename and username.

Insert a new module and copy the following module into it. Adapt the names as needed, then run the code by clicking somewhere in the procedure and pressing F5.

Sub ImportText()
‘ Declare variables
Dim dbs As DAO.Database
Dim rstIn As DAO.Recordset
Dim rstOut As DAO.Recordset
Dim strPath As String
Dim intPosLeft As Integer
Dim intposRight As Integer

‘ Set error handling
On Error GoTo ErrHandler

‘ Assign object variables
Set dbs = CurrentDb
Set rstIn = dbs.OpenRecordset(“MemberList”, dbOpenForwardOnly)
Set rstOut = dbs.OpenRecordset(“tblMemberList”, dbOpenDynaset)

‘ Loop through the lines of the text file
Do While Not rstIn.EOF
‘ Do we have a new sharename?
If Left(rstIn!Field1, 10) = “Members of” Then
‘ Extract sharename
intPosLeft = InStr(rstIn!Field1, “[“)
intposRight = InStr(rstIn!Field1, “]”)
strPath = Mid(rstIn!Field1, intPosLeft, intposRight – intPosLeft + 1)
ElseIf Not (Trim(rstIn!Field1) = “”) Then
‘ Otherwise, if not on an empty line, add a new record
rstOut.AddNew
rstOut!Field1 = strPath
rstOut!Field2 = Trim(rstIn!Field1)
rstOut.Update
End If
‘ Move on
rstIn.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rstOut.Close
rstIn.Close
Set rstOut = Nothing
Set rstIn = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
‘ Inform user, then clean up
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Your great…. the code works PERFECTLY …. thank you very much for the quick response!

Reply | Quote

Your great…. the code works PERFECTLY …. thank you very much for the quick response!

Reply | Quote

The following VBA routine uses DAO, so you must select Tools| References… in the Visual Basic Editor, and make sure that the reference to the Microsoft DAO 3.6 Object Library is ticked, then click OK.

The code assumes that you have imported or linked the text file as a table named MemberList, with one text field named Field1. It also assumes that you have already created an empty table tblMemberList with two text fields Field1 and Field2 of sufficient length to hold the longest sharename and username.

Insert a new module and copy the following module into it. Adapt the names as needed, then run the code by clicking somewhere in the procedure and pressing F5.

Sub ImportText()
‘ Declare variables
Dim dbs As DAO.Database
Dim rstIn As DAO.Recordset
Dim rstOut As DAO.Recordset
Dim strPath As String
Dim intPosLeft As Integer
Dim intposRight As Integer

‘ Set error handling
On Error GoTo ErrHandler

‘ Assign object variables
Set dbs = CurrentDb
Set rstIn = dbs.OpenRecordset(“MemberList”, dbOpenForwardOnly)
Set rstOut = dbs.OpenRecordset(“tblMemberList”, dbOpenDynaset)

‘ Loop through the lines of the text file
Do While Not rstIn.EOF
‘ Do we have a new sharename?
If Left(rstIn!Field1, 10) = “Members of” Then
‘ Extract sharename
intPosLeft = InStr(rstIn!Field1, “[“)
intposRight = InStr(rstIn!Field1, “]”)
strPath = Mid(rstIn!Field1, intPosLeft, intposRight – intPosLeft + 1)
ElseIf Not (Trim(rstIn!Field1) = “”) Then
‘ Otherwise, if not on an empty line, add a new record
rstOut.AddNew
rstOut!Field1 = strPath
rstOut!Field2 = Trim(rstIn!Field1)
rstOut.Update
End If
‘ Move on
rstIn.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rstOut.Close
rstIn.Close
Set rstOut = Nothing
Set rstIn = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
‘ Inform user, then clean up
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote