Reading Excel Data
-
I have the following vb.net code to read the excel data into a dataset. The code works fine and the excel data is converted to a dataset. If the excel file has a data in the same column in which some numbers are formatted as text (eg. 080) and some numbers are not formatted as text but formatted as numbers (eg. 100.01) then the dataset contains empty values for the ones that are formatted as numbers. Is there anyway to get those values as text? Thanks, Bharathy. Public Shared Function GetDataSetFromExcel(ByVal excelFileName As String) As DataSet Dim con As OleDbConnection Dim dt As DataTable Dim conStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;" con = New OleDbConnection(conStr) Try con.Open() dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) If (dt IsNot Nothing) Then Dim excelSheetNames(dt.Rows.Count) As String Dim i As Integer = 0 For Each row As DataRow In dt.Rows excelSheetNames(i) = row("TABLE_NAME").ToString() i = i + 1 Next Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" + excelSheetNames(0) + "]", con) Dim myDataSet As New DataSet() myCommand.Fill(myDataSet) Return myDataSet Else Return Nothing End If Catch ex As Exception Throw (ex) Finally con.Close() End Try End Function
-
I have the following vb.net code to read the excel data into a dataset. The code works fine and the excel data is converted to a dataset. If the excel file has a data in the same column in which some numbers are formatted as text (eg. 080) and some numbers are not formatted as text but formatted as numbers (eg. 100.01) then the dataset contains empty values for the ones that are formatted as numbers. Is there anyway to get those values as text? Thanks, Bharathy. Public Shared Function GetDataSetFromExcel(ByVal excelFileName As String) As DataSet Dim con As OleDbConnection Dim dt As DataTable Dim conStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;" con = New OleDbConnection(conStr) Try con.Open() dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) If (dt IsNot Nothing) Then Dim excelSheetNames(dt.Rows.Count) As String Dim i As Integer = 0 For Each row As DataRow In dt.Rows excelSheetNames(i) = row("TABLE_NAME").ToString() i = i + 1 Next Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" + excelSheetNames(0) + "]", con) Dim myDataSet As New DataSet() myCommand.Fill(myDataSet) Return myDataSet Else Return Nothing End If Catch ex As Exception Throw (ex) Finally con.Close() End Try End Function
It sounds like this is an issue with the Excel driver attempting to guess the data type for the column. The driver has probably made the decision, based on sampling an initial number of rows (default is 8) that the column should contain text values. Values that look like they belong to another data type will be disregarded and show up as NULL. If you add the element 'IMEX=1' to your connection string this tells the driver to treat intermixed data in a column as text (see here[^]) If you need to increase the number of rows that the driver uses to guess the data type for a column, you need to make a registry change. See this article[^] for more information.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
It sounds like this is an issue with the Excel driver attempting to guess the data type for the column. The driver has probably made the decision, based on sampling an initial number of rows (default is 8) that the column should contain text values. Values that look like they belong to another data type will be disregarded and show up as NULL. If you add the element 'IMEX=1' to your connection string this tells the driver to treat intermixed data in a column as text (see here[^]) If you need to increase the number of rows that the driver uses to guess the data type for a column, you need to make a registry change. See this article[^] for more information.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Thanks a lot for your help. IMEX=1 did the trick.