MySQL Forums
Forum List  »  Newbie

Re: mysqlimport from a VBS
Posted by: Paul Grimes
Date: May 23, 2019 01:17AM

Hello Peter and thanks for getting back to me. When I mean "stopped working" I was meaning in the sense that the imports no longer work.
I have this VBS script, When a file called actions.bea8c868-19f8-4168-9972-8e0b555d35a4csv is received, this VBS script runs to import the data in the csv file into the table. Now this did and still does work on our previous server, but I cant for the life figure out why it will not import on this new server

On Error Resume Next

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim resGuid
resGuid = WScript.Arguments(0)
Dim fileName
fileName = "actions." & 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 8.0\bin\mysqlimport --local --user=root --password=Delgirl2015 new_db_2005 --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 8.0\bin\mysql -e ""SELECT count(*) FROM actions WHERE reportGuid = '" & resGuid & "'"" --user=root --password=Delgirl2015 new_db_2005"
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 "Import generated warnings, but report has been imported", resLine
Message "Warning"
archiveFolder = "Warning"
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 = "import_report@leymac.com"
oMessage.To = "pggrimes@hotmail.com"
oMessage.Subject = "Action Imported: " & 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 Server Address"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername";) = "Mail Server Username"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword";) = "Mail Server Password"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport";) = 2626
oMessage.Configuration.Fields.Update
oMessage.Send
End Sub

Public Sub Message(text)
'MsgBox text
End Sub

The contents of the CSV file is this.

"externalPK","customerPK","customerId","locationPK","locationId","userPK","userId","template","formDate","dateTimeStarted","dateTimeMobileReleased","currentStatus","reportGuid","snapshotTrigger","reportStatus","scoreUN","previousScoreUN","Actions","TravelToJob","JobStartTime","R019","R002","R004","R001","Job_No","LoggedBy","JobSheet","CustomerEmail","CustomerRef","R055","R056","ContactEmail","R005","R007","R009","R011","R013","R015","R017","R006","R008","R010","R012","R014","R016","R018","R438","MUsed","R051","R003","R176","R075","SigReason","NoJobSheet","R054","R053","JobEndTime","CRB_No","R4500","R4510","R4520","IPA100","R4501","R4511","R4521","IPB100","R4502","R4512","R4522","IPC100","R4503","R4513","R4523","IPD100","R4504","R4514","R4524","IPE100","R4505","R4515","R4525","IPF100","R4506","R4516","R4526","IPG100","R4507","R4517","R4527","IPH100","R4508","R4518","R4528","IPI100","R4509","R4519","R4529","IPJ100"
"1000002_6_28___","1162","163","482","486","6","7","167","2019-05-23T08:05:11","2019-05-23T08:05:11","2019-05-23T08:07:08","9","bea8c868-19f8-4168-9972-8e0b555d35a4","Actions:Job_Received","9","-1","-1","","","","FAULT ON ONE HEATING BOILER","TEST SCHOOL","TEST CLIENT","EH 6312","1000002","TEST ENGINEER","TEST JOBSHEET","","CARETAKER","TESTNAME","","","Yes","Yes","N/A","N/A","Yes","No","No","N/A","N/A","N/A","N/A","N/A","N/A","N/A","","No","","2019-05-23T00:00:00","","","","No","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""

As for the error log, this is all that it displays
2019-05-23T06:51:06.345560Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-05-23T06:51:06.349113Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.15) starting as process 4672
2019-05-23T06:51:26.749421Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-05-23T06:51:26.841568Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.15' socket: '' port: 3306 MySQL Community Server - GPL.
2019-05-23T06:51:26.884634Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

Regards
Paul

Options: ReplyQuote


Subject
Written By
Posted
May 22, 2019 04:38AM
Re: mysqlimport from a VBS
May 23, 2019 01:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.