[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-04-03
6
Medium Priority
?
296 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Accepted Solution

by:
HarryNS earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 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