SIEMENS TP1200审计追踪导入SQL Sever数据库中

原创已于 2024-10-28 21:46:46 修改 · 953 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

于 2024-09-03 23:32:28 首次发布

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