brief introduction

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

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

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

modular

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 CSV files, blank lines will be ignored. If blank lines contain spaces, an error will be reported. If it is a CSV file edited by Excel, an empty line will become a line containing only separators, and an error will be reported.

Read one by one

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 will not read all the contents into memory at one time, unless the ToList or ToArray methods are called. Therefore, this writing method of reading item by item 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 is to read the title. If not, the title is obtained for the first time by the while loop, and an error will be reported.

csv.ReadHeader(); This sentence is to assign a value to the title. If not, CSV Getfield < string > ("name") will report that the title cannot be found.

Using TryGetField can prevent unexpected error reporting.

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 item by item

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 attribute is used to mark the field order.

When reading the file, if there is no title, the fields can only be determined by 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 = false configuration tells CsvReader that there is no title. This line must be added, otherwise the first line title will be skipped by default, resulting in a missing line 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 the title, you also 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 CSV files.

public class Foo
{
    ...

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

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

 

Ignore

Ignore field

Optional

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

public class Foo
{
    ...

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

Default

When the read field is empty, the Default attribute can specify a Default value for it.

The Default attribute is only valid when reading, and null values are not replaced with Default values when writing.

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, the read value is "", not null. After marking the NullValues attribute, if the value of a field in the CSV file is the value specified by NullValues, it will be null after reading.

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

Unfortunately, this feature does not work when writing to a file. Therefore, it will cause inconsistent reading and writing.

Constant

The Constant attribute 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 used for type conversion.

For example, we often specify the format of numbers and time types.

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 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 Allowexspecificer, these two enumerations have similar functions. This feature is only valid when reading and will not be converted to hexadecimal writing when writing. This will lead to inconsistent reading and writing. You can specify the writing Format with the Format attribute.

mapping

If you cannot add attributes to the class to be mapped, in this case, you can use ClassMap to map.

The effect of using mapping and using features is the same, and the place of pit father is also the same. The following example implements the functionality of the above feature 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");
    }
}

 

You need to register before using the mapping

csv.Configuration.RegisterClassMap<Foo2Map>();

 

ConvertUsing

ConvertUsing allows you to use a delegate method to implement 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 earlier. Do you want to use the first line as the title

csv.Configuration.HasHeaderRecord = false;

 

IgnoreBlankLines

Whether to ignore blank lines. The default value is true

csv.Configuration.IgnoreBlankLines = false;

 

A line containing only spaces or cannot be ignored.

AllowComments

Whether to allow comments, which begin with #.

csv.Configuration.AllowComments = true;

 

Comment

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

csv.Configuration.Comment = '/';

 

BadDataFound

Set a function that will be triggered 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 is false. If there are quotation marks in the string, there must be three "connected together, there will be one in the read string". If there are 1 quotation marks, it will be ignored and 2 quotation marks will be reported as errors.

If true, it will return "as string".

csv.Configuration.IgnoreQuotes = true;

 

CsvWriter does not have this attribute. Once the string contains ", three" are written together.

TrimOptions

Remove field leading and trailing spaces

csv.Configuration.TrimOptions = TrimOptions.Trim;

PrepareHeaderForMatch

PrepareHeaderForMatch defines the function to match the attribute name with the title. The title and attribute name are run through this function. This function can be used to delete the spaces in the title, or to unify the case and compare when the title and attribute names are not of the same case.

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