將某些表從mysql服務器下載到特定位置作為csv檔案

1. 連接到mysql資料庫。

2. 應該給用戶輸入從資料庫下載哪個表。

3. 所選表應下載到特定位置并另存為 csv 檔案。注意 csv 名稱應該是 tablename.csv。

   Sub connect()
    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement
   
    Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
    Server_Name = "localhost"
    Database_Name = "testdb" ' Name of database
    User = "root" 'id user or username
    Password = "zxcasdQWE123" 'Password
   
    SQLStr = "SELECT * FROM vector"
   
    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";User=" & User & ";Password=" & Password & "; Option=3;"
   
    rs.Open SQLStr, Cn, adOpenStatic
   
    Dim myArray()
    Dim ostream As Object
   
    myArray = rs.GetRows()
   
    kolumner = UBound(myArray, 1)
    rader = UBound(myArray, 2)
   
    Set ostream = CreateObject("ADODB.Stream")
    ostream.Open 
    'ostream.WriteText "hi, hello" & vbNewLine & "how, are" ' test input. not for any use
    ostream.SaveToFile ("C:\Users\asus\Downloads\vector.csv")
    ostream.Close
   
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
   End Sub
   

我已經連接到我的資料庫并為例如選擇了一個表并將其放入陣列。現在我對如何將該表下載為 csv 感到震驚。并讓用戶輸入要從資料庫中選擇的表。任何人都可以幫助我。

uj5u.com熱心網友回復：

創建一個新作業簿，使用 CopyFromRecordset 將記錄復制到作業表，然后另存為 CSV。

Option Explicit

Sub CreateCSV()

    ' logon credentials
    Const Server_Name = "localhost"
    Const Database_Name = "testdb" ' Name of database
    Const User = "root" 'id user or username
    Const Password = "zxcasdQWE123" 'Password
     
    ' connect
    Dim Cn As Object, sCn As String, rs As Object
    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver}" & _
            ";Server=" & Server_Name & ";Database=" & Database_Name & _
            ";User=" & User & ";Password=" & Password & "; Option=3;"
    
    ' get list of tables
    Dim arTbl, n As Long, sTbl As String, msg As String, u As Variant
    Set rs = Cn.Execute("SHOW TABLES")
    arTbl = rs.getrows()
    
    For n = 1 To UBound(arTbl, 2)
        msg = msg & n & ") " & arTbl(0, n - 1) & vbLf
    Next
    u = InputBox(msg, "Select Table")
    
    ' check user input is valid
    If IsNumeric(u) Then
       If u < 1 Or u > UBound(arTbl, 2) Then
            MsgBox u & " is an invalid entry !", vbExclamation
            Exit Sub
       End If
    Else
       MsgBox u & " is an invalid entry !", vbExclamation
       Exit Sub
    End If
    ' selected table
    sTbl = arTbl(0, u - 1)
    
    ' execute query
    Dim wbCSV As Workbook, wb As Workbook, filename As String
    Set rs = Cn.Execute("SELECT * FROM " & sTbl)
    
    ' create workbook, save as csv
    Set wb = ThisWorkbook
    Set wbCSV = Workbooks.Add(1)
    filename = wb.Path & "\" & sTbl & ".csv"
    
    With wbCSV.Sheets(1)
        ' header
        For n = 1 To rs.Fields.Count
           .Cells(1, n) = rs.Fields(n - 1).Name
        Next
        ' data
        .Range("A2").CopyFromRecordset rs
        n = .Cells(.Rows.Count, "A").End(xlUp).Row
        .SaveAs filename, xlCSV
        wbCSV.Close savechanges:=False
    End With

    MsgBox n - 1 & " rows exported to " & filename, vbInformation
       
End Sub

標籤：擅长 vba

上一篇：物件“應用程式”的方法“撤消”失敗，如何解決？

下一篇：訪問資料庫中的空單元格時如何填充