Solved

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

Posted on 2009-04-03
6
281 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now