从Excel导入 - 非数字值被忽略 [英] Importing from Excel - non-numeric values are ignored

问题描述

我有code，从指定格式的Excel表单进口。在一列，大多数数据是数字，但非数字值也是present。非数值由进口code忽视，出于某种原因。

I have code that imports from Excel sheets of a specified format. In one of the columns, most data is numeric, but non-numeric values are also present. The non-numeric values are ignored by import code, for some reason.

ConnectionString中看起来是这样的：

The connectionstring looks like this:

    Dim FileConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Path & "\" & _
    Filename & ";Extended Properties=" & _
    """Excel 12.0;HDR=YES;IMEX=1;"""

实际导入code看起来是这样的：

The actual import code looks something like:

    Dim Factory As DbProviderFactory = _
    DbProviderFactories.GetFactory("System.Data.OleDb")

    Dim Adapter As DbDataAdapter = Factory.CreateDataAdapter(), _
    DataObject As New DataSet

    Using Connection As DbConnection = Factory.CreateConnection
        Connection.ConnectionString = FileConnectionString

        Using Command As DbCommand = Connection.CreateCommand

            Command.CommandText = _
            "SELECT [Column1], [Column2]" & _
            "FROM [Sheet1$]"

            Adapter.SelectCommand = Command

            Adapter.Fill(DataObject)

            ...

请注意，我已经启用的 IMEX = 1 的通知Excel中混合数据将是present。这似乎并没有帮助。任何想法是怎么回事？

Please note that I have enabled IMEX = 1 to inform Excel that mixed data will be present. This doesn't seem to help. Any idea what's going on?

推荐答案

默认情况下的Excel基地的第一个8行的数据列中的数据类型。要改变这一点，你需要更新注册表项：

By default Excel bases the data type for a column on the first 8 rows of data. To change this, you need to update the registry key:

HKLM \\软件\\微软\\办公室\\ 12.0 \\访问连接引擎\\引擎\\ Excel中

HKLM\Software\Microsoft\Office\12.0\Access Connect Engine\Engines\Excel

与您希望Excel扫描行数。将值设置为0有它扫描所有的行。请注意，它设置为零可以减缓Excel加载了一点，尤其是在大S preadsheets。

with the number of rows you want Excel to scan. Set the value to 0 to have it scan all the rows. Note that setting it to zero can slow the Excel loads down a bit, especially on large spreadsheets.

此外，如果您还使用Excel 2003中，你需要更新第二个注册表项：

Also, if you are also using Excel 2003, you need to update a second registry key:

HKLM \\ SOFTWARE \\微软\\喷气\\ 4.0 \\发动机\\ Excel中

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

这篇关于从Excel导入 - 非数字值被忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！