Results 1 to 2 of 2
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010

    Importing Excel to datatatable, all number character will not import and left as Null

    I learn import a Excel file into a datatable from this link.


    Second column of Excel file is text type and data stored like "EMP" or "119".

    After imported, all number character will not import and left as Null.

    How to fix it?
    ----------------- Code is list below ---------------------

    Imports System.Data
    Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    Dim MyConnection As System.Data.OleDb.OleDbConnection
    Dim DtSet As System.Data.DataSet
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    MyConnection = New System.Data.OleDb.OleDbConnection _
    ("provider=Microsoft.Jet.OLEDB.4.0;" _
    " Data Source='c:\testfile.xls'; " _
    "Extended Properties=Excel 8.0;")
    MyCommand = New System.Data.OleDb.OleDbDataAdapter _
    ("select * from [Sheet1$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "TestTable")
    DtSet = New System.Data.DataSet
    DataGridView1.DataSource = DtSet.Tables(0)
    Catch ex As Exception
    End Try
    End Sub
    End Class

  2. #2
    Hyperactive Member
    Join Date
    Sep 2017

    Re: Importing Excel to datatatable, all number character will not import and left as

    In my experience both ACE and JET both ignore extended properties when it comes to not inferring datatypes or explicitly trying to define them. I find it to be junky for a lack of better term. I've done this dance before and threw those shoes away. If you can get the data in CSV or TXT or quite frankly anything BUT excel you're better off creating a Schema.ini file and define the import yourself.

    Otherwise you may have better luck using Interop

    Also if you take the try block out of your code you can at least see where it's bombing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.