locked
UPload Excel sheet to database mysql RRS feed

  • Question

  • User-60872656 posted

    Hi

    i am using asp.net and mysql database using VBscript and i have to upload a excel file and it must insert those data in the table can any one help me in this regard

    Thanks

    Avinash desai 

    Tuesday, February 12, 2008 1:05 AM

Answers

  • User2022958948 posted

    Hi,

    Firsly, I want to clarify that VBScript and JavaScript are the Script language in the client side. In ASP.NET, you can use C# or VB.NET on the server side.

    From your another thread, I found VBscript in your question means VB.NET in ASP.NET. Have I misunderstood?

    There are the codes as below can achieve retrieve excel file to dataset which can be used to import to database.

    Imports Microsoft.Office.Interop.Excel
    Imports System.Text
    Imports System.Reflection
    Imports System.Data

     

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            Dim ds As DataSet = GetExcel("c:\abcd.xls")
            Dim dt As System.Data.DataTable = ds.Tables(0)
            GridView1.DataSource = ds
            GridView1.DataBind()
            'Excel file has been imported into DataTable. You can import the data from DataTable into DataBase by using System.Data.SqlClient.
    
        End Sub
        Public Function GetExcel(ByVal fileName As String) As DataSet
            Dim oXL As Application
            Dim oWB As Workbook
            Dim oSheet As Worksheet
            Dim oRng As Range
            Try
                '  creat a Application object
                oXL = New ApplicationClass()
                '   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value)
    
                '   get   WorkSheet object 
                oSheet = DirectCast(oWB.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
                For Each ws As Worksheet In oWB.Sheets
                Next
                Dim dt As New System.Data.DataTable("dtExcel")
                Dim ds As New DataSet()
                ds.Tables.Add(dt)
                Dim dr As DataRow
    
                Dim sb As New StringBuilder()
                Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
                Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
                '  get data columns
                Dim j1 As Integer = 1
                While j1 <= jValue
                    dt.Columns.Add("column" & j1, System.Type.GetType("System.String"))
                    System.Math.Max(System.Threading.Interlocked.Increment(j1), j1 - 1)
                End While
    
                'string colString = sb.ToString().Trim();
                'string[] colArray = colString.Split(':');
    
                '  get data in cell
                Dim i As Integer = 1
                While i <= iValue
                    dr = ds.Tables("dtExcel").NewRow()
                    Dim j As Integer = 1
                    While j <= jValue
                        oRng = DirectCast(oSheet.Cells(i, j), Microsoft.Office.Interop.Excel.Range)
                        Dim strValue As String = oRng.Text.ToString()
                        dr("column" & j) = strValue
                        System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
                    End While
                    ds.Tables("dtExcel").Rows.Add(dr)
                    System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
                End While
                Return ds
            Catch ex As Exception
                Label1.Text = "error: "
                Label1.Text += ex.Message.ToString()
                Return Nothing
            Finally
                Dispose()
            End Try
        End Function

     In C# code, you can check this: http://forums.asp.net/t/1212053.aspx

    Hope it helps.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 13, 2008 10:21 PM
  • User2022958948 posted

    You should add it as reference. Check this: http://forums.asp.net/p/1192930/2067132.aspx#2067132 aalasp encountered this error too.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 14, 2008 11:46 PM
  • User2022958948 posted

    Hi,

    You need to install the Office 2003 or above. And you can download Office.Interop.Excel.dll.

    Please download the dll file at http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

    Another Download URL:  http://www.dll4you.com/download/get.php?file=microsoft.office.interop.excel.dll&site=13&ver=11.0.5530&com=Microsoft%20Corporation&des=Microsoft.Office.Interop.Excel  It's not free.

    There are lots of worth posts in this thread. http://forums.asp.net/t/1192930.aspx  Please do not just check my post.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:19 AM
  • User2022958948 posted

    Select any empty folder which the dll files will copy into. Then please add Microsoft.Office.Interop.Excel.dll as reference.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:40 AM
  • User2022958948 posted

    The dll file in your some folder, right? add reference->Browse(not .net)->to choose your Imports Microsoft.Office.Interop.Excel.dll file

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:59 AM
  • User2022958948 posted

    Is your datetable name right? Please check the format of your datetable. Or you can try to use ds.Tables(0).Rows.Count.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 3, 2008 1:54 AM
  • User2022958948 posted

    Hi,

    You can upload the excel file to server and then get excel file to GridView. Define Button2_Click event to upload file and display the content of the file to GridView instead of Page_Load event.

     <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="upload file and display to GridView"
                style="height: 26px" />

        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            FileUpload1.SaveAs(Server.MapPath("Test.xls"))
            Dim ds As DataSet = GetExcel(Server.MapPath("Test.xls"))
            Dim dt As System.Data.DataTable = ds.Tables(0)
            GridView1.DataSource = ds
            GridView1.DataBind()
            'Excel file has been imported into DataTable. You can import the data from DataTable into DataBase by using System.Data.SqlClient.

        End Sub

     Hope it helps.
     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 3, 2008 8:39 PM
  • User2022958948 posted

    Or you can use FileUpload1.SaveAs(Server.MapPath(FileUpload1.FileName)) to custom filename as the filename the user chose.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 4, 2008 12:41 AM
  • User2022958948 posted

    Hi,

    This error means ds.Tables("dtExcel") is null. So please make sure your table name is correct. And you have import the data into this table.

    If you still have any questions, please post a new thread that will be more helpful to you and more members can help you. We will follow up for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 6, 2008 8:33 PM

All replies

  • User1774691446 posted

    Please refer this link

    http://excel-mysql.qarchive.org/

    Tuesday, February 12, 2008 2:17 AM
  • User-555489963 posted

    This might help as well (though it is in C#):

    http://naspinski.net/post/Converting-an-Excel-Spreedsheet-to-a-DatSet2c-Datatable-and-Multi-Dimensioanl-Array.aspx

     That will get your data into a dataset, datatable, or mutli-dimensional array, then you can just store them how you would like in the mysql db

    Tuesday, February 12, 2008 2:46 AM
  • User2022958948 posted

    Hi,

    Firsly, I want to clarify that VBScript and JavaScript are the Script language in the client side. In ASP.NET, you can use C# or VB.NET on the server side.

    From your another thread, I found VBscript in your question means VB.NET in ASP.NET. Have I misunderstood?

    There are the codes as below can achieve retrieve excel file to dataset which can be used to import to database.

    Imports Microsoft.Office.Interop.Excel
    Imports System.Text
    Imports System.Reflection
    Imports System.Data

     

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            Dim ds As DataSet = GetExcel("c:\abcd.xls")
            Dim dt As System.Data.DataTable = ds.Tables(0)
            GridView1.DataSource = ds
            GridView1.DataBind()
            'Excel file has been imported into DataTable. You can import the data from DataTable into DataBase by using System.Data.SqlClient.
    
        End Sub
        Public Function GetExcel(ByVal fileName As String) As DataSet
            Dim oXL As Application
            Dim oWB As Workbook
            Dim oSheet As Worksheet
            Dim oRng As Range
            Try
                '  creat a Application object
                oXL = New ApplicationClass()
                '   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value)
    
                '   get   WorkSheet object 
                oSheet = DirectCast(oWB.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
                For Each ws As Worksheet In oWB.Sheets
                Next
                Dim dt As New System.Data.DataTable("dtExcel")
                Dim ds As New DataSet()
                ds.Tables.Add(dt)
                Dim dr As DataRow
    
                Dim sb As New StringBuilder()
                Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
                Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
                '  get data columns
                Dim j1 As Integer = 1
                While j1 <= jValue
                    dt.Columns.Add("column" & j1, System.Type.GetType("System.String"))
                    System.Math.Max(System.Threading.Interlocked.Increment(j1), j1 - 1)
                End While
    
                'string colString = sb.ToString().Trim();
                'string[] colArray = colString.Split(':');
    
                '  get data in cell
                Dim i As Integer = 1
                While i <= iValue
                    dr = ds.Tables("dtExcel").NewRow()
                    Dim j As Integer = 1
                    While j <= jValue
                        oRng = DirectCast(oSheet.Cells(i, j), Microsoft.Office.Interop.Excel.Range)
                        Dim strValue As String = oRng.Text.ToString()
                        dr("column" & j) = strValue
                        System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
                    End While
                    ds.Tables("dtExcel").Rows.Add(dr)
                    System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
                End While
                Return ds
            Catch ex As Exception
                Label1.Text = "error: "
                Label1.Text += ex.Message.ToString()
                Return Nothing
            Finally
                Dispose()
            End Try
        End Function

     In C# code, you can check this: http://forums.asp.net/t/1212053.aspx

    Hope it helps.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 13, 2008 10:21 PM
  • User-60872656 posted

    Hi

    I tried this code while writing this code in "Imports Microsoft.Office.Interop.Excel" and there are so many errors occuring in these lines

    ---------------------------

     Dim oXL As Application
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim oRng As Range
    ---------------------------------
    can you help me in this
    Thanks
    Avinash desai 
    Thursday, February 14, 2008 11:38 PM
  • User2022958948 posted

    You should add it as reference. Check this: http://forums.asp.net/p/1192930/2067132.aspx#2067132 aalasp encountered this error too.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 14, 2008 11:46 PM
  • User-60872656 posted

    HI

    I used your steps and i am not getting "Microsoft.Office.Interop.Excel" in ".Net "  tab. can you help me in this

     

    Friday, February 15, 2008 12:04 AM
  • User2022958948 posted

    Hi,

    You need to install the Office 2003 or above. And you can download Office.Interop.Excel.dll.

    Please download the dll file at http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

    Another Download URL:  http://www.dll4you.com/download/get.php?file=microsoft.office.interop.excel.dll&site=13&ver=11.0.5530&com=Microsoft%20Corporation&des=Microsoft.Office.Interop.Excel  It's not free.

    There are lots of worth posts in this thread. http://forums.asp.net/t/1192930.aspx  Please do not just check my post.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:19 AM
  • User-60872656 posted

     

    Hi

    I have downloaded it from first URL and i dont knw where to place it . may i know where to place it

     

     

    Friday, February 15, 2008 12:25 AM
  • User2022958948 posted

    Select any empty folder which the dll files will copy into. Then please add Microsoft.Office.Interop.Excel.dll as reference.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:40 AM
  • User-60872656 posted

     Hi

    I tried it but also its not displaying that dll file in  ".Net " tab

    Friday, February 15, 2008 12:54 AM
  • User2022958948 posted

    The dll file in your some folder, right? add reference->Browse(not .net)->to choose your Imports Microsoft.Office.Interop.Excel.dll file

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 15, 2008 12:59 AM
  • User-60872656 posted

     Hi

    now it works and the data from excel sheet is binded in gridview but i need to insert it in to the data base table how is it done can show me where to take right step for this 

    Friday, February 15, 2008 1:31 AM
  • User2022958948 posted

    After the codes as above, you get the DataSet which can be inserted into database via sql statement. Your problem is insert DataSet into DataBase.

           Imports System.Data.SqlClient
           Imports System.Data
    
    
    
            Dim constr As String = "...." 'your connection string
            Dim connection As SqlConnection = New SqlConnection(constr)
            Dim i As Integer
            For i = 0 To ds.Tables("table").Rows.Count - 1
                Dim name As String = ds.Tables("table").Rows(i)("ID")
                'get every cell content of this row like the above line
                Dim sqlsel As String = "insert into table('ID') values('" + name + "')"
    
                Dim sdc As SqlCommand = New SqlCommand(sqlsel, connection)
                sdc.CommandType = CommandType.Text
                connection.Open()
                sdc.ExecuteNonQuery()
                connection.Close()
    
            Next

     You can check this link about insert into dataset: http://msdn.microsoft.com/en-us/library/aa720188(VS.71).aspx

    I would like to suggest you learn data-access in http://www.asp.net/learn/ firstly.

     

     

    Friday, February 15, 2008 1:55 AM
  • User-60872656 posted

    Hi

    I have 10 columns and canyou explain me these below line code

     Dim name As String = ds.Tables("table").Rows(i)("ID")
    'get every cell content of this row like the above line
    Can you give me one simple example for  this ? 
    Monday, March 3, 2008 12:40 AM
  • User-60872656 posted

    Hi

    I am getting an error in this

    line

     

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    ' Dim constr As String = "...." 'your connection string
    ' Dim connection As SqlConnection = New SqlConnection(constr)
    Dim i As Integer
    For
    i = 0 To ds.Tables("table").Rows.Count - 1
    Dim one As String = ds.Tables("table").Rows(i)("Column1")
    Dim two As String = ds.Tables("table").Rows(i)("Column2")
    Dim three As String = ds.Tables("table").Rows(i)("Column3")
    Dim four As String = ds.Tables("table").Rows(i)("Column4")
    Dim five As String = ds.Tables("table").Rows(i)("Column5")
    Dim six As String = ds.Tables("table").Rows(i)("Column6")
    Dim seven As String = ds.Tables("table").Rows(i)("Column7")
    Dim eight As String = ds.Tables("table").Rows(i)("Column8")
    Dim nine As String = ds.Tables("table").Rows(i)("Column9")
    Dim ten As String = ds.Tables("table").Rows(i)("Column10")

    'get every cell content of this row like the above line
    ' Dim sqlsel As String = "insert into table('ID') values('" + one + "')"
    Dim cmdSavegrd As OdbcCommand = New OdbcCommand("INSERT INTO gd_master VALUES( " & one & ",'" & two & "', '" & three & "'," & four & ",'" & five & "'," & six & "," & seven & ",'" & eight & "'," & nine & ",'" & ten & "')", con)
    'Dim sdc As SqlCommand = New SqlCommand(sqlsel, con)
    ' sdc.CommandType = CommandType.Text
    con.Open() 'sdc.ExecuteNonQuery() cmdSavegrd.ExecuteReader() con.Close() Next
    End Sub

      I am getting error in the for statement as follows:

    "Object reference not set to an instance of an object." 

    please can you help me to solve this problem..........

     

    Monday, March 3, 2008 1:10 AM
  • User2022958948 posted

    Which line you encountered error? This line "Dim one As String = ds.Tables("table").Rows(i)("Column1")" ?

    You can check your dataset if the column of dataset "column1" is exist. In the process of retrieving data from excel file, you used "dt.Columns.Add("column" & j1, System.Type.GetType("System.String"))". And the column name is "column1" rather than "Column1".(It's lowercase)

    Monday, March 3, 2008 1:30 AM
  • User-60872656 posted

    HI

    I am getting the error in this for loop line 

     Dim ia As Integer = ds.Tables("table").Rows.Count - 1
            For i = 0 To ia
     

    and the error is

    Object reference not set to an instance of an object. 

    Monday, March 3, 2008 1:43 AM
  • User2022958948 posted

    Is your datetable name right? Please check the format of your datetable. Or you can try to use ds.Tables(0).Rows.Count.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 3, 2008 1:54 AM
  • User2032571450 posted

    try this..

    string fname = File1.Value.ToString();
       try
       {
       
        if(File1.Value.ToString().Equals(""))
        {
         Label15.Visible=true;
         Label13.Visible=true;
         Label15.Text="Select a file";
        }
        else
        {
         // Code to Import from Excel in to database.
         string dbFileName = fname;
         string insertSql = "INSERT INTO tblDatatable SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=" + dbFileName + "',[Sheet1$])";
         SqlCommand commandInsExcSql = new SqlCommand(insertSql, con);
      
       
         con.Open();
         commandInsExcSql.ExecuteNonQuery();
         con.Close();
         Label4.Text="File Successfully uploaded to the database";
         Button1.Visible=true;
         Label18.Visible=true;
        }
        Label4.Visible=true;
       }

    Monday, March 3, 2008 3:55 AM
  • User-60872656 posted

     Hi

    It was mistake in my data table where i used tables instead of dtExcel  .Thanks for your reply it helped me a lot

    Monday, March 3, 2008 4:34 AM
  • User-60872656 posted

    Hi

    Vince Xu

    Can you help me in using file upload when i want the data to be inserted from the file i want it to browsed by  the user  and after selecting the file on button click the data must load in to the gridview and then it has to import to the database here is my full code for this:

     code behind the page

    Imports Microsoft.Office.Interop.Excel
    Imports System.Text
    Imports System.Reflection
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.Odbc
    Imports System.Data.Odbc.OdbcConnection
    
    Partial Class excelupload
        Inherits System.Web.UI.Page
        Public ds As New DataSet()
        Public ConnStr As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=TaxPRo;uid=root;pwd=password;option=3"
        Public con As OdbcConnection = New System.Data.Odbc.OdbcConnection(ConnStr)
    
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            Dim ds As DataSet = GetExcel("C:\Test.xls")
            Dim dt As System.Data.DataTable = ds.Tables(0)
            GridView1.DataSource = ds
            GridView1.DataBind()
            'Excel file has been imported into DataTable. You can import the data from DataTable into DataBase by using System.Data.SqlClient.
    
        End Sub
        Public Function GetExcel(ByVal fileName As String) As DataSet
    
    
    
            Dim oXL As Application
            Dim oWB As Workbook
            Dim oSheet As Worksheet
            Dim oRng As Range
            Try
                '  creat a Application object
                oXL = New ApplicationClass()
                '   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                 Missing.Value, Missing.Value, Missing.Value)
    
                '   get   WorkSheet object 
                oSheet = DirectCast(oWB.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
                For Each ws As Worksheet In oWB.Sheets
                Next
                Dim dt As New System.Data.DataTable("dtExcel")
    
                ds.Tables.Add(dt)
                Dim dr As DataRow
    
                Dim sb As New StringBuilder()
                Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
                Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
                '  get data columns
                Dim j1 As Integer = 1
                While j1 <= jValue
                    dt.Columns.Add("column" & j1, System.Type.GetType("System.String"))
                    System.Math.Max(System.Threading.Interlocked.Increment(j1), j1 - 1)
                End While
    
                'string colString = sb.ToString().Trim();
                'string[] colArray = colString.Split(':');
    
                '  get data in cell
                Dim i As Integer = 1
                While i <= iValue
                    dr = ds.Tables("dtExcel").NewRow()
                    Dim j As Integer = 1
                    While j <= jValue
                        oRng = DirectCast(oSheet.Cells(i, j), Microsoft.Office.Interop.Excel.Range)
                        Dim strValue As String = oRng.Text.ToString()
                        dr("column" & j) = strValue
                        System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
                    End While
                    ds.Tables("dtExcel").Rows.Add(dr)
                    System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
                End While
                Return ds
            Catch ex As Exception
                ' Label1.Text = "error: "
                ' Label1.Text += ex.Message.ToString()
                Return Nothing
            Finally
                Dispose()
            End Try
        End Function
    
        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            '  Dim constr As String = "...." 'your connection string
            ' Dim connection As SqlConnection = New SqlConnection(constr)
            If DropDownList1.SelectedValue = "gd_master" Then
    
    
                Dim i As Integer
                Dim ia As Integer = ds.Tables("dtExcel").Rows.Count - 1
                For i = 0 To ia
                    Dim one As String = ds.Tables("dtExcel").Rows(i)("Column1")
                    Dim two As String = ds.Tables("dtExcel").Rows(i)("Column2")
                    Dim three As String = ds.Tables("dtExcel").Rows(i)("Column3")
                    Dim four As String = ds.Tables("dtExcel").Rows(i)("Column4")
                    Dim five As String = ds.Tables("dtExcel").Rows(i)("Column5")
                    Dim six As String = ds.Tables("dtExcel").Rows(i)("Column6")
                    Dim seven As String = ds.Tables("dtExcel").Rows(i)("Column7")
                    Dim eight As String = ds.Tables("dtExcel").Rows(i)("Column8")
                    Dim nine As String = ds.Tables("dtExcel").Rows(i)("Column9")
                    Dim ten As String = ds.Tables("dtExcel").Rows(i)("Column10")
    
                    'get every cell content of this row like the above line
                    ' Dim sqlsel As String = "insert into table('ID') values('" + one + "')"
                    Dim cmdSavegrd As OdbcCommand = New OdbcCommand("INSERT INTO gd_master VALUES( " & one & ",'" & two & "', '" & three & "'," & four & ",'" & five & "'," & six & "," & seven & ",'" & eight & "'," & nine & ",'" & ten & "')", con)
                    'Dim sdc As SqlCommand = New SqlCommand(sqlsel, con)
                    ' sdc.CommandType = CommandType.Text
                    con.Open()
                    'sdc.ExecuteNonQuery()
                    cmdSavegrd.ExecuteReader()
                    con.Close()
    
                Next
            End If
        End Sub
    End Class
     
     
     
    Code
     
    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="excelupload.aspx.vb" Inherits="excelupload" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
    <title>Untitled Page</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    Choose Your Excel &nbsp;File To Upload : <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" Style="z-index: 100; background-color:Transparent ; left: 10px; ;
    top: 229px">

    </asp:GridView>
    &nbsp;
    <asp:Button ID="Button1" runat="server" Style="z-index: 101; left: 11px; ;
    top: 144px" Text="Import to Database" />
    <br />

    Choose The Table For Excel Data Insertion<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
    DataTextField="Tables_in_taxpro" DataValueField="Tables_in_taxpro" Style="z-index: 102;
    left: 21px; ; top: 106px">
    <asp:ListItem Text ="Select Table" Value="0">
    </asp:ListItem>
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringcountrystate %>"
    ProviderName="<%$ ConnectionStrings:ConnectionStringcountrystate.ProviderName %>"
    SelectCommand="show tables"></asp:SqlDataSource>
    &nbsp; &nbsp;
    <asp:FileUpload ID="FileUpload1" runat="server" Style="z-index: 105; left: 28px;
    ; top: 47px" />

    </div>
    </form>
    </body>
    </html>
     
    Please can you help me i used the file upload but i dont where should i use it and how it is used ....Plz can you help me in this 
      
    Monday, March 3, 2008 8:03 AM
  • User2022958948 posted

    Hi,

    You can upload the excel file to server and then get excel file to GridView. Define Button2_Click event to upload file and display the content of the file to GridView instead of Page_Load event.

     <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="upload file and display to GridView"
                style="height: 26px" />

        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            FileUpload1.SaveAs(Server.MapPath("Test.xls"))
            Dim ds As DataSet = GetExcel(Server.MapPath("Test.xls"))
            Dim dt As System.Data.DataTable = ds.Tables(0)
            GridView1.DataSource = ds
            GridView1.DataBind()
            'Excel file has been imported into DataTable. You can import the data from DataTable into DataBase by using System.Data.SqlClient.

        End Sub

     Hope it helps.
     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 3, 2008 8:39 PM
  • User-60872656 posted

     Hi

    Vince Xu 

    This code works for me but there instead of test.xls file there may be any file(XLS)  from any location in the computer how is this accomplished can you help me in this?











     

    Monday, March 3, 2008 11:59 PM
  • User2022958948 posted

    This excel file on server side is a temporary file, so you can use only one file name to achieve it no matter the file name on client. After importing into GridView, the file is useless and it can be covered by other files.

    Tuesday, March 4, 2008 12:12 AM
  • User-60872656 posted

    Hi

    The code contains the Test.xls file only if user want the another file other than this and he will select the file how this is achived? 

    Tuesday, March 4, 2008 12:21 AM
  • User2022958948 posted

    On the client, user chose one file "a.xls" to upload. And it will save in the server as text.xls. After completing uploading, it will imported into GridView. The file name chose on client is not associated with the file name on server. No matter what the file name, it will be saved as text.xls on server as a temporary file.

    Tuesday, March 4, 2008 12:38 AM
  • User2022958948 posted

    Or you can use FileUpload1.SaveAs(Server.MapPath(FileUpload1.FileName)) to custom filename as the filename the user chose.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 4, 2008 12:41 AM
  • User-60872656 posted

    HI

    I used the above code you provided and there after selecting the file when i click on button  the following error appears and the file selected must bind the gridview

    "The process cannot access the file 'C:\Documents and Settings\user\My Documents\Visual Studio 2005\WebSites\avinash\Test.xls' because it is being used by another process. " thanks a lot for your reply

     

    Tuesday, March 4, 2008 12:44 AM
  • User-60872656 posted

    Or you can use FileUpload1.SaveAs(Server.MapPath(FileUpload1.FileName)) to custom filename as the filename the user chose.

     

     

    Thanks a lot now it works fine

     

    Tuesday, March 4, 2008 12:46 AM
  • User-60872656 posted

    Hi

    When i select the file the error appearing as

    "The process cannot access the file 'C:\Documents and Settings\user\My Documents\Visual Studio 2005\WebSites\avinash\category_Master.xls' because it is being used by another process. "

     

    I am not even opened it why this is happening? 

    Tuesday, March 4, 2008 2:06 AM
  • User2022958948 posted

     Hi,

    This line will open file: oXL.Workbooks.Open().

    You can try oXL.Quit() in Finally to close excel. If it still doesn't work, you can use oWB.Save() or oWB.Close( ... ) to close workbook.

    Tuesday, March 4, 2008 3:30 AM
  • User-60872656 posted

    Hi

    May i know where to use this in my code? 

    Tuesday, March 4, 2008 3:50 AM
  • User2022958948 posted

     

    Public Function GetExcel(ByVal fileName As String) As DataSet

            ...... 

            Finally

                oXL.Quit() 
                Dispose()
            End Try
    End Function

    If it still doesn't work, try the below codes

    Public Function GetExcel(ByVal fileName As String) As DataSet

            ...... 

            Finally

                oWB.Save() 'oWB.Close( ... )

                oXL.Quit() 
                Dispose()
            End Try
    End Function

    Tuesday, March 4, 2008 3:59 AM
  • User-60872656 posted

    Hi

    this above code also dint worked for me please can you help ?

    Tuesday, March 4, 2008 4:08 AM
  • User-60872656 posted

    Hi

    Now at this line "  For i = 0 To ds.Tables("dtExcel").Rows.Count - 1"  i am getting the error as follows

    "Object reference not set to an instance of an object."  please can you help me this regard plz

     

    Thursday, March 6, 2008 6:20 AM
  • User2022958948 posted

    Hi,

    This error means ds.Tables("dtExcel") is null. So please make sure your table name is correct. And you have import the data into this table.

    If you still have any questions, please post a new thread that will be more helpful to you and more members can help you. We will follow up for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 6, 2008 8:33 PM
  • User-1845917253 posted

    i worked on this for two days and finally have the final version of the code that takes an excel sheet input and then puts it all in a database

    we just have to be sure about the number of columns to be put in - the rows are calculated automatically

    hopefully this will help others

    using System;

    using System.IO;

    using System.Collections;

    using System.Configuration;

    using System.Data;

    using MySql.Data.MySqlClient;

    using System.Data.OleDb;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.HtmlControls;

    using System.Web.UI.WebControls;

     

     

    public partial class excel : System.Web.UI.Page

    {

     

    string FileToConvert;protected void Page_Load(object sender, EventArgs e)

    {

    FileToConvert = Server.MapPath(
    ".") + "/temp.xls";

    }

    protected void convert_Click(object sender, EventArgs e)

    {

    if (fu.HasFile)

    {

    fu.SaveAs(FileToConvert);//temporarily saves the file to work with (will be cleaned up two lines down)

    ConvertFile();

    File.Delete(FileToConvert);//cleans up the temporary file that was stored

    }

    else

    showArray.Text = "No File Supplied";

    }

    protected void ConvertFile()

    {

     

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileToConvert+";Extended Properties=Excel 8.0;";

    try

    {

    OleDbConnection connection =
    new OleDbConnection(connectionString);

    connection.Open();

    //this next line assumes that the file is in default Excel format with Sheet1 as the first sheet name, adjust accordingly

    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);

    DataSet ds = new DataSet();

    DataTable dt = new DataTable();

    adapter.Fill(ds);//now you have your dataset ds now filled with the data and ready for manipulation

    //the next 2 lines do exactly the same thing, just shows two ways to get the same data

    dt = ds.Tables[0];

    adapter.Fill(dt);//overwrites the previous declaration with the same information

    //now you have your datatable filled and ready for manipulation

    connection.Close();

    int number_of_columns = dt.Columns.Count;

    int number_of_rows = ds.Tables[0].Rows.Count/2;

    string[,] sheetEntries = new string[number_of_rows,number_of_columns];

    string[] columnNames = new string[number_of_columns];

    //gets column names

    for (int j = 0; j < number_of_columns; j++)

    columnNames[j] = dt.Columns[j].ToString();

    //gets excel spreadsheet information

    for (int i = 0; i < number_of_rows; i++)

    {

    for (int j = 0; j < number_of_columns; j++)

    sheetEntries[i, j] = dt.Rows[i].ItemArray.GetValue(j).ToString();

    }

    //now the array is filled with data and ready for manipulation

    ////////////////////////////////////////////////////////////////////////////////////////////////////

    // unneccessary, but it shows that the stuff was pushed correctly into the array /

    /////////////////////////////////////////////////////////////////////////////////////////////////////

     

    MySqlConnection mySqlConnection1 =
    new MySqlConnection("server=localhost; database=mydatabase; pooling=false;");

     

    for (int i1 = 0; i1 < number_of_rows; i1++)

    {

    string strSQL1 = "insert into fcodocline (fundnumber, mixedfundnumber, currbudget, actuals, budgetoutstanding) values (" + sheetEntries[i1, 0] + ",'" + sheetEntries[i1, 1] + "'," + sheetEntries[i1, 2] + "," + sheetEntries[i1, 3] + "," + sheetEntries[i1, 4] + ");";

     

    MySqlCommand mySqlCommand1 =
    new MySqlCommand(strSQL1);

    mySqlCommand1.Connection = mySqlConnection1;

    mySqlConnection1.Open();

    mySqlCommand1.ExecuteNonQuery();

    mySqlCommand1.Connection.Close();

    }

    showArray.Text =
    "<h2>Data Displayed From Array</h2><table><tr>";

    foreach (string s in columnNames)

    showArray.Text += "<th>" + s + "</th>";

    showArray.Text += "</tr>";

    for (int i = 0; i < number_of_rows; i++)

    {

    showArray.Text +=
    "<tr>";

    for (int j = 0; j < number_of_columns; j++)

    showArray.Text += "<td>" + sheetEntries[i, j] + "</td>";showArray.Text += "</tr>";

    }

    showArray.Text +=
    "</table>";

     

    }

    catch (Exception ex)

    {

    showArray.Text =
    "<h2>[error]</h2><b>details:</b><br />" + ex.ToString();

    }

    }

    }

    Wednesday, June 25, 2008 9:11 AM
  • User1578255474 posted

    Avinash,

    To upload Excel Sheet to MySQL - If you are ok using System.Data.OleDb the following might help.

    private void ReadFromXLS(string xlsFileNameWithPath)
    {
        string sheetName = string.Empty;
        string sqlQuery = string.Empty;

        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
        Data Source={0};
        Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";

        connectionString = string.Format(connectionString, xlsFileNameWithPath);

        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString))
        {
     conn.Open();
     DataTable dtSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);


     foreach (DataRow drSheets in dtSheets.Rows)
     {
         DataSet dsSheetResults = new DataSet();
         sheetName = drSheets["TABLE_NAME"].ToString();
         sqlQuery = string.Format("SELECT * FROM [{0}]", sheetName);

         System.Data.OleDb.OleDbDataAdapter OleDbda = new System.Data.OleDb.OleDbDataAdapter(sqlQuery, conn);

         OleDbda.Fill(dsSheetResults);

         using (DataTable dt = dsSheetResults.Tables[0])
         {
      //Insert Values to MYSQL Loop through DataRow
      ////dr[0].ToString());
      
         }

         //release objects since it is in loop
         dsSheetResults = null;
         sb = null;
     }

     conn.Close();
        }
    }

     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, June 25, 2008 9:51 AM
  • User-60872656 posted

    Hi

      bn222

        As i got the Solution before 2 moths and now here its working fine and Hope the code you provided may help others.

     

     

    Thursday, June 26, 2008 12:09 AM
  • User1772768441 posted

    Hi Vince Xu,

     Thank you for you're post, it's very much helpful... but it's does not have a column name... how can I put a colunm name?

     

     

    Tuesday, February 10, 2009 7:48 PM
  • User259409749 posted

    Thanks,

    Now how can place the dta in array to mysql database.

    With regards,

    Panduranga V


    Friday, August 6, 2010 3:10 AM