locked
DataSet , DataAdapter and excel issue RRS feed

  • Question

  • User15800025 posted

     Ok interestingly enough I have some code that pulls data from an excel file and displays it to screen. 

    Taking a scenario where i have 3 rows  and 1 column.  If in    two of the 3 rows i have Integers and in the 3rd row i have a string the dataset column gets typed as a integer and doesn' t bring in the string value.  It works vice versa if in two of the rows i have a string and the third row i ahve a integer the integer doesn't get brought in. 

    Has anyone experienced this and how do i get passed it?

         Protected Function ExcelConnection() As OleDbCommand
            ' Connect to the Excel Spreadsheet
            Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Session("LocalFile") & ";" & _
                        "Extended Properties=Excel 8.0;"
    
            ' create your excel connection object using the connection string
            objXConn = New OleDbConnection(xConnStr)
            objXConn.Open()
    
            ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
            ' the "table name" is the name of the worksheet within the spreadsheet
            ' in this case, the worksheet name is "Sheet1" and is coded as: [Sheet1$]
            Dim objCommand As New OleDbCommand("SELECT  * FROM [Sheet1$]", objXConn)
            Return objCommand
    
        End Function
    
      Private Sub ViewSpreadsheet()
    
            ' Create a new Adapter
            Dim objDataAdapter As New OleDbDataAdapter()
            ' retrieve the Select command for the Spreadsheet
            objDataAdapter.SelectCommand = ExcelConnection()
            ' Create a DataSet
            Dim objDataSet As New DataSet()
            ' Populate the DataSet with the spreadsheet worksheet data
    
            objDataAdapter.Fill(objDataSet)
    
            'GridViewExcel.Attributes.Add("style", "table-layout:fixed")
    
            Dim indColumnCount As Integer
            For indColumnCount = 0 To objDataSet.Tables(0).Columns.Count - 1
                Dim z As New BoundField
                z.DataField = objDataSet.Tables(0).Columns(indColumnCount).ColumnName()
                z.HeaderText = objDataSet.Tables(0).Columns(indColumnCount).ColumnName()            
                GridViewExcel.Columns.Add(z)
            Next
    End sub
    
     
    Tuesday, November 18, 2008 1:30 PM

Answers

  • User1624600859 posted

    try using this

    "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Session("LocalFile") + ";Extended Properties='Excel 8.0;IMEX=1'";

     IMEX=1 is used for mixed data types..

    hope it helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 20, 2008 2:44 AM

All replies

  • User1624600859 posted

    try using this

    "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Session("LocalFile") + ";Extended Properties='Excel 8.0;IMEX=1'";

     IMEX=1 is used for mixed data types..

    hope it helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 20, 2008 2:44 AM
  • User1624600859 posted

     have a look to this article ..it will give you a deep knowledge regarding the same....

     

    http://blog.lab49.com/archives/196

    Thursday, November 20, 2008 2:49 AM
  • User15800025 posted

     Thank you very much this worked great...

    I will read th article now as it might clear some things up for me.

    Rico

     

    Thursday, November 20, 2008 12:18 PM