Daten aus Excelliste in MySQL speichern

  • VB.NET

    Daten aus Excelliste in MySQL speichern

    Schönen guten Abend,

    ich suche eine Möglichkeit, eine Excelliste in einer MySQL Datenbank zu speichern. Ansich geht das mit dem unten ersichtlichen Code, aber das übergeben an die Datenbank dauert "sehr" lange (ca.9 Minuten für 16000 Datensätze)

    Kann man das ganze irgendwie "gesamt" an die Datenbank sende, damit es schneller geht? Derzeit gehe ich ja Zeile für zeile durch ;(

    Ich bin für jeden Tip sehr Dankbar :)

    VB.NET-Quellcode

    1. Dim dt As New DataTable
    2. Using cn As New System.Data.OleDb.OleDbConnection
    3. Dim Builder As New OleDbConnectionStringBuilder With _
    4. { _
    5. .DataSource = bereitschaft, _
    6. .Provider = "Microsoft.ACE.OLEDB.12.0" _
    7. }
    8. Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=Yes;")
    9. cn.ConnectionString = Builder.ConnectionString
    10. cn.Open()
    11. Dim SchemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    12. Dim tab As String = SchemaTable.Rows(0).Item(2).ToString
    13. tab = tab.Replace("'", "")
    14. Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
    15. cmd.CommandText = "SELECT * FROM [" & tab & "]"
    16. Dim dr As System.Data.IDataReader = cmd.ExecuteReader
    17. dt.Load(dr)
    18. DataGridView1.DataSource = dt
    19. End Using
    20. End Using
    21. Dim dt_fertig As New DataTable
    23. For a = 0 To DataGridView1.Columns.Count - 1
    24. dt_fertig.Columns.Add(DataGridView1.Columns(a).Name)
    25. Next
    27. For a = 0 To DataGridView1.Rows.Count - 2
    28. Dim plz1 As String = DataGridView1.Rows(a).Cells(1).Value.ToString
    29. If plz1.Length = 4 Then plz1 = "0" & plz1
    30. Dim plz2 As String = DataGridView1.Rows(a).Cells(2).Value.ToString
    31. If plz2.Length = 4 Then plz2 = "0" & plz2
    32. ' Dim dat As String = DataGridView1.Rows(a).Cells(7).Value.ToString
    34. dt_fertig.Rows.Add(DataGridView1.Rows(a).Cells(0).Value.ToString, _
    35. plz1, plz2, _
    36. DataGridView1.Rows(a).Cells(3).Value.ToString, _
    37. DataGridView1.Rows(a).Cells(4).Value.ToString, _
    38. DataGridView1.Rows(a).Cells(5).Value.ToString, _
    39. DataGridView1.Rows(a).Cells(6).Value.ToString, _
    40. DataGridView1.Rows(a).Cells(7).Value.ToString.Substring(0, 10), _
    41. DataGridView1.Rows(a).Cells(8).Value.ToString, _
    42. DataGridView1.Rows(a).Cells(9).Value.ToString)
    44. Next
    46. DataGridView2.DataSource = dt_fertig
    48. ListBox1.Items.Clear()
    49. ListBox2.Items.Clear()
    51. For a = 0 To DataGridView2.Rows.Count - 1
    52. If ListBox1.Items.Contains(DataGridView2.Rows(a).Cells(3).Value.ToString) Then
    54. Else
    55. ListBox1.Items.Add(DataGridView2.Rows(a).Cells(3).Value.ToString)
    57. End If
    59. Next
    61. '### hier werden die Daten in die MySQL Datenbank geschrieben
    63. Dim conn As New MySqlConnection()
    64. conn.ConnectionString = My.Settings.mysql
    65. Dim myAdapter As New MySqlDataAdapter
    67. For a = 0 To DataGridView2.Rows.Count - 1
    68. conn.Open()
    69. Dim gebiet As String = "'" & DataGridView2.Rows(a).Cells(0).Value.ToString & "'"
    70. Dim plzv As String = "'" & DataGridView2.Rows(a).Cells(1).Value.ToString & "'"
    71. Dim plzb As String = "'" & DataGridView2.Rows(a).Cells(2).Value.ToString & "'"
    72. Dim fbkt As String = "'" & DataGridView2.Rows(a).Cells(3).Value.ToString & "'"
    73. Dim tname As String = "'" & DataGridView2.Rows(a).Cells(4).Value.ToString & "'"
    74. Dim fregio As String = "'" & DataGridView2.Rows(a).Cells(5).Value.ToString & "'"
    75. Dim rname As String = "'" & DataGridView2.Rows(a).Cells(6).Value.ToString & "'"
    76. Dim datum As String = "'" & DataGridView2.Rows(a).Cells(7).Value.ToString & "'"
    77. Dim tag As String = "'" & DataGridView2.Rows(a).Cells(8).Value.ToString & "'"
    78. Dim prüfdatum As String = "'" & DataGridView2.Rows(a).Cells(9).Value.ToString & "'"
    80. gebiet = gebiet (vbLf, "")
    81. plzv = plzv .Replace(vbLf, "")
    82. plzb = plzb.Replace(vbLf, "")
    83. fbkt = fbk.Replace(vbLf, "")
    84. fregio = fregio.Replace(vbLf, "")
    85. rname = rname.Replace(vbLf, "")
    86. datum = datum.Replace(vbLf, "")
    87. tag = tag.Replace(vbLf, "")
    88. prüfdatum = prüfdatum.Replace(vbLf, "")
    89. tname = tname.Replace(vbLf, "")
    91. Dim sqlquery = "INSERT INTO `clst`(`gebiet`, `plzv`, `plzb`, `fbkt`, `tname`, `fregio`, `rname`, `datum`, `tag`, `prüfdatum`) VALUES (" & _
    92. gebiet & "," & plzv & "," & plzb & "," & fbkt & "," & tname & "," & fregio & "," & rname & "," & datum & "," & tag & "," & prüfdatum & ")"
    93. Dim myCommand As New MySqlCommand()
    94. myCommand.Connection = conn
    95. myCommand.CommandText = sqlquery
    96. myAdapter.SelectCommand = myCommand
    97. Dim myData As MySqlDataReader
    98. myData = myCommand.ExecuteReader()
    99. conn.Close()
    102. Next a

Facebook
Telefonspende