Click here to Skip to main content
15,889,211 members
Please Sign up or sign in to vote.
4.33/5 (2 votes)
See more:
I'm using Microsoft Visual Studio 2005

How do I upload an excel file into Visual Basic that can display it's content?

then how do I upload it in sql?

something without oledb?
Posted
Updated 27-Jul-14 22:04pm
v2

Please, read this: Accessing Microsoft Office Data from .NET Applications[^] and this: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]. Now, you should know that all you need to do is to use ADO.NET.

Steps to do:
1) Create OleDbConnection[^] and connect to MS Excel file using proper ConnectionString[^].
2) Create OleDbCommand[^] and load data into OleDbDataReader[^]
3) Create SqlConnection[^] and connect to the SQL Server instance using proper ConnectionString[^]
4) Use SqlBulkCopy[^] class to write data (from OleDbDataReader) into SQL Server.
5) Close opened connections

That's all. Try!
 
Share this answer
 
Comments
Leo Chapiro 28-Jul-14 4:24am    
That is the only way, +5 !
Maciej Los 28-Jul-14 4:44am    
Thank you ;)
[no name] 28-Jul-14 5:20am    
My ++5
Maciej Los 28-Jul-14 6:50am    
Thank you ;)
Two steps or solutions
1. Import excel to datagridview
2. import grid to database

or

Import excel in both..

First one looks better because you can load grid with excel then you can manually able to do correction in this..
After that you can save that to database.

Following shows how to load grid with excel data.

dgvdetails is datagridview
VB
Private Sub Import_To_Grid()
    Dim FilePath As String = "C:\Item Master-April2013.xlsx"
    Dim conStr As String = ""
    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    conStr = String.Format(conStr, FilePath)
    Dim connExcel As New OleDbConnection
    connExcel.ConnectionString = conStr
    Dim cmdExcel As New OleDbCommand()
    Dim oda As New OleDbDataAdapter()
    cmdExcel.Connection = connExcel

    'Get the name of First Sheet
    connExcel.Open()
    Dim Exceldt As New DataTable()
    Dim dtExcelSchema As DataTable
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
    connExcel.Close()

    'Read Data from First Sheet
    connExcel.Open()
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"
    oda.SelectCommand = cmdExcel

    oda.Fill(Exceldt)
    connExcel.Close()

    'Bind Data to GridView
    dgvDetails.DataSource = Exceldt
    'BindDataToCmbClass() 'binddata to class for filter
    'cmb_userclass.SelectedIndex = 0
End Sub


Following shows how to save data of grid with specific columns and conditions
r as integer
sdcmd as sqlcommand with your query and connection. I Believe, at least you know this.

As i don't know your columns.. InvoiceNo,SrNo,ProductID,Parameters,Quantity are the columns.

VB
            For r = 0 To dgvDetails.RowCount - 1
                If Val(dgvDetails.Rows(r).Cells("SrNo").Value) <> 0 Then
                    sdCmd.Parameters.Clear()
                    sdCmd.Parameters.Add("@InvoiceNo", OleDb.OleDbType.WChar).Value = r
                    sdCmd.Parameters.Add("@SrNo", OleDb.OleDbType.Numeric).Value = dgvDetails.Rows(r).Cells("SrNo").Value
                    sdCmd.Parameters.Add("@ProductID", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("ProductID").Value
                    sdCmd.Parameters.Add("@Particulars", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("Particulars").Value
                    sdCmd.Parameters.Add("@Quantity", OleDb.OleDbType.Currency).Value = dgvDetails.Rows(r).Cells("Quantity").Value
                End If
sdCmd.ExecuteNonQuery()
            Next
 
Share this answer
 
Comments
Maciej Los 28-Jul-14 2:57am    
Never do that this way! It's the worse what you can do. Why? 1) performance, 2) adding record by record could cause SQL Server hangs, etc.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900