Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Sql import to Excel tool

The steps of this VBA macro are as follows:

1. Select the SQL file via the file dialog.
2. Read the contents of a file.
3. Parse each line in the file and if it contains "insert into", extract the table name.
4. Checks if a worksheet with the same name as tablename already exists, and creates a new worksheet if it does not.
5. Insert the data into the corresponding worksheet.

Tip: Because issues such as upper and lower case in SQL text are actually quite complicated, use this example with caution.
Some unexpected situations, such as fields containing some) values The main problem is positioning, and then the length of the value.,Commas, and then spaces and other issues;
It should be easier to handle using python;
the followingVBAThe script is for reference only; you can bind the button yourself;

For this type of:

insert into aaa (aa,bb,cc) values ('2','','3aa');
insert into aaa (aa,bb,cc) values ('1',null,'');
1
2

' +++++++++++++++++++++++++++++++++++++++++++++++++++
' author Mr.qyb_y
' Version 1.0.0
' Date 2024-07-09 21:10
' +++++++++++++++++++++++++++++++++++++++++++++++++++
Sub ImportSQLToExcel()
    Dim fd As FileDialog
    Dim filePath As String
    Dim fileContent As String
    Dim lines As Variant
    Dim line As Variant
    Dim sht As Worksheet
    Dim currentSheetIndex As Integer
    
    ' 创建文件对话框以选择SQL文件
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select SQL File"
    fd.Filters.Add "SQL Files", "*.sql", 1
    
    If fd.Show = -1 Then
        filePath = fd.SelectedItems(1)
    Else
        MsgBox "No file selected.", vbExclamation
        Exit Sub
    End If
    
    ' 读取文件内容
    fileContent = ReadFileContent(filePath)
    lines = Split(fileContent, vbCrLf)
    
    currentSheetIndex = Sheets.Count
    
    ' 解析文件内容并插入到Excel中
    For Each line In lines
        If InStr(line, "insert into") > 0 Then
            Dim tableName As String
            Dim columnNames As String
            tableName = ExtractTableName(CStr(line)) ' 强制转换为字符串类型
            columnNames = ExtractColumnNames(CStr(line)) ' 提取列名
            
            ' 检查工作表是否已经存在
            On Error Resume Next
            Set sht = Sheets(tableName)
            On Error GoTo 0
            
            ' 如果工作表不存在，则创建新的工作表，并插入列名
            If sht Is Nothing Then
                Set sht = Sheets.Add(After:=Sheets(currentSheetIndex))
                sht.Name = tableName
                currentSheetIndex = currentSheetIndex + 1
                
                ' 插入列名
                InsertColumnNames sht, columnNames
            End If
            
            ' 插入数据
            InsertDataIntoSheet sht, CStr(line) ' 强制转换为字符串类型
        End If
    Next line
    
    MsgBox "Data imported successfully!", vbInformation
End Sub

Function ReadFileContent(filePath As String) As String
    Dim fileNumber As Integer
    Dim content As String
    fileNumber = FreeFile
    
    Open filePath For Input As fileNumber
    content = Input(LOF(fileNumber), fileNumber)
    Close fileNumber
    
    ReadFileContent = content
End Function

Function ExtractTableName(ByVal sqlLine As String) As String ' 明确指定参数类型
    Dim startPos As Integer
    Dim endPos As Integer
    startPos = InStr(sqlLine, "insert into") + Len("insert into ")
    endPos = InStr(startPos, sqlLine, " (")
    ExtractTableName = Trim(Mid(sqlLine, startPos, endPos - startPos))
End Function

Function ExtractColumnNames(ByVal sqlLine As String) As String
    Dim startPos As Integer
    Dim endPos As Integer
    startPos = InStr(sqlLine, "(") + 1
    endPos = InStr(sqlLine, ") values")
    ExtractColumnNames = Trim(Mid(sqlLine, startPos, endPos - startPos))
End Function

Sub InsertColumnNames(sht As Worksheet, columnNames As String)
    Dim columns As Variant
    columns = Split(columnNames, ",")
    
    With sht
        Dim i As Integer
        For i = LBound(columns) To UBound(columns)
            .Cells(1, i + 1).Value = Trim(columns(i))
        Next i
    End With
End Sub

Sub InsertDataIntoSheet(sht As Worksheet, ByVal sqlLine As String) ' 明确指定参数类型
    Dim valuesStartPos As Integer
    Dim valuesEndPos As Integer
    Dim values As String
    Dim data As Variant
    
    valuesStartPos = InStr(sqlLine, "values (") + Len("values (")
    valuesEndPos = InStr(valuesStartPos, sqlLine, ");")
    values = Mid(sqlLine, valuesStartPos, valuesEndPos - valuesStartPos)
    
    data = Split(values, ",")
    
    ' 去掉单引号并插入数据到工作表中
    With sht
        Dim nextRow As Long
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        
        Dim i As Integer
        For i = LBound(data) To UBound(data)
            .Cells(nextRow, i + 1).Value = Replace(Trim(data(i)), "'", "")
        Next i
    End With
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

🍀
good night
peace
come on