Read excel file in VB.Net but forcing all cols as string format or as object

Question

_{Tuesday, October 15, 2013 9:14 AM}

Hi all,

I am facing to the following issue.

I read an excel file with some cols where the 200 first one rows contains numeric value, but after that some are no more numeric. When reading using oledb, the non numeric values are not retrieved.

Is there a possibility to read the excel file with all cols with a default format (string or object)?

Thanks for your ideas and help.

Patrick 

Pat

All replies (5)

_{Tuesday, October 15, 2013 10:37 AM ✅Answered | 1 vote}

Hello,

What is happening is default behavior for Excel, although this can be changed via registry settings which I do not recommend you are better off if possible to use Excel automation to retrieve data when your column has mixed data types. 

References

Code Project article for working Excel data via OleDb, make sure to read the section on Extended properties.

My MSDN article on Excel automation and MSDN article on getting last row and column which most likely be needed.

Another alternate method is Open XML if reading Excel 2007 or higher.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

_{Tuesday, October 15, 2013 2:37 PM ✅Answered | 1 vote}

When using the OLEDB/ODBC provider/driver the only way for the Excel driver to correctly identify the data type for each column is to change the TypeGuessRows setting in the Registry to 0. This will cause the driver to scan all rows in the Worksheet, rather than just the first eight (or whatever is specified) in order to determine the data type.

There can be several entries for TypeGuessRows, depending on whether you are using 32-bit/64-bit Windows and Jet or ACE OLEDB.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Tuesday, October 15, 2013 9:19 AM | 1 vote}

Hi Patrick,

Please try using IMEX=1 in the your excel connection string as below

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"""This attribute hints the reader to read all values as text.Thanks,Amit TonkPlease mark this as answer if this post help you.

_{Tuesday, October 15, 2013 10:09 AM}

Hi Amit,

As you can see in the code behind, I already use IMEX code, but it doesn't seem to be ok.

Public Function ReadExcelIntoDataset(ByVal file As String, ByVal sheetName As String) As Data.DataTable
        Dim dsExcel As New System.Data.DataTable
        'First name sure the file exists
        If Not IO.File.Exists(file) Then
            MessageBox.Show("The file provided doesn't exist. Please check the name and try again")
            Return dsExcel
        Else
            Try
                'Declare the variables needed for the reading of the Excel file
                Dim sOleDbString As New String("SELECT * FROM  [" & sheetName & "]")
                'Create our connection to the Excel sheet
                Dim strCon As String
                If Right(file, 3) = "xls" Then
                    strCon = String.Format(CultureInfo.CurrentCulture, "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes;IMEX=1" & Chr(34) & ";", file)
                Else
                    strCon = String.Format(CultureInfo.CurrentCulture, "Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes;IMEX=1" & Chr(34) & ";", file)
                End If
                Dim cnExcel As OleDbConnection = New Data.OleDb.OleDbConnection(strCon)
                'Create our command object
                Dim cmdExcel As New Data.OleDb.OleDbDataAdapter(sOleDbString, cnExcel)
                'Now we try to fill the DataSet with the data from the Excel file
                Try
                    'Fill was successful
                    cmdExcel.Fill(dsExcel)
                    'Catch any errors that have occurred
                Catch ex As Exception
                    Dim myErrorlogger As New ErrorLogger
                    Dim actualMethod, calledby As String
                    Dim sf As StackFrame = New StackFrame()
                    Dim mb As MethodBase = sf.GetMethod()
                    actualMethod = mb.Name
                    Dim st As StackTrace = New StackTrace()
                    sf = st.GetFrame(1)
                    mb = sf.GetMethod()
                    calledby = mb.Name
                    IsInError = True
                    myErrorlogger.WriteToErrorLog(ex.Message & StrSql & "_" & ex.ToString & "_" & _
                        ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7), ex.StackTrace, "Error in " & actualMethod & " Called by")
                    IsInError = True
                    dsExcel = Nothing
                    'Finally close the connection

Pat

_{Tuesday, October 15, 2013 11:47 AM}

Hi pasand

Read excel file you may use LinqToExcel it's freely available open source dll you may refer that in net and use in your project it's very easy and straight forward.

Regards

Sarathi.R

If you get your question answered, please come back and mark the reply as an answer.
If you are helped by an answer to someone else's question, please mark it as helpful.

By Sarathi