User manual of CsvHelper

catalog

This code is based on CsvHelper 15.0.5

brief introduction

CsvHelper is a. NET library for reading and writing CSV files. Extremely fast, flexible and easy to use.

CsvHelper is built on. NET Standard 2.0 and can run almost anywhere.

Github address: https://github.com/joshclose/csvhelper

modular

modular function
CsvHelper The core class for reading and writing CSV data.
CsvHelper.Configuration Class to configure CsvHelper read-write behavior.
CsvHelper.Configuration.Attributes Configure the properties of CsvHelper.
CsvHelper.Expressions Class that generates LINQ expressions.
CsvHelper.TypeConversion Class that interconverts CSV fields to. NET types.

read

Test class

public class Foo
{
    public int ID { get; set; }

    public string Name { get; set; }
}

csv file data

ID,Name
1,Tom
2,Jerry

Read all records

using (var reader = new StreamReader("foo.csv"))
{
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        var records = csv.GetRecords<Foo>();
    }
}

When reading the csv file, blank lines will be ignored. If blank lines contain spaces, an error will be reported.
If it is a CSV file edited by Excel, the blank line will become a line containing only separators, and an error will also be reported.

Read item by item

using (var reader = new StreamReader("foo.csv"))
{
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        while (csv.Read())
        {
            var record = csv.GetRecord<Foo>();
        }
    }
}

The getrecords < T > method returns an IEnumerable < T > through yield, and does not read all the contents into memory at one time unless the ToList or ToArray methods are called. Therefore, this article by article reading method is not necessary.

Read a single field

using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    csv.Read();
    csv.ReadHeader();

    while (csv.Read())
    {
        var id = csv.GetField<int>(0);
        var name = csv.GetField<string>("Name");
    }
}

When reading line by line, you can ignore the title line, but not here.

csv.Read(); this sentence reads the title. If not, the first time the while loop gets the title, it will definitely report an error.

csv.ReadHeader(); this sentence assigns a value to the title, if not, csv.GetField < string > ("name") will report no title found.

Using TryGetField can prevent unexpected errors.

csv.TryGetField(0, out int id);

write in

Write all records

var records = new List<Foo>
{
    new Foo { ID = 1, Name = "Tom" },
    new Foo { ID = 2, Name = "Jerry" },
};

using (var writer = new StreamWriter("foo.csv"))
{
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.WriteRecords(records);
    }
}

Write one by one

using (var writer = new StreamWriter("foo.csv"))
{
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        foreach (var record in records)
        {
            csv.WriteRecord(record);
        }
    }
}

Write field by field

using (var writer = new StreamWriter("foo.csv"))
{
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.WriteHeader<Foo>();
        csv.NextRecord();

        foreach (var record in records)
        {
            csv.WriteField(record.ID);
            csv.WriteField(record.Name);
            csv.NextRecord();
        }
    }
}

characteristic

Index

The Index property is used to mark the field order.

When reading a file, if there is no title, the fields can only be determined in order.

public class Foo
{
    [Index(0)]
    public int ID { get; set; }

    [Index(1)]
    public string Name { get; set; }
}
    
using (var reader = new StreamReader("foo.csv"))
{
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        csv.Configuration.HasHeaderRecord = false;

        var records = csv.GetRecords<Foo>().ToList();
    }
}

csv.Configuration.HasHeaderRecord =The false configuration tells CsvReader that there is no title. This line must be added, otherwise the first line will be skipped by default, resulting in one line missing in the final result. If there is a large amount of data, it will be difficult to find this bug.

When writing files, they are written in Index order. If you don't want to write a title, you need to add csv.Configuration.HasHeaderRecord = false;

Name

If the field Name and column Name are inconsistent, you can use the Name property.

public class Foo
{
    [Name("id")]
    public int ID { get; set; }

    [Name("name")]
    public string Name { get; set; }
}

NameIndex

NameIndex is used to process columns with the same name in a CSV file.

public class Foo
{
    ...

    [Name("Name")]
    [NameIndex(0)]
    public string FirstName { get; set; }

    [Name("Name")]
    [NameIndex(1)]
    public string LastName { get; set; }
}

Ignore

Ignore fields

Optional

If no matching field is found during reading, it is ignored.

public class Foo
{
    ...

    [Optional]
    public string Remarks { get; set; }
}

Default

The Default property specifies the Default value for a field that is read when it is empty.

The Default property is only valid when reading, and when writing, it does not replace the null value with the Default value.

NullValues

public class Foo
{
    ...

    [NullValues("None", "none", "Null", "null")]
    public string None { get; set; }
}

When reading the file, if the value of a field in the CSV file is empty, then the value after reading is' ', not null. After the NullValues property is marked, if the value of a field in the CSV file is the value specified by NullValues, then it will be null after reading.

If the Default attribute is also marked, it has no effect.

As a matter of fact, this feature does not work when writing files. Therefore, it may cause inconsistency between reading and writing.

