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!
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)
Dim dc As New DataColumn("AsString", GetType(String))
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)
dr("AsString") = CStr(dr("F1"))
DataGridView1.DataSource = dt
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
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)
Dim o As Object = cmd.ExecuteScalar
If TypeOf (o) Is DBNull Then
result = "NULL"
result = o.ToString