asp.net - 从 Excel 导入数据集时为列值插入 NULL

Question

我正在将 excel 数据导入应用程序中的数据表，并面临某些特定列值的问题。

excel表格列中的一些单元格 CustomerUniqID 在角落显示带有绿色标记的警告。

The number is formatted as text or preceded by apostrophe.

当从 Excel 工作表填充数据集时，这些单元格值不会导入并显示空白值。

Dim query As String = "SELECT CINT(CustomerUniqID),[Status] FROM [Sheet1$]"
Dim conn As New OleDbConnection(conStr)
If conn.State = ConnectionState.Closed Then
   conn.Open()
End If
Dim cmd As New OleDbCommand(query, conn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)

我的连接字符串是

<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2'"/>

专栏 CustomerUniqID 包含数字，我无法导入这些单元格值。这个怎么做 ？

Answer 1

如原始帖子中所述，我可以让它失败的唯一方法是，转义/文本单元格是否比我最初测试的单元格更深。 OleDB 不会将 Schema.ini 与 excel 文件一起使用，这太糟糕了，因为这将允许一个非常干净和简单的解决方案，唉......

使用的样本数据:

Country    Capital     Population   Rank
France     Paris       2.25         7
Canada     Toronto     2.5          6
Egypt      Cairo       10.2         9
...

它实际上使用了 16 行，最后 3 个“Rank”项目被转义为文本(例如 '2 )。这些都在 Excel 中显示绿色角落警告标志。

由于 OleDB 不读取/使用模式，因此它从前 N 行(在我的注册表中定义为 8)决定每列的数据类型。当转义单元格不匹配时，它返回一个 DBNull 值。尝试通过 SQL ( CInt , Val ) 转换列失败，因为 OleDB 在可以应用转换之前已经确定那里的数据不匹配。

在某些情况下，我会读两遍。首先将正确数据类型中的“好”列放入一个 DataTable ；然后再次将“脏”列作为文本并手动转换数据。这在数据集中有其他数字列并且您不希望将它们转换为文本/字符串的情况下很有用。

对于发布的案例，如果确实只涉及 2 列，您应该可以使用一个表作为文本读入；并添加一个数字列以接收转换后的值。与其从一个表转换到另一个表，不如从一列转换到另一列。 (请问，如果你想要一个例子，但它只是以下的一个子集)。

在任何一种情况下，“技巧”都是使用不同的连接字符串来强制 OleDB 以文本形式读取数据。显然这需要 HDR=No 和 IMEX=1 ，至少在我的配置中:

Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

此示例/文本代码使用 2 表方法来验证其他数字( Population )没有被转换，只是 Rank :

' ConStr to allow OleDB to guess the datatypes   
Dim TypedConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2';"

' ConStr to force OleDB to read it all as Text 
Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

' get the typed columns into a DT - skip Rank as dirty column
Dim SQL = "SELECT Country, Capital, Population FROM [Capitals$]"
Using con As New OleDbConnection(TypedConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("Pop")
    da.Fill(dsPop.Tables("Pop"))
End Using

' create a new temp DT containing just the naughty column
' use the generic F/Field index in the SQL (we told Ole there was no header)
SQL = "SELECT F4 As RankText FROM [Capitals$]"
' create connection forcing the contents to text:
Using con As New OleDbConnection(TextConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("RankText")
    da.Fill(dsPop.Tables("RankText"))
End Using
' remove the header row
dsPop.Tables("RankText").Rows.RemoveAt(0)

'create a new INT col in Dt(0)
dsPop.Tables("Pop").Columns.Add("Rank", GetType(Int32))

' convert Tbl(1) text to Int and store in Tbl(0) 
For n As Integer = 0 To dsPop.Tables(1).Rows.Count - 1
    dsPop.Tables("Pop").Rows(n).Item("Rank") = 
           Convert.ToInt32(dsPop.Tables("RankText").Rows(n).Item(0).ToString)
Next

'optional: remove the [RankText] tbl since we are done with it
dgv.DataSource = dsPop.Tables("Pop")

' report the datatype of the last row rank:
tbDataType.Text = dsPop.Tables("Pop").Rows(14).Item("Rank").GetType.ToString

在即时窗口中，报告的类型与预期的一样:

? dspop.Tables("Pop").Rows(0).Item(2)       ' (population - paris)
2.25 {Double}
? dspop.Tables("RankText").Rows(0).Item(0)  ' temp table text
"7" {String}
? dspop.Tables("Pop").Rows(0).Item(3)       ' converted, merged value
7 {Integer}

对我来说，OleDB 会自动将 '3 转换为 "3" 。换句话说，它在转换为文本时省略了前导刻度/撇号。由于 Excel 版本与 OleDB.ACE 和 OleDb.Jet 的组合可能产生多种可能性，我们可能需要一个后备转换器(我是在意外向 Excel 添加反引号后写的，也许它对某人有值(value)) :

Private Function GetNumericValue(s As String) As Integer
    ' ToDo add exception handling
    If Char.IsDigit(s(0)) Then
        Return Convert.ToInt32(s)
    Else
        Return Convert.ToInt32(
            New String(s.ToCharArray(1, s.Length - 1))
            )
    End If
End Function

它只会检查非数字的第一个字符，否则它可能会将 "1234 Main Street Suite 56" 转换为 123456，这可能是不可取的。结果:


俄罗斯、日本和葡萄牙是 Rank 数据以文本形式转义的行。

资源:

Excel Connection Strings

Excel Values Returned as NULL Using DAO

IMEX entry MSDN msg board

ConnectionStrings.com entry about IMEX