Import data from txt file or excel to Mysql vbscript

Posted on 2011-10-27
Last Modified: 2012-06-27
I have a script that runs and pulls data from Active directory and puts it into a mysql databse. The problem is I need to also have the employees birthday and start date in the database. Of course AD doesn't have a default place to but that information. So I had HR pull the info for me but it comes in an excel file with employeeID (144401) Birthdate (07/28/1980) DOH (08/31/2006)
Now I can pull the employeeID number from AD which I do in the code included. What I need is while its adding users from ad to the database to also read the excel or txt file and compare the employeeID from AD and the excel file and take the birthdate and doh and add it also to the employee record in the data base.
Option Explicit

On Error Resume Next

' The name of the Access database to use
Dim strDatabase : strDatabase = "intranet"
' The name of the Access table to use
strDatabase    = "employeeinfo"

' List of users who should NOT be exported
' This list should contain the user's logon name
' Separate each name by a comma
Const ForReading = 1

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objTextFile : Set objTextFile = objFSO.OpenTextFile ("blocked_users.txt", ForReading)

Dim strText : strText = objTextFile.ReadAll

Dim arrUsers : arrUsers = Split(strText, ",")
Dim strUsers
For Each strUsers in arrUsers
Dim Users2Skip : Users2Skip  = strUsers

' Constant for the account being disabled
' Constant for the search to search subtrees

Const adOpenStatic     = 3
Const adLockOptimistic = 3

' General variable declarations
Dim objConnectionDB, objRecordsetDB
Dim objConnectionAD, objCommandAD, objRecordsetAD
Dim dtStart
Dim strSQL
Dim objRootDSE, strDNSDomain
Dim strDN, intUAC, strSam, strDisplayName, strDescription, strManagerDN, strPhoneExt, strPhoneNumber, dtExpireDate
Dim blnProcessUser
Dim objUser, objManager

' Get the start time of the script
dtStart = TimeValue(Now())

'Create and open ADO connection to the Access database
Set objConnectionDB = CreateObject("ADODB.Connection")
Set objRecordsetDB  = CreateObject("ADODB.Recordset")

' Open the database
objConnectionDB.Open "dsn=intranet;"

' Open the recordset
objRecordsetDB.Open strDatabase, objConnectionDB, adOpenStatic, adLockOptimistic

' Define the SQL statement used to clear out previous
' user info which was exported from AD
strSQL = "DELETE FROM " & strDatabase & " WHERE ImportedFromAD = 'True'"

'Wscript.Echo "Removing previously exported records from the " & _
'    strDatabase & " database."
objConnectionDB.Execute strSQL, , 129

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
'strDNSDomain   = objRootDSE.Get("defaultNamingContext")
strDNSDomain	= "OU=015-Users,OU=015-Cleveland,DC=na,DC=sysco,DC=net"
' Create and open an ADO connection to AD
Set objConnectionAD = CreateObject("ADODB.Connection")
Set objCommandAD    = CreateObject("ADODB.Command")

objConnectionAD.Provider = "ADsDSOObject"
objConnectionAD.Open "Active Directory Provider"

' Set connection properties
With objCommandAD
  .ActiveConnection = objConnectionAD

  ' Use SQL syntax for the query
  ' This retrieves all values named in the SELECT section for
  ' user accounts which do not have the Notes section = NoExport.
  ' The recordset is sorted ascending on the displayName value.
  .CommandText = _
             "Select userAccountControl, distinguishedName," & _
             " sAMAccountname, displayName" & _
             " FROM 'LDAP://" & strDNSDomain & "'" & _
             " WHERE objectCategory = 'person' AND" & _
             " objectClass = 'user' AND info <> 'NoExport'" & _
             " ORDER BY displayName"

  .Properties("Page Size")     = 1000
  .Properties("Timeout")       = 30
  .Properties("Searchscope")   = ADS_SCOPE_SUBTREE
  .Properties("Cache Results") = False
End With

'Wscript.Echo "Running the query to find users."
Set objRecordSetAD = objCommandAD.Execute

' Move to the first record in the recordset

