1. SQL Sever创建数据库及表
USE AuditViwer
CREATE TABLE AuditViwer
(
ID int primary key IDENTITY(1,1) not null,
RecordID Varchar(10), --ID
TimeStamp Datetime, --时间戳
DeltaToUTC Varchar(10), --
UserID Varchar(300), --
Description Varchar(300), --
Comment Varchar(300),
Checksum Varchar(20))
2. Audit 素材内容
触摸屏导出文件即可 Audit_log0_20240819_010001_HMI_Panel.txt
3.执行代码
''''''''''''''''''''''''''''''
Dim Fso, Folder, Files, File, FileCount, FileName, i
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Folder = Fso.GetFolder("C:\Users\Administrator\Desktop\Audit")
Set Files = Folder.Files
FileCount = Files.Count
ReDim FileName(FileCount)
For Each File In Files
LongLocalFileSize = File.Size
Msgbox File.Name '
FileName(i) = File.Name
i = i + 1
Next
'''''''''''''''''''''''''''''''
Dim f, Path
Path = "C:\Users\Administrator\Desktop\Audit\" & FileName(0)
'WScript.Echo Path
Set f = fso.OpenTextFile(path, 1, False, - 1)
Dim text
text = f.ReadAll
WScript.Echo text
f.Close
Dim Arr1, arr2
Arr1 = Split(text, vbCrLf)
ReDim arr2(UBound(Arr1) - 2, 8)
'''''''''''''''''''''''''''''''
For i = 1 To UBound(Arr1) - 2
arr2(i, 0) = Split(Arr1(i), vbTab)(0)
arr2(i, 1) = Replace(Split(Arr1(i), vbTab)(1), """", "")
arr2(i, 2) = Replace(Split(Arr1(i), vbTab)(2), """", "")
arr2(i, 3) = Replace(Split(Arr1(i), vbTab)(3), """", "")
arr2(i, 4) = Replace(Replace(Split(Arr1(i), vbTab)(4), """", ""), "'", " ")
arr2(i, 5) = Replace(Replace(Split(Arr1(i), vbTab)(5), """", ""), "'", " ")
arr2(i, 6) = Replace(Split(Arr1(i), vbTab)(6), """", "")
arr2(i, 7) = Split(Arr1(i), vbTab)(7)
Next
'''''''''''''''''''''''''''''''
Dim Value, VText(100)
Dim K1, K2, K3, Y1, Y2
For i = 1 To UBound(Arr1) - 2
ReDim Value(UBound(Arr1) - 2)
K1 = i \ 999
K2 = i Mod 999
K3 = K1 * 999 + K2
Value(K1) = "('" & arr2(K3, 0) & "','" & arr2(K3, 1) & "','" & arr2(K3, 2) & "','" & arr2(K3, 3) & "','" & arr2(K3, 4) & "', '" & arr2(K3, 5) & "','" & arr2(K3, 6) & "','" & arr2(K3, 7) & "'),"
VText(K1) = VText(K1) & Value(k1)
Next
'''''''''''''''''''''''''''''''
Dim Insert
Insert = "Insert into AuditViewer(RecordID,TimeStamp,DeltaToUTC,UserID,ObjectID,Description,Comment,Checksum)Values"
'''''''''''''''''''''''''''''''
Dim SQL
For i = 0 To 99
If VText(i) <> "" Then
SQL = Mid(Insert & VText(i), 1, Len(Insert & vtext(i)) - 1)
End If
Next
'''''''''''''''''''''''''''''''
Dim Datebases
Datebases = "AuditViewer"
Dim PCName
PCName = "DESKTOP-CVJ1487\WINCC"
Dim scon, conn
sCon = "Driver={SQL Server};Server=" & PCName & ";database=" & Datebases & ";UID=;PWD=;"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
Dim ocom
'Set oRs1 = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1
Set oCom.ActiveConnection = conn
oCom.CommandText = SQL
oCom.Execute
' Set oRs1 = Nothing
conn.Close
Set conn = Nothing