Importing data from Excel - VB.NET

Question

I am trying to import some data from an excel spreadsheet, using VB.net

my steps are:

first the user uploads the file to the server

then i want to read the file from the server to then populate a gridview

this is what i have:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim filepath As String = ""
        If FileUpload1.HasFile Then
            Try
                If (FileUpload1.PostedFile.ContentType = "application/vnd.ms-excel") Then
                    Dim filename As String = Path.GetFileName(FileUpload1.FileName)
                    'Session("userid") & "-" & Date.Now()
                    filepath = "\excel\" & Session("userid") & "_" & Now.Date().ToString("Mdy") & "_" & filename
                    FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
                    ReadExcel(filepath)

                Else
                    StatusLabel.Text = "Only Excel file types are accepted"
                End If

            Catch ex As Exception
                StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message
            End Try

        End If
    End Sub
    Sub ReadExcel(ByVal filepath As String)
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & filepath & "';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [NSTS]", MyConnection)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        gwResults.DataSource = DtSet.Tables(0)
        MyConnection.Close()
    End Sub

the error happens with "MyConnection", it tried to look on the "C:/" instead of on the server:

'c:\excel\3_41911_Sample.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

how can i set the OleDb connection to get the source file from the server instead?

thank you!

nevermind, i got it

i added: Server.MapPath("~/") & filepath and now it works. however, now i'm getting the error: The Microsoft Jet database engine could not find the object 'NSTS'. Make sure the object exists and that you spell its name and the path name correctly.

NSTS is the name of my first spreadsheet. what am i doing wrong? :(

i was missing a studip dollar sign :) ahh, it all works now!

"select * from [NSTS$]"

thanks!

p.campbell · Accepted Answer · 2011-04-19 16:06:51Z

2

Use a $ in your sheet's name in the query:

"select * from [NSTS$]"

answered Apr 19, 2011 at 16:06

p.campbell

101k70 gold badges263 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Priyank · Accepted Answer · 2011-04-19 16:04:23Z

1

Your c:\excel\ path is not local path but it is path local to where you are running your application.

If you are running this application from local machine, In order to map c:\excel\ path, you should either map server drive to your windows and use that drive name OR use \\excel as path value.

answered Apr 19, 2011 at 16:04

Priyank

10.7k2 gold badges29 silver badges25 bronze badges

Comments

Gary Barnett · Accepted Answer · 2011-04-19 16:05:39Z

First - do you know exactly where on the server path the file is being saved to? I'd begin by hardcoding the path to make sure that there isn't anything else squirrely going on.

Looking at your code you're saving the file here...

FileUpload1.SaveAs(Server.MapPath("~/") & filepath)

So... first, are you sure it's saving there? If so then look as where you're reading the file with this call...

                ReadExcel(filepath)

Have you tried -

ReadExcel(Server.MapPath("~/") & filepath)?

Collectives™ on Stack Overflow

Importing data from Excel - VB.NET

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related