基于VBA实现消防报警系统文本文件转SQL查询的技术求助

Hey there! Let's walk through solving this parsing challenge for your fire alarm system prep. As someone who's built similar text-to-SQL automation tools, here's a practical, step-by-step approach tailored to your scenario:

Step 1: Read the Text File into VBA #

First, we need to load the entire text file content and split it into individual lines for processing. Here's a simple way to do that:

Sub ParseFirePanelConfig()
    Dim filePath As String
    Dim fileContent As String
    Dim lines() As String
    Dim line As Variant
    
    ' Update this path to your actual text file location
    filePath = "C:\FirePanel\Config.txt"
    
    ' Read the full file content
    Open filePath For Input As #1
    fileContent = Input$(LOF(1), 1)
    Close #1
    
    ' Split content into separate lines (adjust delimiter if your file uses vbLf instead of vbCrLf)
    lines = Split(fileContent, vbCrLf)
    
    ' Loop through each line to parse
    For Each line In lines
        If Trim(line) <> "" Then ' Skip empty lines
            ParseSingleLine line
        End If
    Next line
End Sub

Step 2: Extract Rule Name (Bracket Content) #

Each line has a rule name wrapped in brackets. We'll use string functions to pull that out:

Sub ParseSingleLine(lineText As String)
    Dim ruleName As String
    Dim openBracketPos As Integer, closeBracketPos As Integer
    
    ' Find positions of opening and closing brackets
    openBracketPos = InStr(lineText, "[")
    closeBracketPos = InStr(lineText, "]")
    
    ' Extract rule name if brackets exist and are in order
    If openBracketPos > 0 And closeBracketPos > openBracketPos Then
        ruleName = Trim(Mid(lineText, openBracketPos + 1, closeBracketPos - openBracketPos - 1))
    Else
        ' Handle lines with missing brackets (skip or log error)
        Debug.Print "Skipping invalid line: " & lineText
        Exit Sub
    End If
    
    ' Next: Parse input/output pairs
    ParseInputOutput ruleName, lineText
End Sub

Step 3: Parse Input/Output Pairs (Colon-Separated) #

Now we'll split the part after the colon into individual input-output pairs, then extract each key-value set:

Sub ParseInputOutput(ruleName As String, lineText As String)
    Dim colonPos As Integer
    Dim inputOutputSection As String
    Dim kvPairs() As String
    Dim kvPair As Variant
    Dim equalPos As Integer
    Dim inputItem As String, outputItem As String
    Dim sqlStmt As String
    
    ' Find the colon separating rule name from input/output data
    colonPos = InStr(lineText, ":")
    If colonPos = 0 Then
        Debug.Print "No colon found in line: " & lineText
        Exit Sub
    End If
    
    ' Get the part after the colon, trim extra spaces
    inputOutputSection = Trim(Mid(lineText, colonPos + 1))
    
    ' Split into individual key-value pairs (adjust delimiter if your file uses something else, like semicolons)
    kvPairs = Split(inputOutputSection, ",")
    
    ' Loop through each pair to extract input and output
    For Each kvPair In kvPairs
        kvPair = Trim(kvPair)
        equalPos = InStr(kvPair, "=")
        
        If equalPos > 0 Then
            inputItem = Trim(Mid(kvPair, 1, equalPos - 1))
            outputItem = Trim(Mid(kvPair, equalPos + 1))
            
            ' Generate SQL statement (customize table/column names to match your database)
            sqlStmt = "INSERT INTO FireAlarmRules (RuleName, InputItem, OutputItem) VALUES ('" & _
                      Replace(ruleName, "'", "''") & "', '" & _
                      Replace(inputItem, "'", "''") & "', '" & _
                      Replace(outputItem, "'", "''") & "');"
            
            ' Print SQL to Immediate Window for testing, or execute it directly
            Debug.Print sqlStmt
            
            ' To execute the SQL, use ADODB connection (example below)
            ' ExecuteSQL sqlStmt
        Else
            Debug.Print "Invalid key-value pair: " & kvPair
        End If
    Next kvPair
End Sub

Step 4: Optional - Execute SQL Directly #

If you want to run the generated SQL against your database, add this helper function (you'll need to reference the ADODB library):

Sub ExecuteSQL(sqlStmt As String)
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Update connection string to match your database (Access, SQL Server, etc.)
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\FirePanel\AlarmDB.accdb;"
    
    On Error Resume Next
    conn.Execute sqlStmt
    If Err.Number <> 0 Then
        Debug.Print "SQL Error: " & Err.Description & " | Statement: " & sqlStmt
    End If
    On Error GoTo 0
    
    conn.Close
    Set conn = Nothing
End Sub

Key Notes for Your Use Case #

• Adjust Delimiters: If your text file uses different separators (e.g., ; instead of , for pairs), update the Split function arguments.
• Error Handling: Add more checks for edge cases (like missing equal signs, malformed brackets) based on your actual file structure.
• SQL Injection Protection: The Replace function escapes single quotes to prevent issues with text containing apostrophes.

If you can share a sample line from your text file, I can refine this code to match your exact format!

内容的提问来源于stack exchange，提问作者Robert Sullivan