如何选择DataSet中列的数据类型

发布于 2025-01-03 23:31:57 字数 1499 浏览 4 评论 0原文

作为数据转换的一部分，我需要从 Excel 工作表读取数据。某些列主要包含数字数据，但在 Excel 工作表中的某处可能包含一些字母数字数据。问题是，我的转换将字母数字值视为 null（或空白。使用 .ToString() 方法返回“”）。

为了连接到 Excel，我创建了 oledb 连接，创建了 OleDbDataAdapter，然后使用适配器填充数据集。

下面是用于连接的 VB 代码：

private _oleadpt As OleDbDataAdapter
private  _oleconnection As New OleDbConnection

       Dim olecomm As OleDbCommand                
       '_database comes from a settings file and is the full path to an excel document
        Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _database & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        _oleconnection.ConnectionString = connstring

        olecomm = New OleDbCommand
        olecomm.CommandText = "SELECT RegionalBranch, DocumentType, TiffFileNumberReference, VersionNumber, RTSItemNumber, ItemSearch, HeatNumber, RTSVendorNumber, PurchaseOrderNumber, Branch, " + _
            "Quality, CreationDate, CreationTime, ReceiverNumber, ChathamItemNumber, ChathamVendorNumber, ChathamDivision, Processed FROM [Sheet1$]"
        olecomm.Connection = _oleconnection

        _oleadpt = New OleDbDataAdapter(olecomm)
        Dim commandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(_oleadpt)

        ds = New DataSet
        _oleconnection.Open()
        _oleadpt.Fill(ds)

我认为数据适配器正在根据它查看的第一个多行确定列的数据类型。它决定带有数字的列是数字，这是我的问题的开始。

更改 Excel 中列的格式似乎不会影响我的数据集中的数据类型。

有没有办法告诉数据适配器或数据集列使用什么类型的数据？或者我应该尝试在 SQL 语句中转换数据？

任何帮助将不胜感激！

原文

I need to read data from an Excel sheet as part of a data conversion. Certain columns contain mostly numeric data but may contain some alphanumeric data somewhere in the excel sheet. The problem is, my conversion sees the alphanumeric values as null (or blank. Using the .ToString() method returns "").

To connect to Excel, I'm creating an oledb connection, creating an OleDbDataAdapter, then filling a DataSet with the adapter.

Here's the VB code for connecting:

private _oleadpt As OleDbDataAdapter
private  _oleconnection As New OleDbConnection

       Dim olecomm As OleDbCommand                
       '_database comes from a settings file and is the full path to an excel document
        Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _database & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        _oleconnection.ConnectionString = connstring

        olecomm = New OleDbCommand
        olecomm.CommandText = "SELECT RegionalBranch, DocumentType, TiffFileNumberReference, VersionNumber, RTSItemNumber, ItemSearch, HeatNumber, RTSVendorNumber, PurchaseOrderNumber, Branch, " + _
            "Quality, CreationDate, CreationTime, ReceiverNumber, ChathamItemNumber, ChathamVendorNumber, ChathamDivision, Processed FROM [Sheet1$]"
        olecomm.Connection = _oleconnection

        _oleadpt = New OleDbDataAdapter(olecomm)
        Dim commandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(_oleadpt)

        ds = New DataSet
        _oleconnection.Open()
        _oleadpt.Fill(ds)

I think the data adapter is determining the datatype for the columns based on the first however-many rows it looks at. It decides the columns with numbers are numeric which is the start of my problem.

Changing the formatting of the column in Excel doesn't seem to affect the datatypes in my data set.

Is there a way to tell the dataadapter or dataset what type of data to use for a column? Or should I try to cast the data in my SQL statement?

Any help would be much appreciated!

分享到QQ

分享到微博