OleDB和混合Excel数据类型：缺失数据

Question

我有一个Excel工作表，想要将其读入datatable中 - 除了Excel表格中的一个特定列外，一切都很顺利。该列名为“ProductID”，是一个值的混合，例如##########和n#########。

我尝试通过将其读入数据集/ datatable来让OleDB自动处理一切，但是“ProductID”中的任何n######值都会丢失，被忽略并保持为空白。我尝试使用数据阅读器手动创建DataTable，但结果完全相同。

以下是代码：

// 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###### 这样的值。我该怎么做？

- rlb.usa

Answer 1

我正在使用 .Net 4.0 读取 Excel 文件，使用 OleDbDataAdapter 时遇到了类似的问题。即在 MS Excel 中读取 "PartID" 列中的混合数据类型，其中 PartID 值可以是数字（例如 561）或文本（例如 HL4354），即使该 Excel 列已经格式化为 "Text"。

据我所知，ADO.NET 根据列中大多数值的数据类型来选择数据类型（如果平局则选择数字数据类型）。也就是说，如果样本集中大多数 PartID 都是数字，则 ADO.NET 将声明该列为数字。因此，ADO.Net 将尝试将每个单元格转换为数字，并且无法导入这些 "text" PartID 的 "text" 值。

我的解决方法是将 OleDbConnection 连接字符串设置为使用 Extended Properties=IMEX=1;HDR=NO 来指示这是一个导入过程，而且表中不包括标头。Excel 文件有一个标题行，因此在这种情况下告诉 ado.net 不要使用它。然后在代码中稍后删除数据集中的标题行，就可以得到该列的混合数据类型。

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 来搜索字段

    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")
                });

- Brian Wells

---

"Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text"使某人大获成功。 - TheVillageIdiot

似乎使用ACE OleDb驱动程序连接Excel 12.0时，无法在连接字符串中设置ImportMixedTypes。 - Ludington

实际上，“Extended Properties=IMEX=1;”可以解决问题。不需要包含“HDR=NO”。但还是谢谢。 - Solo

@Solo - 你的解决方案对我也起作用了！在一个表格中，办公室警告说“此单元格中的数字格式为文本或以撇号开头”，使用 Extended Properties=\"Excel 8.0;IMEX=1;\"; 解决了这个问题。 - BornToCode

为了避免出现可安装的ISAM错误，您需要在Extended Properties内容周围添加引号。Extended Properties="Excel8.0;IMEX=1" - Mido

Answer 2

我发现几个论坛声称在连接字符串的扩展属性中添加IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text可以解决这个问题，但事实并非如此。最终，我通过在连接字符串的扩展属性中添加"HDR=NO"（如Brian Wells所示）来解决了这个问题，以便我可以导入混合类型。

然后，我添加了一些通用代码来命名第一行数据后的列，然后删除第一行。

    public static DataTable ImportMyDataTableFromExcel(string filePath)
    {
        DataTable dt = new DataTable();

        string fullPath = Path.GetFullPath(filePath);

        string connString =
           "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=\"" + fullPath + "\";" +
           "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

        string sql = @"SELECT * FROM [sheet1$]";

        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString))
        {
            dataAdapter.Fill(dt);
        }

        dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

        return dt;
    }

    private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt)
    {
        DataRow firstRow = dt.Rows[0];

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if(!string.IsNullOrWhiteSpace(firstRow[i].ToString())) // handle empty cell
              dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
        }

        dt.Rows.RemoveAt(0);

        return dt;
    }

- user1424725

---

访问Excel电子表格中的数据存在诸多问题，这引发了一个明显的问题：为什么微软不提供一种更现代（且性能更好）的机制来获取数据，而是使用糟糕的Jet驱动程序？有很多第三方工具比微软提供的任何东西都做得更好。 - 3Sphere

@3Sphere，你知道有什么可以读取XLS文件的工具吗？我尝试了几个工具，但它们只能读取新版本的XLSX文件，真是让人恼火。 - Simon_Weaver

@Simon_Weaver 这个讨论串中提到的机制虽然笨重，但是提供了一种可靠（但极其低效）的从XLS文件中提取数据的方法。如果你需要更快的东西，你要么得编写自己的解析器，要么就得购买第三方组件（如Spreadsheet Gear）。 - 3Sphere

这种技术效果很好，而且比其他答案更通用 - 但是如果文件在第一行的2个单元格中有重复数据，它会抛出异常。我提出了一个修订意见，但被拒绝了，所以如果要复制这个解决方案，请小心！ - Amos Zoellner

Answer 3

没问题sh4，很高兴对混合类型问题有所帮助。

我记得DateTime列是个完全不同的问题，过去曾经让我头疼...... 我们处理的一个Excel文件有时候会将日期转换成双精度数据类型（显然，Excel将日期存储为双精度浮点数，表示自1900年1月0日以来经过的天数）。

解决方法是使用：

