Fix: Jet can get the datatype wrong with Excel as data source using vb.net

Good Morning. I cannot believe what happened on "The Shield" last night. All of Mackey’s talk about loyalty and family went straight down the tubes when his butt was on the line. Is there anyone left routing for this bastard? I think for a lot of us, it is like we want to see justice served and see this guy go down in flames. it’s a car wreck and we can’t turn away. Next week is a 90 minute episode and believe you me I will be taping this one.

Anyway, I really struggled with this. When connecting to an excel spreadsheet the Jet Engine "fixes" the datatypes with reference to a scan of some rows and then produces NULLS when subsequent values in that column do not conform to that fixed datatype: specifically text in number columns. This is what I came up with and I have attached the spreadsheet. I hope you never have to deal with this problem.  Don’t forget to change the path for the attached spreadsheet. Make it a great day!

Imports System.Data.OleDb
Public Class Form1
    Private DataFile As String = "C:\Test\TestImport.xls"
    Private ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
    Private con As New OleDbConnection(ConString)
    Private dt As New DataTable("TestTable")
    Private Sub LoadData()
        Dim sql As String = "SELECT * FROM [Sheet1$]"
        Dim da As New OleDbDataAdapter(sql, con)
        da.Fill(dt)
        Dim dc As New DataColumn("AsString", GetType(String))
        dt.Columns.Add(dc)
        For i As Integer = 0 To dt.Rows.Count – 1
            Dim dr As DataRow = dt.Rows(i)
            If TypeOf (dr("F1")) Is DBNull Then
                dr("AsString") = CheckCell(i)
            Else
                dr("AsString") = CStr(dr("F1"))
            End If
        Next
        DataGridView1.DataSource = dt
    End Sub
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        LoadData()
    End Sub
    Private Function CheckCell(ByVal Index As Integer) As String
        Dim result As String = ""
        Dim sql As String = "SELECT * FROM [SHEET1$A" & CStr(Index + 1) & ":A" & CStr(Index + 1) & "]"
        Dim cmd As New OleDbCommand(sql, con)
        con.Open()
        Dim o As Object = cmd.ExecuteScalar
        con.Close()
        If TypeOf (o) Is DBNull Then
            result = "NULL"
        Else
            result = o.ToString
        End If
        Return result
    End Function
End Class

 http://cid-a0d71e1614e8dbf8.skydrive.live.com/embedrowdetail.aspx/Public/TestImport.xls

 

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: