Solved

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

Posted on 2009-04-03
6
278 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

21 Experts available now in Live!

Get 1:1 Help Now