Autoimport CSV Files
Posted by: Paul Grimes
Date: July 21, 2019 01:38AM

Hello all.
I have this line in a VBS file, it will take a CSV and import it into a table called actions, but since moving to a new server where the MySQL is remote from I gather I need to change this VBS to use the mySQLImport command.
The MySQL server is on an IP of 192.168.0.4 Hostname is MYSQL and the CSV files and scripts are on 192.168.0.5 Hostname is MSSQL
Any ideas please?
Cheers

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:\wamp64\bin\mysql\mysql5.7.26\bin\mysqlimport.exe --local --user=root --password=MySQL_PASSWORD actions --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:\wamp64\bin\mysql\mysql5.7.26\bin\mysql -e ""SELECT count(*) FROM actions WHERE reportGuid = '" & resGuid & "'"" --user=root --password=MySQL_PASSWORD DATABASE_NAME"
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 = "ENTER EMAIL ADRRESS"
oMessage.To = "ENTER EMAIL ADDRESS"
oMessage.Subject = "ENTER SUBJECT LINE: " & 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";) = "ENTER MAIL SERVER"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername";) = "ENTER USERNAME"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword";) = "ENTER PASSWORD"
oMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport";) = ENTER SMTP SERVER PORT
oMessage.Configuration.Fields.Update
oMessage.Send
End Sub

Public Sub Message(text)
'MsgBox text
End Sub

Options: ReplyQuote


Subject
Written By
Posted
Autoimport CSV Files
July 21, 2019 01:38AM


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.