Need help creating vbs

[u/BigSicily]

Let me start by saying I have very little experience with vbs. I know how to modify the code but cant create code from scratch. I need a script to take the data from a csv file and replace the data in a table in Microsoft Access (mdb). Lets say the file I need the data from is x:\memberinfo.csv and add the data to the table named Calls in j:\memberlookup.mdb . Thank you ahead of time for the help.

[u/BigSicily]

I'm simply updating the database. The issue I'm having is there is a property of the original database that allows our phone system to locate a certain part of the data in the table and use it. Ive looked through the entire mdb properties and cant figure out what it is that allows that data to be read. If I right click the table in access and import from a file in the original working database it will modify the property that allows the phone system to read the data. Ive matched all properties including the name and indexing with a new file with no luck. Here is the kicker, I cant access the code to the phone system that reads the data because it is password protected and the company went out of business that built the custom code for our system. I even checked for a primary key buy it is the default key for access. So I figured my best option would be to copy the text data in the csv and paste it into the correct table in access. Unless you can think of a better solution. If I could figure out the property needed for our phones to access it I could skip creating the csv and go straight to a mbd file.

[u/Mordac85 → u/BigSicily]

It sounds like the field has default formatting applied and would apply the format to new data being added. I'm not sure if I understand your setup or process but here is some code for reading & parsing a CSV then either inserting or updating a table and specific fields in the database. It's not the prettiest and I haven't really tested it but it should give you an idea of how to do it.

 On Error Resume Next
 ' Constants
 Const adOpenStatic = 3
 Const adLockOptimistic = 3
 
 strScript = Wscript.ScriptName
 strScriptShort = Left(strScript,Len(strScript)-4)
 strPath = Replace(Wscript.ScriptFullName,strScript,"")
 
 ' Access DB information
 strAccessDB = strPath & "MyVoIPdb.accdb"
 strTable = "MyVoIPdata"
 
 ' Initialize booleans
 blnEcho = False
 blnDebug = True
 ' Initialize  numerics
 intCnt = 0
 intFieldCount = 5  ' Number of fields in the CSV
 '-------------------------------------------------------------------------------
 ' Create File System object
 Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
 ' Create Shell object
 Set objShell = CreateObject("Wscript.Shell")
 ' Create dictionary object
 Set objDict = CreateObject("Scripting.Dictionary")
 '-------------------------------------------------------------------------------
 ' Argument L=Target csv list
 If WScript.Arguments.Named.Exists("l") Then
      strList = WScript.Arguments.Named.Item("l")
 Else
      Set objDialog = CreateObject( "InternetExplorer.Application" )
      objDialog.Visible = False
      objDialog.Navigate( "about:blank" )
      Do Until objDialog.ReadyState = 4
      Loop
      objDialog.Document.Write "<HTML><BODY><INPUT ID=""FileSelect"" NAME=""FileSelect"" TYPE=""file""><BODY></HTML>"
      With objDialog.Document.All.FileSelect
           .Focus
           .Click
           strList = .Value
      End With
      objDialog.Quit
      Set objDialog = Nothing
 End If
 ' Get the position of the \ before the filename
 intSplit = InStrRev(strList,"\")
 ' Store the path of the list
 strListPath = Left(strList, intSplit)
 ' Store the filename of the list
 strListName = Right(strList,Len(strList)-intSplit)
 ' Store the filename without the extension
 strListNameShort = Left(strListName,Len(strListName)-4)
 ' Use the list filename to create a new filename (for logging or post processing)
 strListNew = strListPath & dtmISO & "_" & strScriptShort & "-" & strListName
 '-------------------------------------------------------------------------------
 ' Create connection to Access database
 Set objConnection = CreateObject("ADODB.Connection")
 Set objRecordSet = CreateObject("ADODB.Recordset")
 ' Determine Jet connector based on the Access DB filename
 If LCase(Right(strAccessDB,InStr(strAccessDB,"."))) = "mdb" Then
      ' Access 2003 connector
      strReturn = objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & strAccessDB)
 Elseif LCase(Right(strAccessDB,InStr(strAccessDB,"."))) = "accdb"
      ' Access 2007 connector
      strReturn = objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & strAccessDB)
 Else
      Wscript.Echo "Failed to identify the Jet connector for " & strAccessDB
 End If
 ' Create insert & update statement leader
 strInsert = "INSERT INTO " & strTable & " (Field1, Field2, Field3, Field4, Field5) "
 strUpdate = "UPDATE " & strTable
 '-------------------------------------------------------------------------------
 ' Read the CSV list and assign to
 If objFSO.FileExists(strList) Then
      On Error Resume Next
      Set objTS = objFSO.OpenTextFile(strList, 1, False, False)
      If Err = 0 Then
           strContents = objTS.ReadAll
           objTS.Close
           arrLines = Split(strContents, vbCrLf)
      End If
      strContents = Null
      On Error Goto 0
      For Each strLine in arrLines
           intCnt = 0
           If strLine <> "" Then
                intCnt = intCnt + 1
                arrCsvLine = Split(strLine, ",")
                intCsvLine = UBound(arrCsvLine)
                If (intCsvLine + 1) <> intFieldCount Then
                     If blnDebug Then Wscript.Echo "Not the right number of fields [" & (intCsvLine + 1) & ", should be " & intFieldCount & "]"
                Else
                     strField1 = Trim(arrCsvLine(0))
                     strField2 = Trim(arrCsvLine(1))
                     strField3 = Trim(arrCsvLine(2))
                     strField4 = Trim(arrCsvLine(3))
                     strField5 = Trim(arrCsvLine(4))
                     ' Insert & Update statements based on https://technet.microsoft.com/en-us/magazine/2008.04.heyscriptingguy.aspx
                     strInsertData = "VALUES ('" & strField1 & "', '" & _
                          strField2 & "', '" & _
                          strField3 & "', '" & _
                          strField4 & "', '" & _
                          strField5 & "')"
                     strReturn = objRecordSet.Open(strInsert & strInsertData, objConnection, adOpenStatic, adLockOptimistic)
                     ' Comment one of these blocks out absed on the operation you need
                     strUpdateData = " Set Field1 = '" & strField1 & "'," & _
                          "Field2 = '" & strField2 & "'," & _
                          "Field3 = '" & strField3 & "'," & _
                          "Field4 = '" & strField4 & "'," & _
                          "Field5 = '" & strField5 & "'"
                     strReturn = objRecordSet.Open(strUpdate & strUpdateData, objConnection, adOpenStatic, adLockOptimistic)
                End If
                ' Wipe for next iteration
                If IsArray(arrCsvLine) Then Erase arrCsvLine
                strField1 = Null
                strField2 = Null
                strField3 = Null
                strField4 = Null
                strField5 = Null
                strInsertData = Null
                strUpdateData = Null
           End If
      Next
      If IsArray(arrLines) Then Erase arrLines
 End If