Excel批量导入SQL Server时ISBN列含字母值变为NULL问题

问题说明 #

我尝试将Excel表格内容批量复制到SQL Server表中，Excel中的ISBN列多数值为整数数字，但偶尔会包含X这类字母。完成批量复制后发现，含字母的ISBN值全部变为NULL。SQL Server表中该列已设置为varchar类型，也尝试过调整Excel单元格的多种格式（常规、数字、文本、自定义），但问题依旧，无字母的ISBN导入正常且无报错。

导入代码 #

// 导入Excel文件
public class ExcelImport
{
    private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
    private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";

    public void Import(string filePath)
    {
        string file = filePath;
        string extension = Path.GetExtension(file);
        string conString = "";
        string sheetName = "";

        switch (extension) 
        {
            case ".xls":
                conString = string.Format(Excel03ConString, filePath, "YES");
                break;

            case ".xlsx":
                conString = string.Format(Excel07ConString, filePath, "YES");
                break;
        }

        using (OleDbConnection conn = new(conString))
        {
            using (OleDbCommand cmd = new())
            {
                cmd.Connection = conn;
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                sheetName = dt.Rows[0]["Table_Name"].ToString();
                conn.Close();
            }
        }

        using (OleDbConnection conn = new(conString))
        {
            using (OleDbCommand cmd = new())
            {
                OleDbDataAdapter oda = new();
                cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                conn.Open();
                oda.SelectCommand = cmd;
                DataTable dt = new();
                oda.Fill(dt);
                conn.Close();
                InsertRecords(dt);
            }
        }
    }

    private async void InsertRecords(DataTable imported)
    {
        string truncateQuery = "DELETE FROM Base";

        SqlConnection conn = new DbConnection().GetConnection();

        using(conn)
        {
            SqlCommand sqlCmd = new SqlCommand(truncateQuery, conn);
            await conn.OpenAsync();
            await sqlCmd.ExecuteNonQueryAsync();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.DestinationTableName = "dbo.Base";

                bulkCopy.ColumnMappings.Add("ISBN", "ISBN"); // 带X的ISBN会变成NULL的列
                bulkCopy.ColumnMappings.Add("Title", "Title");

                await bulkCopy.WriteToServerAsync(imported);
                await conn.CloseAsync();
            }
        }
    }
}

解决方法 #

• 修改OLEDB连接字符串，强制识别混合类型列为文本：在Extended Properties里添加IMEX=1，该参数会让驱动把包含混合数据类型的列当成文本处理，避免因多数值是数字就推断为数值类型。修改后的连接字符串如下：
  • Excel 03：Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'
  • Excel 07+：Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'
• 确保Excel单元格格式正确生效：设置ISBN列为文本格式后，需重新编辑带字母的单元格（比如双击单元格再回车），或使用“数据”选项卡的“分列”功能，最后一步选择文本格式，确保Excel真正将这些值存储为文本。
• 读取时强制指定ISBN列类型：如果上述方法无效，可在查询Excel时显式把ISBN列转换为文本，比如把查询语句改成SELECT CStr(ISBN) AS ISBN, Title FROM [" + sheetName + "]，强制将ISBN转为字符串后再读取。

内容的提问来源于stack exchange，提问作者Darth Nihilus