' Loop until we reach the end of the recordset
Do While NOT objRecordsetAD.EOF
  ' Blank out/reset a few variables..just in case.
  strDN  = ""
  intUAC = ""
  strSam = ""
  strDisplayName = ""
  strManagerDN   = ""
  dtExpireDate   = ""
  blnProcessUser = True

  ' Get the userAccountControl value. This lets us, among other things,
  ' determine if the account is disabled.
  intUAC = objRecordsetAD.Fields("userAccountControl")

  ' Process user if account is not disabled.

    ' Get the user's logon name
    strSam         = objRecordsetAD.Fields("sAMAccountname")

    ' Determine if the user is included in the list of logon names to skip.
    If Instr(UCase(Users2Skip), UCase(strSam)) Then blnProcessUser = False

    ' Get the user's display name
    strDisplayName = objRecordsetAD.Fields("displayName")

    ' Set boolean value to skip this user if the user's display name is blank.
    If strDisplayName = "" Then blnProcessUser = False

    ' If our simple checks went ok, we can now process this user.
    If blnProcessUser = True Then

      ' Get the distinguished name of this user
      ' The syntax is something like:
      '           CN=Joe E. Law,OU=Sales,OU=US,DC=mydomain,DC=local
      strDN = objRecordsetAD.Fields("distinguishedName")

      ' Bind to the user object
      Set objUser = GetObject("LDAP://" & strDN & "")

      ' Process the user
      With objUser
        'Wscript.Echo "Processing user: " & strDisplayName

        ' Get the user's account expiration date
        dtExpireDate = CDate(.AccountExpirationDate)

        ' Process the user if the user's account expiration date is not passed
        If (dtExpireDate = "") OR _
          (dtExpireDate = CDate("01/01/1970")) OR _
          (dtExpireDate >= Date()) Then

        'Add new record to the Access database
          ' Get user data from AD and populate the new record in the
          ' Access database

          ' You can use the .Get("xxx") or .xxx formats to retrieve the data
          ' All fields on the left MUST exist in the Access table
          objRecordsetDB("UserName")        = LCase(.sAMAccountName)
          objRecordsetDB("FirstName")       = .Get("givenName")
          objRecordsetDB("LastName")        = .sn
          strPhoneNumber = .telephoneNumber
          If strPhoneNumber <> "" Then
				strPhoneExt = Right(strPhoneNumber, 4) 
          objRecordsetDB("WorkPhone")     = strPhoneExt
            ' Release this object reference
            Set strPhoneNumber = Nothing
            Set strPhoneExt = Nothing
          End If
          objRecordsetDB("Email")           = .mail
          objRecordsetDB("MobilePhone")     = .mobile
          objRecordsetDB("Title")           = .title
          objRecordsetDB("Department")      = .department
          objRecordsetDB("EmployeeID")		= .employeeID
          strDescription			        = .description
          If Left(strDescription,1) = "S" Then
          objRecordsetDB("Description")     =  strDescription
            ' Release this object reference
            Set strDescription = Nothing
		End If
          ' Get the distiguished name of the manager
          strManagerDN = .manager
          ' If manager value is not blank then process
          If strManagerDN <> "" Then
            ' Bind to manager's account
            Set objManager = GetObject("LDAP://" & strManagerDN & "")
              ' Populate the Access database with the display name of the manager
              objRecordsetDB("Manager")     = objManager.givenName & " " &
            ' Release this object reference
            Set objManager = Nothing
          End If
    ' Check image folder for image matching user 
    	  Dim strFirstName : strFirstName = .Get("givenName")
          Dim strLastName : strLastName = .sn
    	  Dim strImage : strImage = "staff/" & strLastName & "_" & strFirstName & ".jpg"
		  Dim filesys : Set filesys = CreateObject("Scripting.FileSystemObject") 
		If filesys.FileExists(strImage) Then
   			objRecordsetDB("UserImage")  = "Yes"
			objRecordsetDB("UserImage")  = "No"	   
		End If 

          ' Define that this record was exported from AD
          objRecordsetDB("ImportedFromAD")  = "True"

        ' Commit the record

        ' Release this object reference
        Set objUser = Nothing
        End If
      End With
    End If
  End If

 ' Move to the next record in the AD recordset