Constant

The Constant property specifies a Constant value for the field, which is used when reading and writing, regardless of any other mapping or configuration specified.

Format

Format specifies the string format to use for type conversion.

For example, number and time type, we often specify its format.

public class Foo
{
    ...

    [Format("0.00")]
    public decimal Amount { get; set; }

    [Format("yyyy-MM-dd HH:mm:ss")]
    public DateTime JoinTime { get; set; }
}

Boolean truevalues and Boolean false values

These two features are used to convert bool to the specified form of display.

public class Foo
{
    ...

    [BooleanTrueValues("yes")]
    [BooleanFalseValues("no")]
    public bool Vip { get; set; }
}

NumberStyles

public class Foo
{
    ...

    [Format("X2")]
    [NumberStyles(NumberStyles.HexNumber)]
    public int Data { get; set; }
}

More useful is NumberStyles.HexNumber And NumberStyles.AllowHexSpecifier , these two enumerations have the same effect. This feature is only valid when reading, and will not be converted to hexadecimal when writing. This results in inconsistent read and write, and you can use the Format property to specify the write Format.

mapping

If you cannot add properties to a class to map, in this case, you can use ClassMap to map.

The effect of using mapping is the same as that of using features, and so is the effect of using features. The following example implements the functions of the above features with attributes.

public class Foo2
{
    public int ID { get; set; }

    public string Name { get; set; }

    public decimal Amount { get; set; }

    public DateTime JoinTime { get; set; }

    public string Msg { get; set; }

    public string Msg2 { get; set; }

    public bool Vip { get; set; }

    public string Remarks { get; set; }

    public string None { get; set; }

    public int Data { get; set; }
}

public class Foo2Map : ClassMap<Foo2>
{
    public Foo2Map()
    {
        Map(m => m.ID).Index(0).Name("id");
        Map(m => m.Name).Index(1).Name("name");
        Map(m => m.Amount).TypeConverterOption.Format("0.00");
        Map(m => m.JoinTime).TypeConverterOption.Format("yyyy-MM-dd HH:mm:ss");
        Map(m => m.Msg).Default("Hello");
        Map(m => m.Msg2).Ignore();
        Map(m => m.Vip)
            .TypeConverterOption.BooleanValues(true, true, new string[] { "yes" })
            .TypeConverterOption.BooleanValues(false, true, new string[] { "no" });
        Map(m => m.Remarks).Optional();
        Map(m => m.None).TypeConverterOption.NullValues("None", "none", "Null", "null");
        Map(m => m.Data)
            .TypeConverterOption.NumberStyles(NumberStyles.HexNumber)
            .TypeConverterOption.Format("X2");
    }
}

Before using a map, you need to register

csv.Configuration.RegisterClassMap<Foo2Map>();

ConvertUsing

ConvertUsing allows you to use a delegate method for type conversion.

// constant
Map(m => m.Constant).ConvertUsing(row => 3);

// Put the two columns together
Map(m => m.Name).ConvertUsing(row => $"{row.GetField<string>("FirstName")} {row.GetField<string>("LastName")}");

Map(m => m.Names).ConvertUsing(row => new List<string> { row.GetField<string>("Name") } );

to configure

Delimiter

Separator

csv.Configuration.Delimiter = ",";

HasHeaderRecord

This configuration has been mentioned before. Do you want to use the first line as the title

csv.Configuration.HasHeaderRecord = false;

IgnoreBlankLines

Whether to ignore empty lines? true by default

csv.Configuration.IgnoreBlankLines = false;

Cannot ignore a row that contains only spaces or.

AllowComments

Whether comments are allowed. Comments begin with.

csv.Configuration.AllowComments = true;

Comment

Gets or sets the character used to represent the commented line. The default is ා.

csv.Configuration.Comment = '/';

BadDataFound

Set a function that will trigger when the data is incorrect and can be used to log.

IgnoreQuotes

Gets or sets a value indicating whether quotation marks should be ignored and treated like any other character when parsing.

The default value is false. If there are quotation marks in the string, there must be three "linked together, one of the read strings will be found". If there is one, it will be ignored, and if there are two, it will be reported as an error.

If true, "is returned as a string.

csv.Configuration.IgnoreQuotes = true;

There is no such attribute in CsvWriter. Once the string contains ", three" are written together.

TrimOptions

Remove leading and trailing spaces

csv.Configuration.TrimOptions = TrimOptions.Trim;

PrepareHeaderForMatch

PrepareHeaderForMatch defines a function that matches the property name with the title. Both the title and property name run through this function. This function can be used to delete the spaces in the title, or to compare after unified case when the case of title and property name is inconsistent.

csv.Configuration.PrepareHeaderForMatch = (string header, int index) => header.ToLower();

Tags: C# github Attribute Excel

Posted on Wed, 20 May 2020 01:00:02 -0400 by jomama