CsvHelper.CsvReader读取CSV失败，报无表头记录错误

问题描述 #

点击toSqlButton按钮时，程序尝试将pathInputTextBox指定路径的CSV文件内容追加到SQL Server的tableInputTextBox指定表中，但读取CSV阶段失败，报错：

CsvHelper.ReaderException: 'No header record was found.
IReader state:
ColumnCount: 0
CurrentIndex: -1
HeaderRecord:

错误出现在toSQLButton_Click事件的csv.ReadHeader();行，调试发现ReadCSV方法返回null。已尝试调整文件读取方式、CsvHelper的分隔符和表头配置，均无效，确认文件路径正确。

相关代码 #

private CsvReader ReadCSV(string pathString)
{
    try
    {
        var reader = new StreamReader(pathString);
        CsvReader csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture) { Delimiter = ";", HasHeaderRecord = true });
        return csv;
    }
    catch (FileNotFoundException)
    {
        MessageBox.Show("File not found. Please check the entered file path.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (Exception ex)
    {
        MessageBox.Show("An error occurred while reading the CSV file: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    return null; 
}

private void toSqlButton_Click(object sender, EventArgs e)
{
    string sqlConnection = "Data Source=VERYCOOLDATASOURCE\\SQLEXPRESS;Initial Catalog=VeryCoolDB;Integrated Security=True";
    CsvReader csv = ReadCSV(pathInputTextBox.Text);
    using(SqlConnection sqlconn = new SqlConnection(sqlConnection))
    {
        // Get CSV column names for comparison
        csv.ReadHeader();
        List<string> csvColumns = csv.HeaderRecord.ToList() ?? new List<string>();

        // Get SQL column names for comparison
        List<string> sqlColumns = GetSQLColumns(sqlConnection, $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableInputTextBox.Text}'");

        // Compare columns
        if (csvColumns.All(sqlColumns.Contains) && csvColumns.Count == sqlColumns.Count)
        {
            try
            {
                sqlconn.Open();
                using (SqlTransaction sqltrans = sqlconn.BeginTransaction())
                using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.KeepIdentity, sqltrans))
                {
                    sqlcopy.DestinationTableName = tableInputTextBox.Text;
                    CsvDataReader csvdata = new CsvDataReader(csv);
                    sqlcopy.WriteToServer(csvdata);
                    sqltrans.Commit();
                }
                sqlconn.Close();
            }
            catch (Exception ex) 
            {
                MessageBox.Show("An error occurred while writing the data: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        else
        {
                MessageBox.Show("Columns of the CSV file and columns of the SQL table do not match.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

static List<string> GetSQLColumns(string connectionString, string query) 
{
    List<string> columns = new List<string>();
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            columns.Add(reader["COLUMN_NAME"].ToString());
        }

        reader.Close();
        connection.Close();
    }
    return columns; 
}

CSV内容 #

mitid; vname; nname; gehalt; geburtsdatum
5; bingus; bongus; 1223.23; 2010-01-01;
6; bongus; bingus; 1223.23; 2010-01-01;

文件路径 #

C:\Users\MALA\Desktop\AppendExcelTest.csv

排查与修复方案 #

1. 修复ReadCSV方法的资源管理与空值问题 #

当前方法未正确释放StreamReader资源，且未提前验证CSV内容有效性，导致返回null后后续调用报错。修改后：

private CsvReader ReadCSV(string pathString)
{
    try
    {
        // 指定编码避免读取乱码，用using确保资源释放
        using var reader = new StreamReader(pathString, Encoding.UTF8);
        var config = new CsvConfiguration(CultureInfo.InvariantCulture) 
        { 
            Delimiter = ";", 
            HasHeaderRecord = true,
            // 自动去除表头和字段的多余空格
            TrimOptions = TrimOptions.TrimHeaders | TrimOptions.TrimFields
        };
        var csv = new CsvReader(reader, config);
        
        // 提前读取一行验证文件是否有内容
        if (!csv.Read())
        {
            MessageBox.Show("CSV文件为空或无有效内容", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null;
        }
        // 重置读取位置到表头
        csv.Context.Reader.BaseStream.Position = 0;
        return csv;
    }
    catch (FileNotFoundException)
    {
        MessageBox.Show("未找到文件，请检查输入的路径", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (Exception ex)
    {
        MessageBox.Show("读取CSV文件时出错：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    return null; 
}

2. 新增空值判断避免空引用异常 #

在toSqlButton_Click中，使用csv前先判断是否为null：

private void toSqlButton_Click(object sender, EventArgs e)
{
    string sqlConnection = "Data Source=VERYCOOLDATASOURCE\\SQLEXPRESS;Initial Catalog=VeryCoolDB;Integrated Security=True";
    CsvReader csv = ReadCSV(pathInputTextBox.Text);
    
    // 新增：如果csv为null，直接终止流程
    if (csv == null)
    {
        return;
    }
    
    using(SqlConnection sqlconn = new SqlConnection(sqlConnection))
    {
        // ... 原有逻辑 ...
    }
    
    // 释放CsvReader关联的流资源
    csv.Context.Reader.Dispose();
}

3. 修复CSV格式问题 #

你的CSV内容中，分隔符后存在多余空格（如mitid; vname），且行尾有多余分号，这会干扰CsvHelper的表头识别。上面的配置中已开启TrimOptions自动处理空格，也可以手动清理CSV文件：

• 去掉分隔符后的空格，改为mitid;vname;nname;gehalt;geburtsdatum
• 去掉行尾的多余分号，改为5;bingus;bongus;1223.23;2010-01-01

4. 修复SQL查询的注入风险与正确性 #

原查询使用字符串拼接存在注入风险，且未指定表架构，修改为参数化查询：

static List<string> GetSQLColumns(string connectionString, string tableName) 
{
    List<string> columns = new List<string>();
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = @"SELECT COLUMN_NAME 
                        FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_NAME = @TableName 
                        AND TABLE_SCHEMA = 'dbo'"; // 根据实际架构调整
        
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@TableName", tableName);
        
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                columns.Add(reader["COLUMN_NAME"].ToString().Trim());
            }
        }
    }
    return columns; 
}

调用时改为：

List<string> sqlColumns = GetSQLColumns(sqlConnection, tableInputTextBox.Text);

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