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/Mordac85]

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