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
加油
