Solved

How to get numeric value from Excel into datatable/dataset?

Posted on 2009-04-03
6
293 Views
Last Modified: 2013-12-17
Hi,
Apparently, any numeric values are not being displayed in the Gridview (fine with text though). Is this a bug or something?

Please advise.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            Dim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString
            ' Create the connection object 
            Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
            Try
                  ' Open connection
                  oledbConn.Open()
 
                  ' Create OleDbCommand object and select data from worksheet Sheet1
                  Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
 
                  ' Create new OleDbDataAdapter 
                  Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
 
                  oleda.SelectCommand = cmd
 
                  ' Create a DataSet which will hold the data extracted from the worksheet.
                  Dim ds As DataSet = New DataSet()
 
                  ' Fill the DataSet from the data extracted from the worksheet.
                  oleda.Fill(ds, "Employees")
 
                  ' Bind the data to the GridView
                  GridView1.DataSource = ds.Tables(0).DefaultView
                  GridView1.DataBind()
            Catch
            Finally
                  ' Close connection
                  oledbConn.Close()
            End Try
 
      End Sub

Open in new window

0
Comment
Question by:tangteng78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:VikramMullick
ID: 24057995
Change the  file extension to .csv and try again.
0
 
LVL 6

Expert Comment

by:HarryNS
ID: 24058017
The following code works fine for me. It brings all the Numeric Values from excel to grid view.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        Dim strFilePath As String = "C:\\Test.xls"
        Dim excelConnectionString As String = "Dsn=Excel Files;dbq=" & strFilePath & ";defaultdir=" & ";driverid=790;maxbuffersize=2048;pagetimeout=5"
 
        Dim connString As String = excelConnectionString
        ' Create the connection object 
        Dim oledbConn As System.Data.Odbc.OdbcConnection = New System.Data.Odbc.OdbcConnection(connString)
        Try
            ' Open connection
            oledbConn.Open()
 
            ' Create OleDbCommand object and select data from worksheet Sheet1
            Dim cmd As System.Data.Odbc.OdbcCommand = New System.Data.Odbc.OdbcCommand("SELECT * FROM [Sheet1$]", oledbConn)
 
            ' Create new OleDbDataAdapter 
            Dim oleda As System.Data.Odbc.OdbcDataAdapter = New System.Data.Odbc.OdbcDataAdapter()
 
            oleda.SelectCommand = cmd
 
            ' Create a DataSet which will hold the data extracted from the worksheet.
            Dim ds As DataSet = New DataSet()
 
            ' Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Employees")
 
            ' Bind the data to the GridView
            Me.DataGridView1.DataSource = ds.Tables(0).DefaultView
            DataGridView1.Show()
        Catch
        Finally
            ' Close connection
            oledbConn.Close()
        End Try
 
    End Sub

Open in new window

0
 

Author Comment

by:tangteng78
ID: 24058184
Can you try putting in both text and numeric values in the excel? For my case, either one of them will not be displayed.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 6

Accepted Solution

by:
HarryNS earned 250 total points
ID: 24060219

Following is what is present in the XLS file and I have attached the grid in which I ported it...

KEYID      PKEYID      PRODUCT_NAME      product_desc
555             X01                                      1
556      555      X01.Alpha                                      1
621      556      X01.Alpha.metal                      1
622      621      X01.Alpha.metal.cabin      1
623      622      X01.Alpha.metal.cabin.101      1
624      622      X01.Alpha.metal.cabin.102      1
625      622      X01.Alpha.metal.cabin.103      1
626      556      X01.Alpha.copper                      1
627      626      X01.Alpha.copper.box      1
628      627      X01.Alpha.copper.box.10      1
629      627      X01.Alpha.copper.box.11      1
557            X02                                      1
558      557      X02.Alpha                                      1
630      558      X02.Alpha.metal                      1
631      630      X02.Alpha.metal.cabin      1
632      631      X02.Alpha.metal.cabin.102      1
633      631      X02.Alpha.metal.cabin.103      1

Result.bmp
0
 

Author Comment

by:tangteng78
ID: 24061183
Can you try putting in a number and text on the same column?
0
 

Author Comment

by:tangteng78
ID: 24074072
Kind of experimenting with the problem...if you have more numeric values than text value in the same column, you will only get the numeric values in the datatable, and vise versa.
Example in excel:
colum1
=====
100
101
this is value 1 -> This value will be missing.
102
 
When you dispaly it out, the "text" value will be null (missing).
Is this a bug or something?
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question