NullValues选项在加载到DataTable时不起作用 [英] NullValues Option Not Working When Loading to DataTable

问题描述

在将CSV读入DataTable时，我试图为似乎无效的布尔值和null值添加选项.例如，一个文件包含类似于以下内容的数据:

When reading a CSV into a DataTable, I am trying to add options for boolean and null values that don't seem to be working. For example, a file containing data similar to:

Id,MaxDiscount,Name,Active,AltId
1,,Foo,1,ABC123
2,10,Bar,0,DEF345

以及以下使用架构文件动态获取我们期望的标头和数据类型的逻辑:

And the following logic that uses a schema file to dynamically get the headers and data types we are expecting:

var dt = new DataTable();
using (var reader = new StreamReader(file.FullName))
using (var csv = new CsvReader(reader))
{
    csv.Configuration.HasHeaderRecord = true;
    csv.Configuration.IgnoreQuotes = false;
    csv.Configuration.TypeConverterOptionsCache.GetOptions<int>().NullValues.Add(string.Empty);
    csv.Configuration.TypeConverterOptionsCache.GetOptions<bool>().BooleanFalseValues.Add("0");
    csv.Configuration.TypeConverterOptionsCache.GetOptions<bool>().BooleanTrueValues.Add("1");

    using (var dr = new CsvDataReader(csv))
    {
        foreach (var p in schema.Properties)
        {
            var type = Type.GetType(p.Type, true, true);
            var dc = new DataColumn
            {
                ColumnName = p.Name,
                Unique = p.IsId,
                AllowDBNull = p.Nullable,
                DataType = type
            };

            dt.Columns.Add(dc);
        }
        dt.Load(dr);
    }
}

这将导致错误字符串未被识别为有效的布尔值.无法存储< 0>在活动列中.预期的类型为布尔值.

如果我手动更改数据并将 0 替换为 false ，将 1 替换为 true ，则我将布尔值有效，但出现类似错误:输入字符串的格式不正确.无法存储<>在MaxDiscount列中.预期的类型为Int32.

If I manually change the data and replace 0 with false and 1 with true, I then the boolean values work, but I get a similar error: Input string was not in a correct format. Couldn't store <> in MaxDiscount Column. Expected type is Int32.

为了使此功能正常工作，我在这里缺少什么吗?还是类型转换器选项仅适用于已知对象?

Is there something I am missing here in order to get this to work? Or do the Type Converter options only work on known objects?

解析CSV文件时，我无法使用任何预定义的对象模型，因为它们可以包含任意数量的字段.只要存在模式，程序就应该知道如何处理它.模式示例如下所示:

I am unable to use any pre-defined object models when parsing the CSV files as they can contain any number of fields. As long as a schema exists, then the program should know how to handle it. An example schema would be something like the following:

{
  "type": "Part",
  "bucket": "s3Bucket",
  "prefix": "prefix/of/datafile",
  "targetDirectory": "..\\path\\to\\working\\dir",
  "delimiter": ",",
  "properties": [
    {
      "name": "Id",
      "type": "System.String",
      "required": true,
      "nullable": false,
      "isId": true,
      "defaultValue": null,
      "minLength": 6,
      "maxLength": 8
    },
    {
      "name": "MaxDiscount",
      "type": "System.Int32",
      "required": true,
      "nullable": true,
      "isId": false,
      "defaultValue": null,
      "minLength": -1,
      "maxLength": -1
    },
    {
      "name": "Name",
      "type": "System.String",
      "required": true,
      "nullable": false,
      "isId": false,
      "defaultValue": null,
      "minLength": 1,
      "maxLength": 127
    },
    {
      "name": "Active",
      "type": "System.Boolean",
      "required": true,
      "nullable": false,
      "isId": false,
      "defaultValue": null,
      "minLength": 1,
      "maxLength": 1
    },
    {
      "name": "AltId",
      "type": "System.String",
      "required": true,
      "nullable": true,
      "isId": false,
      "defaultValue": null,
      "minLength": 1,
      "maxLength": 127
    }
  ]
}

在这种情况下，架构中的 Properties 将与CSV文件中的列相关.从理论上讲，这将使我能够在运行时解析文件并验证数据类型，而不必每次引入新的CSV布局时都创建一个新的对象模型.

In this case, the Properties in the schema would relate to columns in the CSV file. This, in theory, would allow me to parse the files and validate the data types at runtime, rather than having to create a new object model each time a new CSV layout is introduced.

推荐答案

在我看来， CsvDataReader 类是无用的- GetFieldType 的实现返回 typeof(字符串)， GetValue 也会返回 string s，因此，尽管它实现了类型化的数据访问器方法，但它们从未被 DataTable 类 Load 方法.

In my opinion the CsvDataReader class is useless - the implementation of GetFieldType returns typeof(string), GetValue also returns strings, so although it implements the typed data accessor methods, they are never called by the DataTable class Load method.

因此没有 CsvHelper 映射发生-转换是通过 DataTable 使用标准字符串类型转换器进行的.

Thus no CsvHelper mapping occurs - the conversion is done by DataTable using standard string to type converters.

我建议删除 CsvDataReader 类的用法，并用以下内容代替 dt.Load(dr); 调用:

I would suggest removing the usage of CsvDataReader class and replacing the dt.Load(dr); call with something like this:

static void Load(DataTable dt, CsvReader csv)
{
    if (csv.Configuration.HasHeaderRecord)
    {
        if (!csv.Read()) return;
        csv.ReadHeader();
    }
    var valueTypes = new Type[dt.Columns.Count];
    for (int i = 0; i < valueTypes.Length; i++)
    {
        var dc = dt.Columns[i];
        var type = dc.DataType;
        if (dc.AllowDBNull && type.IsValueType)
            type = typeof(Nullable<>).MakeGenericType(type);
        valueTypes[i] = type;
    }
    var valueBuffer = new object[valueTypes.Length];
    dt.BeginLoadData();
    while (csv.Read())
    {
        for (int i = 0; i < valueBuffer.Length; i++)
            valueBuffer[i] = csv.GetField(valueTypes[i], i);
        dt.LoadDataRow(valueBuffer, true);
    }
    dt.EndLoadData();
}

本质上是准备列类型映射，并使用 CsvReader.GetField(type，index)方法填充 DataRow 值.这样，转换由 CsvReader 类执行，并将使用所有转换选项.

Essentially preparing column type mapping and using CsvReader.GetField(type, index) method for populating the DataRow values. This way the conversion is performed by the CsvReader class and will use all the conversion options.

顺便说一句，实际上不需要显示的布尔值或null值的选项-所有这些都由 CsvHelper 默认类型转换器处理.

Btw, none of the shown options for boolean or null values is really needed - all they are handled by the CsvHelper default type converters.

这篇关于NullValues选项在加载到DataTable时不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！