MySQL Import Automation

[Paul Grimes]

Good morning all.
We ahve a server running MSSQL and MySQL and the manager wants a seperate MySQL server. So this is what happens. The engineer has a PDA and when he completes a job it sends back a .csv file. When it lands on the server it runs a script .vbs file and imports the csv into the MySQL. This has worked great for years, but now I need to look at this script to import the csv into a seperate MySQl server. The script at present is this. Any ideas how to implement this please? Thanks

"On Error Resume Next

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim resGuid
resGuid = WScript.Arguments(0)
Dim fileName
fileName = "timesheet." & resGuid & "csv"

'Message fileName

Dim file

If fso.FileExists(fileName) Then
Set file = fso.OpenTextFile(fileName, 1)
'Message "have file"
Dim colLine, resLine
colLine = file.ReadLine
resLine = file.ReadLine
file.close()
Set file = Nothing

Message colLine
Message resLine

colLine = Replace(colLine, """", "")

Dim shell
Set shell = CreateObject("WScript.Shell")

Dim cmd
cmd = "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlimport --local --user=JobDeskMySQL --password=]-iA3d8C1Kll7dW4 MysqlDatabase --fields-optionally-enclosed-by=\"" --fields-terminated-by=, --ignore-lines=1 --columns=" & colLine & " --verbose " & fileName

'Message cmd


Dim cmdMinus
cmdMinus = Replace(cmd, colLine, "")


'Dim resultFields
'resultFields = Split(resLine, ",")
'Dim resGuid
'resGuid = resultFields(9)
Message resGuid

Dim output
output = RunCommandAndGetResult(cmd)

Message("Warning..")
Dim warnings
warnings = Split(output, "Warnings: ")

Message warnings(1)

Dim warningCount
warningCount = Split(warnings(1))(0)

Message warningCount


' Check whether report is in database
cmd = "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql -e ""SELECT count(*) FROM timesheet WHERE reportGuid = '" & resGuid & "'"" --user=JobDeskMySQL --password=]-iA3d8C1Kll7dW4 MysqlDatabase"
Message cmd

Dim selectCount
selectCount = RunCommandAndGetResult(cmd)

Dim count
count = Split(selectCount, "(*)")

Message count(1)

Dim numCount
numCount = Split(count(1), vbCrLf)
Message numCount(1)

Dim archiveFolder

Message archiveFile

If numCount(1) <> "1" Then
SendAlert "Import unsuccessful", resLine
Message "Failed"
archiveFolder = "Error"
ElseIf warningCount <> "0" Then
SendAlert "Imported OK", resLine
Message "Warning"
archiveFolder = "timesheet\2022"
Else
Message "Success"
archiveFolder = "Done"
End If

Dim archiveFile
archiveFile = "AutoImport_" & archiveFolder & "\" & fileName

If fso.FileExists(archiveFile) Then
fso.DeleteFile(archiveFile)
End If
fso.moveFile fileName, archiveFile


Set fso = Nothing

End If

Function RunCommandAndGetResult(cmd)
Dim scriptExec

'Message cmdMinus

Set scriptExec = shell.Exec(cmd)

Do While scriptExec.Status = 0
WScript.Sleep 100
Loop

Dim output
If not scriptExec.StdOut.AtEndOfStream Then
output = scriptExec.StdOut.ReadAll()
End If

Message output

RunCommandAndGetResult = output

End Function

Public Sub SendAlert(subject, body)
Set oMessage = CreateObject("CDO.Message")
oMessage.From = "[e-mail]"
oMessage.To = "[e-mail]"
oMessage.Subject = "Timesheet: " & subject
oMessage.Textbody = body
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing";) = 2
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate";) = 1
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver";) = "mail.domain.com"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername";) = "[e-mail]"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword";) = "W&QaKkDSXzj9"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport";) = 587
oMessage.Configuration.Fields.Update
oMessage.Send
End Sub

Public Sub Message(text)
'MsgBox text
End Sub"