Python拆分字符串填充Excel列：提取DROP TABLE表名填充指定列

Got it, let's work through this problem together. You need to grab the table name from the DROP TABLE statement (where StepID=1) and fill the new TEST_TABLE_NM column for all rows tied to the same TABLE_ID, right? Here are two solid approaches to get this done in Excel:

This is perfect if you prefer a point-and-click solution without macros. Let's assume your columns are set up like this:

• Column A: TABLE_ID
• Column C: STEP_ID
• Column D: SQL_STRING
• Column E: New TEST_TABLE_NM column

For Excel 365/2021 (Modern Versions) #

Use the XLOOKUP, TEXTBEFORE, and TEXTAFTER functions for clean, readable code:

=TRIM(TEXTAFTER(TEXTBEFORE(XLOOKUP(1, (C:C=1)*(A:A=@A:A), D:D), ";"), "DROP TABLE "))

Breakdown:

1. XLOOKUP finds the SQL_STRING for the current row's TABLE_ID where STEP_ID=1
2. TEXTBEFORE strips off any trailing semicolon from the SQL statement
3. TEXTAFTER pulls out everything after "DROP TABLE "
4. TRIM removes any extra spaces around the table name

For Older Excel Versions (Pre-365) #

If you don't have the newer text functions, use INDEX + MATCH with string manipulation:

=TRIM(MID(INDEX(D:D, MATCH(1, (C:C=1)*(A:A=@A:A), 0)), FIND("DROP TABLE ", INDEX(D:D, MATCH(1, (C:C=1)*(A:A=@A:A), 0)))+11, LEN(INDEX(D:D, MATCH(1, (C:C=1)*(A:A=@A:A), 0)))-FIND("DROP TABLE ", INDEX(D:D, MATCH(1, (C:C=1)*(A:A=@A:A), 0)))-IF(RIGHT(INDEX(D:D, MATCH(1, (C:C=1)*(A:A=@A:A), 0)),1)=";",1,0)))

Note: For Excel 2019 or earlier, you'll need to enter this as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.

If you have a huge dataset or need to handle minor variations in SQL formatting (like lowercase "drop table"), a macro is more efficient. Here's a ready-to-use script:

Sub PopulateTestTableName()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim tableIDMap As Object
    Dim i As Long
    Dim sqlText As String
    Dim extractedTableName As String
    
    ' Update this to your worksheet name
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Use a dictionary to store TABLE_ID -> Table Name pairs
    Set tableIDMap = CreateObject("Scripting.Dictionary")
    
    ' First pass: collect all table names from StepID=1 rows
    For i = 2 To lastRow ' Skip row 1 if it's a header
        If ws.Cells(i, "C").Value = 1 Then
            sqlText = ws.Cells(i, "D").Value
            ' Find the position of "DROP TABLE" (case-insensitive)
            Dim dropPos As Integer
            dropPos = InStr(1, sqlText, "DROP TABLE ", vbTextCompare)
            
            If dropPos > 0 Then
                extractedTableName = Trim(Mid(sqlText, dropPos + 11))
                ' Remove trailing semicolon if present
                If Right(extractedTableName, 1) = ";" Then
                    extractedTableName = Left(extractedTableName, Len(extractedTableName) - 1)
                End If
                ' Add to the map
                tableIDMap(ws.Cells(i, "A").Value) = Trim(extractedTableName)
            End If
        End If
    Next i
    
    ' Second pass: fill the TEST_TABLE_NM column (Column E)
    For i = 2 To lastRow
        If tableIDMap.Exists(ws.Cells(i, "A").Value) Then
            ws.Cells(i, "E").Value = tableIDMap(ws.Cells(i, "A").Value)
        End If
    Next i
    
    MsgBox "TEST_TABLE_NM column filled successfully!", vbInformation
End Sub

How to use this:

1. Press Alt+F11 to open the VBA editor
2. Insert a new module (Right-click your workbook > Insert > Module)
3. Paste the code above
4. Update YourSheetName to match your actual worksheet name
5. Run the macro (Press F5 or use the Run button)

• If your SQL uses lowercase drop table, the formula method will need SEARCH instead of FIND (since SEARCH is case-insensitive). For example, replace FIND("DROP TABLE ", ...) with SEARCH("DROP TABLE ", ...)
• If table names include spaces (not ideal, but possible), both methods will still work because TRIM cleans up extra spaces

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