' Close the Access database recordset
' Close the Access database connection

' Release these object references
Set objRecordsetDB = Nothing
Set objConnectionDB = Nothing

' Close the AD recordset
' Close the AD connection

' Release these object references
Set objRecordsetAD = Nothing
Set objConnectionAD = Nothing


Open in new window

Question by:JHopkins213
    LVL 20

    Accepted Solution

    The easiest way to code this is to take the excel file and save it as a tab delimited file.  (We can adapt the code to go straight to Excel, but it takes a little more work and depends on if Excel is installed on the machine running the code or not - the objects we use are different for those two scenarios).

    I recommned loading the file into a dictionary object for easy comparisons as your loop through the users in AD.  

    For using the dictionary object and a tab delimited file do the following to your code:

    1.  Add this code to your declarations section:

    Dim objHRFile			' **** Added 10/27/2011 ****
    Dim strHRFile			' **** Added 10/27/2011 ****
    Dim dicHRData			' **** Added 10/27/2011 ****
    Dim arrHRData			' **** Added 10/27/2011 ****

    Open in new window


    2.  This code loads the HR file.  I would do it right after you set the start time of the script (dtStart = TimeValue(Now()):
    Make sure the set the value for strHRFile to be for the tab delimited file.

    ' **** Added 10/27/2011 ****
    ' Load File from HR
    ' ***** you need to set the file path and name here *****
    Set dicHRData = CreateObject("Scripting.Dictionary")
    strHRFile = "C:\YOURPATH\YOURFILE.txt"
    If objFSO.FileExists(strHRFile)Then
    	Set objHRFile = objFSO.OpenTextFile (strHRFile, 1) ' 1 is for Reading
    	Do Until objHRFile.AtEndOfStream
    		' Split out Data from File
    		arrHRData = split(objHRFile.Readline, vbTab)
    		' Load to Dictionary Object for easy look up later
    		dicHRData.add arrHRData(0), arrHRData
    End If
    Erase arrHRData

    Open in new window

    3.  Add this code where you build you database record.  I recommend right before you update the record (objRecordsetDB.Update).
    Make sure the update the field names in the code and the array values if the columns are not in order of EmployeeID, DateOfBirth and DateOfHire

    ' **** Added 10/27/2011 ****
    ' **** You need to adjust for the correct DB Names Here
    If dicHRData.exists(employeeID) then
    	arrHRData = dicHRData.Item(employeeID)
    	objRecordsetDB("DateofBirth") = arrHRData(1) ' Or use Proper array value if it is not in second column
    	objRecordsetDB("DateofHire") = arrHRData(2) ' Or use Proper array value if it is not in second column
    End If

    Open in new window

    Let me know how it goes.


    Author Comment

    I get an error Microsoft VBScript runtime error: Variable is undefined: 'employeeID'

     If dicHRData.exists(EmployeeID) then
    	arrHRData = dicHRData.Item(EmployeeID)
    	objRecordsetDB("BirthDate") = arrHRData(1) ' Or use Proper array value if it is not in second column
    	objRecordsetDB("StartDate") = arrHRData(2) ' Or use Proper array value if it is not in second column
    End If 

    Open in new window


    Author Comment

    ' **** Added 10/27/2011 ****
    ' **** You need to adjust for the correct DB Names Here
    If dicHRData.exists(employeeID) then
    	arrHRData = dicHRData.Item(employeeID)
    	objRecordsetDB("DateofBirth") = arrHRData(1) ' Or use Proper array value if it is not in second column
    	objRecordsetDB("DateofHire") = arrHRData(2) ' Or use Proper array value if it is not in second column
    End If 

    Open in new window

    I figured out what I did wrong your code worked great thankyou my problem is I for got to put a . in arrHRData = dicHRData.Item(.employeeID)

    Author Closing Comment

    Very easy to understand. Thank you so much.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now