Importing Data from excel to Mysql using VB.net

[jbutardo]

Hi,

I want to know what code should be used in order for me to import data from an excel file to dataset and then adding it to the database, how can it be done? thanks

[Reverend Jim]

What is your level of expertise? Do you know anything about programming? Have you taken any training? What have you done so far? Is the Excel file in a format that supports ADO or some other level of abstraction? What kind of database? If you want answers you have to provide some information.

[waqasaslammmeo]

hello !
u have to import ur data from excel to access then u can put all ur data in dataset for populating ur grid then save it in MSSQL ,
but first u have to import ur excel data in access

Regards
M.Waqas Aslam

[Reverend Jim]

That is most definitely NOT the case. If the Excel spreadsheet is organized as a proper database table (regular rows and columns) then the spreadsheet can be opened as an ADO data source and records can be retrieved just like any other actual database using a connection object and a recordset. If it is not then the data can be read by creating an Excel application object and reading the data cell by cell, then added to the database record by record. In any case, the OP is using MySql and there is no reason to involve Access.

[waqasaslammmeo]

That is most definitely NOT the case. If the Excel spreadsheet is organized as a proper database table (regular rows and columns) then the spreadsheet can be opened as an ADO data source and records can be retrieved just like any other actual database using a connection object and a recordset. If it is not then the data can be read by creating an Excel application object and reading the data cell by cell, then added to the database record by record. In any case, the OP is using MySql and there is no reason to involve Access.

here is the link how to import excel file to access db http://office.microsoft.com/en-us/excel-help/import-excel-data-to-an-access-database-HP005200852.aspx

[waqasaslammmeo]

That is most definitely NOT the case. If the Excel spreadsheet is organized as a proper database table (regular rows and columns) then the spreadsheet can be opened as an ADO data source and records can be retrieved just like any other actual database using a connection object and a recordset. If it is not then the data can be read by creating an Excel application object and reading the data cell by cell, then added to the database record by record. In any case, the OP is using MySql and there is no reason to involve Access.

http://office.microsoft.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx

here is another one

[Reverend Jim]

to waqasaslammmeo

Can you please explain to me how Importing Data from excel to Mysql using VB.net can be interpreted as "I want to import Excel to an AccessDB?". I'm not trying to be difficult or argumentative but if I can misunderstand such a simple request than I obviously have no command of the English language whatsoever.

I'd also like to remind you of the forum rules/guidelines. To witDo not write in all uppercase or use "leet", "txt" or "chatroom" speak Typing "u", "ur", etc makes it more difficult for non-English (as a first language) forum members to follow conversations.

[Reverend Jim]

Here is an example of using ADO to import records from Excel. In my example, the data on Sheet1 looks like

lname   fname   age   gender
Smith   John    32    M
Doe     Jane    41    F

Imports ADODB

Public Class Form1

    Private Sub btnFetch_Click(sender As System.Object, e As System.EventArgs) Handles btnFetch.Click

        Dim conn As New Connection
        Dim rset As New Recordset
        Dim buff As String

        conn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\temp\test.xls;"
        conn.Open()

        rset.Open("select * from [Sheet1$]", conn, CursorTypeEnum.adOpenForwardOnly)

        Do Until rset.EOF
            buff = rset("lname").Value & " " & rset("fname").Value & " " & rset("age").Value & " " & rset("gender").Value
            txtExcel.AppendText(buff & vbCrLf)
            rset.MoveNext()
        Loop

        rset.Close()
        conn.Close()

    End Sub

End Class

In this case, the first row is expected to contain the column headers (field names). If there are no column headers than the parameter ";HDR=No" must be included in the connection string.

Note that it is not necessary for any AccessDB components to be used (or even present on the user's computer).