How to Import Excel File in MySQL Database Using VB.Net

Submitted by janobe on Saturday, March 2, 2019 - 16:16.

This time, I will teach you how to import excel file in mysql database using vb.net. This powerful method has the ability to import all the data from excel file to mysql database. This is very helpful if you have to transfer the list of records in the excel file to your database. In this way, you don’t have to encode one by one the record in the database.

Creating Database

Create a database named “persondb” Execute the following query to create a table in the database

CREATE TABLE `tblperson` (
  `PersonID` int(11) NOT NULL,
  `FNAME` varchar(90) NOT NULL,
  `LNAME` varchar(90) NOT NULL,
  `ADDRESS` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
<h3>

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for visual basic. ps1

Step 2

Do the form just like shown below. ps2

Step 3

Open the code editor and write the following code above a public class to access MySQL Libraries.

Imports MySql.Data.MySqlClient

Step 4

Create a private function for saving data in the MySQL Database .

Private Function saveData(sql As String)
        Dim mysqlCOn As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=;database=dbpeople;sslMode=none")
        Dim mysqlCmd As MySqlCommand
        Dim resul As Boolean
 
        Try
 
            mysqlCOn.Open()
            mysqlCmd = New MySqlCommand
            With mysqlCmd
                .Connection = mysqlCOn
                .CommandText = sql
                resul = .ExecuteNonQuery()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            mysqlCOn.Close()
        End Try
        Return resul
    End Function

Step 5

Double click the “Browse” button and do the following codes for getting the excel file in the directory.

With OpenFileDialog1
            .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Import data from Excel file"
        End With
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            txtLocation.Text = OpenFileDialog1.FileName
        End If

Step 6

Double click the Timer and write the following codes for the function of progress bar.

If pg_load.Value = 100 Then
            Timer1.Stop()
            MsgBox("Success")
            pg_load.Value = 0
        Else
            pg_load.Value += 1
        End If

Step 7

Double click the “Upload” button and do the following codes to import the excel file into the MySQL Database.

   	Dim OLEcon As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & txtLocation.Text & " ; " & "Extended Properties=Excel 8.0;")
        Dim OLEcmd As New OleDb.OleDbCommand
        Dim OLEda As New OleDb.OleDbDataAdapter
        Dim OLEdt As New DataTable
        Dim sql As String
        Dim resul As Boolean
 
        Try
            OLEcon.Open()
            With OLEcmd
                .Connection = OLEcon
                .CommandText = "select * from [Sheet1$]"
            End With
            OLEda.SelectCommand = OLEcmd
            OLEda.Fill(OLEdt)
 
            For Each r As DataRow In OLEdt.Rows
 
                sql = "INSERT INTO tblperson (FNAME,LNAME,ADDRESS) VALUES ('" & r(0).ToString & "','" & r(1).ToString & "','" & r(2).ToString & "')"
                resul = saveData(sql)
                If resul Then
                    Timer1.Start()
                End If
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            OLEcon.Close()
        End Try

Press F5 to run your project. The sourcecode is include you can download it and run it on your computer For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Comments

Saving excel file to database

Hallo... I have a form with save button and upload button but I click on upload button to insert imported excel file to database nothing happens. I have used your code.

Add new comment

Add new comment
5801 views