0

I have a function in vb.net to load data in excel to a datagridview. The date value is missing in the datagridview.

The data in the datagridview:

enter image description here

The data in the excel file:

enter image description here

The related code:

        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='" + localPathIn + "ExportedExcel.xls';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [ExportedExcel$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

How can I make it work? thanks in advance.

Maple Li
  • 43
  • 1
  • 9

1 Answers1

1

Try adding IMEX=1; as a connection string parameter to treat all data as text. If some values are not valid dates then it could cause your problem. Of course you may need a second step of processing to get data into the types you want.

Crowcoder
  • 11,250
  • 3
  • 36
  • 45
  • Sorry Crowcoder, I have no idea where to add IMEX=1. thank you. – Maple Li Feb 05 '15 at 00:54
  • Like all other parameters in the connection string : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + localPathIn + "ExportedExcel.xls';Extended Properties=Excel 8.0;IMEX=1" – Crowcoder Feb 05 '15 at 01:26
  • Hi Crowcoder, I have tried to add IMEX=1 to the end of MyConnection string, but get the error: System.Data.OleDb.OleDbException: Could not find installable ISAM. – Maple Li Feb 05 '15 at 01:45
  • It appears some have had success as described here: http://stackoverflow.com/questions/512143/error-could-not-find-installable-isam – Crowcoder Feb 05 '15 at 01:51
  • Hi Crowcoder, I have fixed it by changing the column format of the excel file before export it to the datagridview, thanks a lot. – Maple Li Feb 05 '15 at 01:59
  • Hi Crowcoder, there is a minor things not perfectly. I use oSheet.Range("F:F").NumberFormat = "dd/MM/yy hh:mm:ss;@" to change the column format, but it always give me date format in MM/dd/yyyy, which dd/MM/yyyy is preferred, any idea? thank you. – Maple Li Feb 05 '15 at 02:05
  • I'm sorry, I don't really know. Could be a system culture setting. Might be best to ask a new question. – Crowcoder Feb 05 '15 at 02:06