• PRB: Excel Values Returned as NULL Using DAO OpenRecordset


     

    SYMPTOMS

    When connecting to an Excel Spreadsheet using the DAO OpenRecordset method, some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.
     

    CAUSE

    This problem is caused by a limitation of the Excel ISAM driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling.
     

    RESOLUTION

    There are two workarounds for this behavior:
    1. Insure that the data in Excel is entered as text. Just reformatting the Excel column to Text will not accomplish this. You must re-enter the existing values after reformatting the Excel column. In Excel, you can use F5 to re-enter existing values in the selected cell.
    2. You can add the option IMEX=1; to the Excel connect string in the OpenDatabase method. For example:
         Set Db = OpenDatabase("C:\Temp\Book1.xls", _
                
    FalseTrue"Excel 8.0; HDR=NO; IMEX=1;")


    NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

    You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

    The possible settings of IMEX are:

      0 is Export mode
            
    1 is Import mode
            
    2 is Linked mode (full update capabilities)


    The registry key where the settings described above are located is:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

    See the REFERENCES section of this article for information on when the Excel spreadsheet has text column headers with numeric data.

     

    STATUS

    This behavior is by design.
     

    MORE INFORMATION

    Steps to Reproduce Behavior

    To duplicate this problem, first create an Excel Workbook with a default Sheet1 spreadsheet. In the first column of Sheet1, enter the following values - 123, aaa, 456, bbb, 789. Save this Workbook in your C:\Temp directory, and name it Book1.XLS.

    In Visual Basic, create a new Standard EXE project and follow these steps:
    1. Make a reference to Microsoft DAO 3.5 Object Library. In Visual Basic 6.0, this will be Microsoft DAO 3.51 Object Library.
    2. Add a CommandButton to the new Form.
    3. Place the following code in the Form's General Declarations section:
         Dim Db As Database
          
    Dim Rs As Recordset

          
    Private Sub Command1_Click()
              
    Set Rs = Db.OpenRecordset("Sheet1$")
              
    'This will print the spreadsheet Text values as Nulls.

              
    Do While Not Rs.EOF
                  Debug.Print Rs(
    0)
                  Rs.MoveNext
              
    Loop

          
    End Sub

          
    Private Sub Form_Load()
              
    'HDR refers to the Excel header row.
          Set Db = OpenDatabase("C:\Temp\Book1.xls", _
                   
    FalseTrue"Excel 8.0; HDR=NO;")

          
    End Sub

          
    Private Sub Form_Unload(Cancel As Integer)
              Db.Close
              
    Set Db = Nothing

          
    End Sub


    Run the Project by pressing the F5 key and note that in the Debug window the text values are printed as Null. If the majority of the values in the Excel Spreadsheet were text, then the result from the code above would be reversed. That is, the numeric values would come back as Nulls.

     
    喜欢请赞赏一下啦^_^
  • 相关阅读:
    CKEDITOR最新版不能上传图片的解决
    Java Web开发之Servlet获取ckeditor内容
    『实践』Java Web开发之分页(ajax)
    Java开发之JSP行为
    [Wpf学习] 1.传说中的Main
    直接使用汇编编写 .NET Standard 库
    ASP.NET CORE 启动过程及源码解读
    使用EventBus + Redis发布订阅模式提升业务执行性能(下)
    Android 实现浏览器跳转APP应用,网页也可以跳转APP
    Python全栈(七)Flask框架之1.Flask简介与URL和视图介绍
  • 原文地址:https://www.cnblogs.com/amadeuslee/p/3744713.html
Copyright © 2020-2023  润新知