OleDbConnection mobjExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelFile.Text + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=Yes;""");

OleDbDataAdapter mobjExcelDataAdapter = new OleDbDataAdapter("Select * from [" + txtSheet.Text + "$] where [Supplier ID] <> '' ", mobjExcelConn);


DateTime dtShipStatus = DateTime.MinValue;
shipStatusOrig = excelRow["Est Ship Date"].ToString(); // excelRow is DataRow in the DataSet via the OleDbDataAdapter             

if (shipStatusOrig != string.Empty)
{
    // Date may be read in via oledb adapter as a double
    if (IsNumeric(shipStatusOrig))
    {
        double d = Convert.ToDouble(shipStatusOrig);
        dtShipStatus = DateTime.FromOADate(d);

        if (DateTime.TryParse(dtShipStatus.ToString(), out dtShipStatus))
        {
            validDate = true;
            Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
        }
    }
    else
    {
        if (ValidateShipDate(shipStatusOrig))
        {
            dtShipStatus = DateTime.Parse(shipStatusOrig);
            validDate = true;
            Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
        }
        else
        {
            validDate = false;
            MessageBox.Show("Invalid date format in the Excel spreadsheet.\nLine # " + progressBar1.Value + ", the 'Ship Status' value '" + shipStatusOrig + "' is invalid.\nDate should be in a valid date time format.\ne.g. M/DD/YY, M.D.Y, YYYY-MM-DD, etc.", "Invaid Ship Status Date");
        }
    }
...
}
        public static Boolean IsNumeric (Object Expression)
        {
            if(Expression == null || Expression is DateTime)
                return false;

            if(Expression is Int16 || Expression is Int32 || Expression is Int64 || Expression is Decimal || Expression is Single || Expression is Double || Expression is Boolean)
                return true;

            try
            {
                if(Expression is string)
                    Double.Parse(Expression as string);
                else
                   Double.Parse(Expression.ToString());
                return true;
            } catch {} // just dismiss errors but return false

            return false;
        }

        public bool ValidateShipDate(string shipStatus)
        {
            DateTime startDate;
            try
            {
                startDate = DateTime.Parse(shipStatus);
                return true;
            }
            catch
            {
                return false;
            }
        }

- Brian Wells

Answer 4

处理混合数据类型和Excel有两种方法。

方法1

• 打开Excel电子表格并手动设置列格式为所需格式。在本例中，是“文本”格式。

方法2

• 有一个"技巧"，可以在连接字符串末尾添加“IMEX=1”：

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myfile.xls;Extended Properties=Excel 8.0;IMEX=1

• 这将尝试根据您在注册表中设置的方式处理混合的Excel格式。对于本地设置，您可以进行设置，但对于服务器来说，这可能不是一个选项。

- rlb.usa

---

它报错了： System.Data.OleDb.OleDbException 未被用户代码处理 Message="无法找到可安装的 ISAM。" Source="Microsoft JET Database Engine" ErrorCode=-2147467259 - Pratik

我知道这个帖子现在有点老了，但我和Pratik一样遇到了同样的问题... 我无法指定IMEX=1，因为出现了“找不到可安装的ISAM”错误 :( - Simon Dugré

为了避免出现可安装的 ISAM 错误，您需要在“Extended Properties”内容周围添加引号。 Extended Properties="Excel8.0;IMEX=1" - Jake1164

我简直不敢相信我要读到这么远才能找到最明显的解决方案。我曾经认为这可能有效，但出于某种原因从未尝试过。这是最好的解决方案：打开Excel电子表格并手动将列格式设置为所需格式。在这种情况下，是“文本”。稍后我可能会发布一些VBA代码来自动化此过程。 - user1274820

Answer 5

@Brian Wells 谢谢，你的建议解决了问题，但还不完全……对于混合字段 int-string 来说是有效的，但 datetime 列在此之后却出现了奇怪的字符，所以我又做了一个“hack”来解决这个问题。

1. 使用 System.Io.File.Copy 创建 Excel 文件的副本。

2. 在运行时以编程方式修改 Datetime 列标题为日期时间格式，例如“01/01/0001”。

3. 保存 Excel 文件，然后使用 HDR=NO 对修改后的文件进行查询，再应用你的技巧。

虽然有点复杂，但是它有效而且相对快速。如果有其他方法可以替代，请告诉我，我会很高兴听取建议。

祝好。

P.S. 请原谅我的英语，它不是我的母语。

- sh4

---

没问题，很高兴能帮到你！我会在这篇帖子中发布我之前在另一个答案中使用过的DateTime解决方案（这里提供的字符不够）。 - Brian Wells

Answer 6

快捷方式 --> 如果你在Excel中有一个混合类型的列：将你的列按Z到A排序

我基本上浏览了这里所有的答案，其中一些对我有效，一些则不是。然而，对我来说没有一个是理想的，因为某种原因ADO没有获取我在Excel文件中拥有的混合类型列中的数据。我必须设置HDR=NO才能让ADO读取我的电子表格列，该列是文本和数字的混合，这样我就失去了在我的SQL语句中使用列标题的能力，这是不好的。如果Excel文件中的列顺序改变，SQL语句将导致错误或错误的输出。

在混合数据类型列中，关键是前8行。ADO根据前8行确定列的数据类型因此，如果您仍然希望使用扩展参数修改连接字符串，请在通过ADO读取数据之前在Excel文件中将您的列按Z到A排序，这样顶部的行就是文本行，然后您的列将被选择为文本。

如果您的初始行是数字（无论在Excel中您的列是否设置为格式为TEXT），ADO将确定该列为数值类型，因此一旦它读取下面的文本行，它就无法将其转换为数字。相反，如果该列被确定为文本，则如果任何行是数字，则可以将其转换为文本。

- Ibo