• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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

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
tangteng78
Asked:
tangteng78
  • 3
  • 2
1 Solution
 
VikramMullickCommented:
Change the  file extension to .csv and try again.
0
 
HarryNSCommented:
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
 
tangteng78Author Commented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
HarryNSCommented:

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
 
tangteng78Author Commented:
Can you try putting in a number and text on the same column?
0
 
tangteng78Author Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now