Sql 导入到 Excel 工具
这个VBA宏的步骤如下：
- 通过文件对话框选择SQL文件。
- 读取文件内容。
- 解析文件中的每一行，如果包含“insert into”，则提取表名。
- 检查是否已经存在以表名命名的工作表，如果不存在则创建新的工作表。
- 将数据插入到相应的工作表中。
Tip：因为 sql 文本 大小写等问题实际比较复杂，所以本例谨慎使用。
一些意外的情况，比如字段包含一些 ) values 之类的，主要是定位问题，再就是值的长度，万一值里面也有,逗号，再就是空格等问题；
用python应该会好处理些；
以下VBA脚本经供参考；可以自行绑定按钮；
针对这样式的：
insert into aaa (aa,bb,cc) values ('2','','3aa');
insert into aaa (aa,bb,cc) values ('1',null,'');
' +++++++++++++++++++++++++++++++++++++++++++++++++++
' author Mr.qyb_y
' Version 1.0.0
' Date 2024-07-09 21:10
' +++++++++++++++++++++++++++++++++++++++++++++++++++
Sub ImportSQLToExcel()Dim fd As FileDialogDim filePath As StringDim fileContent As StringDim lines As VariantDim line As VariantDim sht As WorksheetDim currentSheetIndex As Integer' 创建文件对话框以选择SQL文件Set fd = Application.FileDialog(msoFileDialogFilePicker)fd.Title = "Select SQL File"fd.Filters.Add "SQL Files", "*.sql", 1If fd.Show = -1 ThenfilePath = fd.SelectedItems(1)ElseMsgBox "No file selected.", vbExclamationExit SubEnd If' 读取文件内容fileContent = ReadFileContent(filePath)lines = Split(fileContent, vbCrLf)currentSheetIndex = Sheets.Count' 解析文件内容并插入到Excel中For Each line In linesIf InStr(line, "insert into") > 0 ThenDim tableName As StringDim columnNames As StringtableName = ExtractTableName(CStr(line)) ' 强制转换为字符串类型columnNames = ExtractColumnNames(CStr(line)) ' 提取列名' 检查工作表是否已经存在On Error Resume NextSet sht = Sheets(tableName)On Error GoTo 0' 如果工作表不存在，则创建新的工作表，并插入列名If sht Is Nothing ThenSet sht = Sheets.Add(After:=Sheets(currentSheetIndex))sht.Name = tableNamecurrentSheetIndex = currentSheetIndex + 1' 插入列名InsertColumnNames sht, columnNamesEnd If' 插入数据InsertDataIntoSheet sht, CStr(line) ' 强制转换为字符串类型End IfNext lineMsgBox "Data imported successfully!", vbInformation
End SubFunction ReadFileContent(filePath As String) As StringDim fileNumber As IntegerDim content As StringfileNumber = FreeFileOpen filePath For Input As fileNumbercontent = Input(LOF(fileNumber), fileNumber)Close fileNumberReadFileContent = content
End FunctionFunction ExtractTableName(ByVal sqlLine As String) As String ' 明确指定参数类型Dim startPos As IntegerDim endPos As IntegerstartPos = InStr(sqlLine, "insert into") + Len("insert into ")endPos = InStr(startPos, sqlLine, " (")ExtractTableName = Trim(Mid(sqlLine, startPos, endPos - startPos))
End FunctionFunction ExtractColumnNames(ByVal sqlLine As String) As StringDim startPos As IntegerDim endPos As IntegerstartPos = InStr(sqlLine, "(") + 1endPos = InStr(sqlLine, ") values")ExtractColumnNames = Trim(Mid(sqlLine, startPos, endPos - startPos))
End FunctionSub InsertColumnNames(sht As Worksheet, columnNames As String)Dim columns As Variantcolumns = Split(columnNames, ",")With shtDim i As IntegerFor i = LBound(columns) To UBound(columns).Cells(1, i + 1).Value = Trim(columns(i))Next iEnd With
End SubSub InsertDataIntoSheet(sht As Worksheet, ByVal sqlLine As String) ' 明确指定参数类型Dim valuesStartPos As IntegerDim valuesEndPos As IntegerDim values As StringDim data As VariantvaluesStartPos = InStr(sqlLine, "values (") + Len("values (")valuesEndPos = InStr(valuesStartPos, sqlLine, ");")values = Mid(sqlLine, valuesStartPos, valuesEndPos - valuesStartPos)data = Split(values, ",")' 去掉单引号并插入数据到工作表中With shtDim nextRow As LongnextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1Dim i As IntegerFor i = LBound(data) To UBound(data).Cells(nextRow, i + 1).Value = Replace(Trim(data(i)), "'", "")Next iEnd With
End Sub🍀
晚安咯
peace
加油