OleDB &混合 Excel 数据类型:缺失数据

问题描述

我有一个 Excel 工作表，我想将其读入数据表 - 除了 Excel 工作表中的一个特定列外，一切都很好.ProductID"列是 ########## 和 n######### 等值的混合.

I have an Excel worksheet I want to read into a datatable - all is well except for one particular column in my Excel sheet. The column, 'ProductID', is a mix of values like ########## and n#########.

我试图让 OleDB 自动处理所有事情，将其读入数据集/数据表，但任何值像 n###### 这样的ProductID"缺失、忽略并留空.我尝试通过使用数据读取器遍历每一行来手动创建我的数据表，但结果完全相同.

I tried to let OleDB handle everything by itself automatically by reading it into a dataset/datatable, but any values in 'ProductID' like n###### are missing, ignored, and left blank. I tried manually creating my DataTable by looping through each row with a datareader, but with the exact same results.

代码如下:

// add the column names manually to the datatable as column_1, column_2, ...
for (colnum = 0; colnum < num_columns; colnum ++){
  ds.Tables["products"].Columns.Add("column_" +colnum , System.Type.GetType("System.String"));
}
while(myDataReader.Read()){
  // loop through each excel row adding a new respective datarow to my datatable
  DataRow a_row = ds.Tables["products"].NewRow();
  for (col = 0; col < num_columns; col ++){
    try {  a_row[col] = rdr.GetString(col);  }
    catch {  a_row[col] = rdr.GetValue(col).ToString(); }
  }
  ds.Tables["products"].Rows.Add(a_row);
}

我不明白为什么它不让我读取像 n###### 这样的值.我怎样才能做到这一点?

I don't understand why it won't let me read in values like n######. How can I do this?

推荐答案

使用 .Net 4.0 并读取 Excel 文件时，我遇到了与 OleDbDataAdapter 类似的问题 - 即读取混合数据类型MS Excel 中的PartID"列，其中 PartID 值可以是数字(例如 561)或文本(例如 HL4354)，即使 Excel 列的格式为文本".

Using .Net 4.0 and reading Excel files, I had a similar issue with OleDbDataAdapter - i.e. reading in a mixed data type on a "PartID" column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as "Text".

据我所知，ADO.NET 根据列中的大多数值选择数据类型(与数字数据类型并列).即，如果样本集中的大多数 PartID 是数字，ADO.NET 将声明该列为数字.因此，ADO.Net 将尝试将每个单元格转换为一个数字，这对于文本"PartID 值将失败，并且不会导入那些文本"PartID.

From what I can tell, ADO.NET chooses the data type based on the majority of the values in the column (with a tie going to numeric data type). i.e. if most of the PartID's in the sample set are numeric, ADO.NET will declare the column to be numeric. Therefore ADO.Net will attempt to cast each cell to a number, which will fail for the "text" PartID values and not import those "text" PartID's.

我的解决方案是将 OleDbConnection 连接字符串设置为使用 Extended Properties=IMEX=1;HDR=NO 来指示这是一个导入并且表将不包括标题.excel 文件有一个标题行，所以在这种情况下告诉 ado.net 不要使用它.然后在代码的后面，从数据集中删除该标题行，瞧，该列的数据类型是混合的.

My solution was to set the OleDbConnection connectionstring to use Extended Properties=IMEX=1;HDR=NO to indicate this is an Import and that the table(s) will not include headers. The excel file has a header row, so in this case tell ado.net not to use it. Then later in the code, remove that header row from the dataset and voilà you have mixed data type for that column.

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close();

//现在您可以使用 LINQ 搜索字段

// now you can use LINQ to search the fields

    var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });

这篇关于OleDB &混合 Excel 数据类型:缺失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！