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
  1. CREATE TABLE `tblperson` (
  2.   `PersonID` int(11) NOT NULL,
  3.   `FNAME` varchar(90) NOT NULL,
  4.   `LNAME` varchar(90) NOT NULL,
  5.   `ADDRESS` varchar(255) NOT NULL
  7. <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.
  1. Imports MySql.Data.MySqlClient

Step 4

Create a private function for saving data in the MySQL Database .
  1. Private Function saveData(sql As String)
  2.         Dim mysqlCOn As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=;database=dbpeople;sslMode=none")
  3.         Dim mysqlCmd As MySqlCommand
  4.         Dim resul As Boolean
  5.  
  6.         Try
  7.  
  8.             mysqlCOn.Open()
  9.             mysqlCmd = New MySqlCommand
  10.             With mysqlCmd
  11.                 .Connection = mysqlCOn
  12.                 .CommandText = sql
  13.                 resul = .ExecuteNonQuery()
  14.             End With
  15.         Catch ex As Exception
  16.             MsgBox(ex.Message)
  17.         Finally
  18.             mysqlCOn.Close()
  19.         End Try
  20.         Return resul
  21.     End Function

Step 5

Double click the “Browse” button and do the following codes for getting the excel file in the directory.
  1. With OpenFileDialog1
  2.             .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
  3.             .FilterIndex = 1
  4.             .Title = "Import data from Excel file"
  5.         End With
  6.         If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
  7.             txtLocation.Text = OpenFileDialog1.FileName
  8.         End If

Step 6

Double click the Timer and write the following codes for the function of progress bar.
  1. If pg_load.Value = 100 Then
  2.             Timer1.Stop()
  3.             MsgBox("Success")
  4.             pg_load.Value = 0
  5.         Else
  6.             pg_load.Value += 1
  7.         End If

Step 7

Double click the “Upload” button and do the following codes to import the excel file into the MySQL Database.
  1.         Dim OLEcon As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & txtLocation.Text & " ; " & "Extended Properties=Excel 8.0;")
  2.         Dim OLEcmd As New OleDb.OleDbCommand
  3.         Dim OLEda As New OleDb.OleDbDataAdapter
  4.         Dim OLEdt As New DataTable
  5.         Dim sql As String
  6.         Dim resul As Boolean
  7.  
  8.         Try
  9.             OLEcon.Open()
  10.             With OLEcmd
  11.                 .Connection = OLEcon
  12.                 .CommandText = "select * from [Sheet1$]"
  13.             End With
  14.             OLEda.SelectCommand = OLEcmd
  15.             OLEda.Fill(OLEdt)
  16.  
  17.             For Each r As DataRow In OLEdt.Rows
  18.  
  19.                 sql = "INSERT INTO tblperson (FNAME,LNAME,ADDRESS) VALUES ('" & r(0).ToString & "','" & r(1).ToString & "','" & r(2).ToString & "')"
  20.                 resul = saveData(sql)
  21.                 If resul Then
  22.                     Timer1.Start()
  23.                 End If
  24.             Next
  25.         Catch ex As Exception
  26.             MessageBox.Show(ex.Message)
  27.         Finally
  28.             OLEcon.Close()
  29.         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.
Tags
Import excel in vb.net
vb.net
MySQL Database
importing excel file in vb.net

Comments

Submitted by Kennedy Omondi (not verified) on Tue, 09/20/2022 - 17:38

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

About text formats