logo
down
shadow

OleDB Jet - Float issues in reading excel data


OleDB Jet - Float issues in reading excel data

By : hoangduonguet
Date : January 10 2021, 01:45 PM
I hope this helps you . I just tested your data and this method posted here worked.
i.e. the cell value kept it's precision 18.124962832, when put into DataSet.
code :


Share : facebook icon twitter icon
Reading Excel ROW using OleDb data retrieval

Reading Excel ROW using OleDb data retrieval


By : www.quixoticguide.co
Date : March 29 2020, 07:55 AM
it fixes the issue Here's the code I tested with. It appears to work perfectly. If your doesn't then I can only assume that your spreadsheet is not structured quite the same as mine.
code :
string connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string testCaseName = "case_1";
string query = "SELECT * from [Sheet1$] WHERE TestCaseName=\"" + testCaseName + "\"";
DataTable dt = new DataTable();

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn))
    {
        DataSet ds = new DataSet();
        dataAdapter.Fill(ds);
        dt = ds.Tables[0];
    }

    conn.Close();
}
Reading Data using OLEDB from opened Excel File

Reading Data using OLEDB from opened Excel File


By : Susan Gleason
Date : March 29 2020, 07:55 AM
I hope this helps you . Here's an example of using SQL to join data from two ranges: it will work fine if the file is open (as long as it has been saved, because you need a file path).
code :
Sub SqlJoin()

    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim sPath
    Dim sSQL As String

    sSQL = "select a.blah from <t1> a, <t2> b where a.blah = b.blah"

    sSQL = Replace(sSQL, "<t1>", Rangename(Sheet1.Range("A1:A5")))
    sSQL = Replace(sSQL, "<t2>", Rangename(Sheet1.Range("C1:C3")))

    If ActiveWorkbook.Path <> "" Then
      sPath = ActiveWorkbook.FullName
    Else
      MsgBox "Workbook being queried must be saved first..."
      Exit Sub
    End If

    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
                 "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

    oRS.Open sSQL, oConn

    If Not oRS.EOF Then
        Sheet1.Range("E1").CopyFromRecordset oRS
    Else
        MsgBox "No records found"
    End If

    oRS.Close
    oConn.Close

End Sub

Function Rangename(r As Range) As String
    Rangename = "[" & r.Parent.Name & "$" & _
                r.Address(False, False) & "]"
End Function
Reading excel file using OLEDB Data Provider

Reading excel file using OLEDB Data Provider


By : Rajesh Menugu
Date : March 29 2020, 07:55 AM
This might help you You need to set value for TypeGuessRows Registry key to 0, this way driver will set data type based on all column values instead of first 8 (default).
The location of the key differs from version to version of driver, you can easily Google it based on your specific version. For example for Access Connectivity Engine 2007 it would be
code :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
First Column of excel file not reading oledb reader in window application, excel file data not reading

First Column of excel file not reading oledb reader in window application, excel file data not reading


By : Pobb
Date : March 29 2020, 07:55 AM
seems to work fine We need to set IMEX=3 for this bcz header place at first column and row default when we downloading its or write mode which locked first cell .
Reading .xlsx files using OLEDB + Issues with certain cells not loading data

Reading .xlsx files using OLEDB + Issues with certain cells not loading data


By : octopusblonde
Date : March 29 2020, 07:55 AM
it fixes the issue My guess is that there are several cells in the same column above the one(s) with the string value 'MEMBERSHIP' that are numbers. This causes OLEDB to infer that the data in these columns are numeric so non-numeric values will be ignored.
The solution is to include IMEX=1 in the Extended Properties section of your conn string:
shadow
Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk