locked
Importing data from Excel RRS feed

  • Question

  • User1568384800 posted

    Hi all.

    I'm importing some data from Excel. I open it as an ole database and save the data into a dataset using an sql adaptor.

    However, some values I'm expecting to be integers (1) are coming up as dates "#1/1/1900#". The cells in the excel spreadsheet are set as general and are just numbers and not dates.

    Any ideas on resolving this?

    My code:

    Dim SQL As String = "SELECT TOP 25 * FROM [Total$]"
            Dim MyOleDBCommand As New OleDbCommand(SQL, con)
    
            Dim adaptor As New OleDbDataAdapter(MyOleDBCommand)
    
            Dim MyDataSet As New DataSet
    
            adaptor.Fill(MyDataSet, "Total")
    
             
            For i = 0 To MyDataSet.Tables(0).Rows().Count - 1
                If i > 10 Then
                    Dim StoreNo As String = MyDataSet.Tables(0).Rows(i).Item("F2").ToString()
                    Dim CustomerName As String = MyDataSet.Tables(0).Rows(i).Item("F3").ToString()
                    Dim OrderReceivedDate As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F4").ToString())
    
                    Dim PONo As String = MyDataSet.Tables(0).Rows(i).Item("F5").ToString()
                    Dim InvoiceNo As String = MyDataSet.Tables(0).Rows(i).Item("F6").ToString()
                    Dim ContainerNo As String = MyDataSet.Tables(0).Rows(i).Item("F7").ToString()
                    Dim ShipmentDepart As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F8"))
                    Dim ShipmentArrive As Date = ConvertExcelDateType(MyDataSet.Tables(0).Rows(i).Item("F9"))
                    Dim ProductionTime As Decimal = Convert.ToDecimal(MyDataSet.Tables(0).Rows(i).Item("F10"))
                    Dim OrderToPC As String = MyDataSet.Tables(0).Rows(i).Item("F11").ToString()
                    Dim OrderOption As String = MyDataSet.Tables(0).Rows(i).Item("F12").ToString()
                    Dim OrderColour As String = MyDataSet.Tables(0).Rows(i).Item("F13").ToString()
    
                    Dim OrderID As Integer = Orders.AddOrder(OrderReceivedDate, New Date, ProductionTime, 0, PONo, ShipmentDepart, ShipmentArrive, 0, StoreNo, CustomerName, OrderToPC, InvoiceNo, ContainerNo, 1)
                    Dim OrderedProductID As Integer = Orders.OrderProduct(OrderID, OrderOption, OrderColour)
    
                    Dim ModuleHT As New Hashtable
    
                    Dim CurrentModules As Modules = Modules.GetModules()
    
                    For j = 0 To CurrentModules.ModuleIDs.Count - 1
                        'Modules
                        If Not IsDBNull(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j))) Then
                            If Integer.TryParse(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j)), 0) Then
                                If MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j)) > 0 Then
                                    Orders.AddOrderedModule(OrderedProductID, CurrentModules.ModuleIDs.Item(j), CInt(MyDataSet.Tables(0).Rows(i).Item(CurrentModules.ModuleCelRefs.Item(j))))
                                End If
                            End If
                        End If
                    Next
                End If

    Tuesday, June 18, 2013 10:50 AM

All replies

  • User269602965 posted

    What is the IMEX setting in your connection definition

    IMEX = 0 excel oledb attempts native data type

    IMEX = 1 excel oledb treats as string

    I like to use IMEX = 1 and convert strings to datatype desired on .net side

    Tuesday, June 18, 2013 1:48 PM