Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am trying to build web application in which I have to upload system generated excel sheet & import data to SQL, I have tried below mentioned code but it only works if I open the excel do save as excel before uploading it.

Please help..

Also I need to delete some specific rows of the excel before uploading.

What I have tried:

VB.NET
Dim excelPath As String = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)

Dim connString As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
    Case ".xls"
        'Excel 97-03
        connString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
        Exit Select
    Case ".xlsx"
        'Excel 07 or higher
        connString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
        Exit Select
End Select

connString = String.Format(connString, excelPath)
Using excel_con As New OleDbConnection(connString)
    excel_con.Open()
    Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
    Dim dtExcelData As New DataTable()

    Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
        oda.Fill(dtExcelData)
    End Using
    excel_con.Close()

    Dim conString As String = ConfigurationManager.ConnectionStrings("SQLDbConnection").ConnectionString
    Using con  As New SqlConnection(conString)
        Using sqlBulkCopy As New SqlBulkCopy(con)
            'Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.TESTTBL"
            con.Open()
            sqlBulkCopy.WriteToServer(dtExcelData)
            con.Close()
        End Using
    End Using
End Using
Posted
Updated 2-Apr-20 6:09am
v3
Comments
MadMyche 2-Apr-20 11:51am    
Please don't use all ALLCAPS which is considered yelling. Please wrap your code in a code block. You can use the Improve question widget above this to do just that.

Then I will consider reading it
Hemil Gandhi 2-Apr-20 12:08pm    
Thanks advice, I have done changes accordingly

1 solution

If you need to "save as Excel" before uploading, then the file you're uploading is not a valid Excel file. You need to fix the code that generates the file, rather than the code that processes it.

As for deleting "some specific rows" before uploading, you'll either need to open the file and delete the rows before uploading, or delete the rows from the table once you've imported it. In either case, since we can't see the data and we don't know how to identify the rows to delete, there's nothing else we can tell you.
 
Share this answer
 
Comments
Hemil Gandhi 2-Apr-20 12:10pm    
Can you please help me out with the best solution, if you provide your email, I will share file for better understanding.
Richard Deeming 2-Apr-20 12:12pm    
No, sorry, I already have a full time job. I don't have time to provide private tutoring.

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