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

Recommended Answers

to waqasaslammmeo

Can you please explain to me how [quote]Importing Data from excel to Mysql using VB.net[/quote] 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 …

Jump to Post

Here is an example of using ADO to import records from Excel. In my example, the data on Sheet1 looks like
[code]
lname fname age gender
Smith John 32 M
Doe Jane 41 F
[/code]
[code]
Imports ADODB

Public Class Form1

Private Sub btnFetch_Click(sender As System.Object, e As System.EventArgs) Handles …
Jump to Post

All 8 Replies

Re: Importing Data from excel to Mysql using VB.net 80 80

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.

Re: Importing Data from excel to Mysql using VB.net 80 80

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

commented: Not details and introduces an unnecessary complication (Access). -2
Re: Importing Data from excel to Mysql using VB.net 80 80

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.

commented: check the link below -1
commented: The request was to use ADO and not Access, you are right +9
Re: Importing Data from excel to Mysql using VB.net 80 80

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

Re: Importing Data from excel to Mysql using VB.net 80 80

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

Re: Importing Data from excel to Mysql using VB.net 80 80

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 wit

Do 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.

Re: Importing Data from excel to Mysql using VB.net 80 80

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).

Re: Importing Data from excel to Mysql using VB.net 80 80

Hello, I have a question for a particular case.

My excel table has a letterhead, and the data are really important after row 13. How I would skip this letterhead and import only data?

Sorry for the English, this is google translator.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.