[FIXED] How to create the C# mapping class to csvhelper

Issue

I have a csv file with 40 columns and I want to load it to a datatable using csvhelper. After installing the library, I did this:

using (TextReader reader = File.OpenText(fileName)) {
   var csv = new CsvReader(reader);
   while (csv.Read()) {
       var farmID = csv.GetField(0);
       Console.WriteLine(farmID);
   }
}

and as you see, I have a console statement and it works perfectly.

However, the csvReader has a constructor that takes a custom class to load the data directly to it.

I tried this:

var record = csv.GetRecord<CSVFileDefinition>();

but I got this exception

No properties are mapped for type 'MyProjectName.CSVFileDefinition'.

because the CSVFileDefinitionis an empty class.

my question is how to fill that class.

This is the library:

http://joshclose.github.io/CsvHelper/

Many thanks

Update 2

The solution that works for me is:

sealed class CSVFileDefinitionMap : CsvClassMap<CSVFileDefinition>
{
   public CSVFileDefinitionMap()
   {
      Map(m => m.FRM_ID).Name("FARM ID");
      Map(m => m.FRM_OWNER).Name("FARM OWNER ");
   }
}

class CSVFileDefinition
{
    public string FRM_ID { get; set; }
    public string FRM_OWNER { get; set; }
}

using (TextReader reader = File.OpenText(fileName)) {
    var csv = new CsvReader(reader);
    csv.Configuration.RegisterClassMap<CSVFileDefinitionMap>();
    while (csv.Read()) {
       var record = csv.GetRecord<CSVFileDefinition>();
    }
}

Solution

It seems that all you need to do is to add a property to the CSVFileDefinition class for each column name you expect to find in the CSV file, and the auto mapping should take care of the rest.

For example, this should pull in the farm ID column providing that the property name matches the column name in the CSV:

public class CSVFileDefinition
{
    public int FarmId { get; set; }

    //... add other columns here...
}

Answered By - Mitchell Lane

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] How to write unescaped quoted values

Issue

Does CsvHelper not support quoted unescaped output or is there something wrong with my writer configuration?

Input:

Jake|"B" Street

CsvHelper reader configuration:

var readerConfig = new CsvConfiguration(CultureInfo.InvariantCulture)
{
    Delimiter = "|",
    TrimOptions = TrimOptions.Trim,     
    IgnoreBlankLines = true,
    Mode = CsvMode.NoEscape
};

After record is read into memory, the value with double quotes is unescaped as expected: "B" Street

However, when I write records to CSV file if ShouldQuote = args => true is used without CsvMode.NoEscape double quotes are escaped and if I add CsvMode.NoEscape the output is not quoted at all.

writerConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture)
{
    ShouldQuote = args => true,
    Mode = CsvMode.NoEscape
};

Output with CsvMode.NoEscape: "B" Street // Output not quoted and not escaped.

Output without CsvMode.NoEscape: """B"" Street" // Double quotes escaped.

Any ideas on how to write unescaped quoted values with CsvHelper?

Solution

This feels a little hacky, but I was able to do what I think you are trying to do with CsvMode.NoEscape and a custom string converter to add the outer quotes. Another option would be to use Convert in a ClassMap to add the outer quotes on a column by column basis.

void Main()
{
    var records = new List<Foo>
    {
        new Foo { Id = 1, Name = "\"B\" Street" },
    };
    
    var config = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        Mode = CsvMode.NoEscape
    };

    using (var csv = new CsvWriter(Console.Out, config))
    {
        csv.Context.TypeConverterCache.AddConverter<string>(new OuterQuoteConverter());
        csv.WriteRecords(records);
    }
}

public class OuterQuoteConverter : StringConverter
{
    public override string ConvertToString(object value, IWriterRow row, MemberMapData memberMapData)
    {
        var quoted = "\"" + (string)value + "\"";
        return base.ConvertToString(quoted, row, memberMapData);
    }
}

public class Foo
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Answered By - David Specht

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How to write a C# List to CSV with CsvHelper package

Issue

I have a simple Console Application that contains a list of 3 users in Program.cs. Within Program.cs I want to call a method in FileOperations.cs.

First my Program.cs:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using CsvHelper;

namespace GimpiesConsoleOOcsvListUI
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create user default instances
            User user0 = new User("Beheer", "beheer@gimpies.nl", "123");
            User user1 = new User("Inkoop", "inkoop@gimpies.nl", "123");
            User user2 = new User("Verkoop", "verkoop@gimpies.nl", "123");

            // List of users (with a list you can add, get and remove items in the list)
            List<User> users = new List<User>();
            users.Add(user0);
            users.Add(user1);
            users.Add(user2);

            // Create login instance
            LoginManager loginMgr = new LoginManager();

            Start:
            // Welcome message
            Console.WriteLine("Welcome to GimpiesTerminal! Choose 1 to login or 2 to register:");

            // Get input from user
            string input = Console.ReadLine();

            bool successfull = false;

            while (!successfull)
            {
                if(input == "1")
                {
                    Console.WriteLine("Enter your username:");
                    string username = Console.ReadLine();
                    Console.WriteLine("Enter your password:");
                    string password = Console.ReadLine();

                    foreach (User user in users)
                    {
                        if (username == user.UserName && password == user.PassWord)
                            {
                                Console.WriteLine("You have successfully logged in !!!");
                                Console.ReadLine();
                                successfull = true;
                            break;  
                            }
                    }
                        if (!successfull)
                            {
                                Console.WriteLine("Your username or password is incorrect, try again !!!");
                            }
                }
                else if (input == "2")
                {
                        // Get user input
                        Console.WriteLine("Enter your username:");
                        string username = Console.ReadLine();

                        Console.WriteLine("Enter your email:");
                        string email = Console.ReadLine();

                        Console.WriteLine("Enter your password:");
                        string password = Console.ReadLine();

                        // Create fresh instance to save input in memory
                        User user = new User(username, email, password);

                        // Adds the user to the excisting list
                        users.Add(user);
                        
                        successfull = true;
                        goto Start;
                }
                else if (input == "3")
                {
                    // Here I want to call the method from FileOperations.cs to write the List here to a CSV file.
                }
                else
                {
                    Console.WriteLine("Try again !!!");
                    break;
                }
            }
            
            // // Loop over stored users within instances inside the list where users are added
            // foreach (User user in users)
            // {
            
            //     if(loginMgr.Login(user))
            //     {
            //         // Login successfull
            //         Console.WriteLine("Login user " + user.UserName);

            //     }
            //     else
            //     {
            //         // Not successfull

            //     }
            // }  
            
        }
    }
}

And here is my FileOperations.cs:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using CsvHelper;

namespace GimpiesConsoleOOcsvListUI
{
    public class FileOperations
    {
        // Handles CRUD within CSV files and able to save them
        public void SaveToCSV(User user)
        {
            using (var mem = new MemoryStream())
            using (var writer = new StreamWriter(mem))
            using (var csvWriter = new CsvWriter(writer))
            {
                csvWriter.Configuration.Delimiter = ";";
                csvWriter.Configuration.HasHeaderRecord = true;
                csvWriter.Configuration.AutoMap<User>();

                csvWriter.WriteHeader<User>();
                csvWriter.WriteRecords(user);

                writer.Flush();
                var result = Encoding.UTF8.GetString(mem.ToArray());
                Console.WriteLine(result);
            }
        }
    } 
}

I get the following errors at line 16 of FileOperations.cs:

StreamWriter writer Argument 1: cannot convert from System.IO.StreamWriter to CsvHelper.ISerializer

And line 23:

User user Argument 1: cannot convert from GimpiesConsoleOOcsvListUI.User to System.Collections.IEnumerable

I'm new to C# so I hope you don't mind if it is a simple to solve problem! :-)

Solution

You could try the following piece of code if you want to serialize a single user object and display its serialized header and values:

public void SaveToCSV(List<User> users)
{
    var csvConfig = new CsvConfiguration(CultureInfo.CurrentCulture)
    {
        HasHeaderRecord = true,
        Delimiter = ",",
        Encoding = Encoding.UTF8
    };

    using (var mem = new MemoryStream())
    using (var writer = new StreamWriter(mem))
    using (var csvWriter = new CsvWriter(writer, csvConfig))
    {
        csvWriter.WriteHeader<User>();
        csvWriter.WriteRecords(users);

        writer.Flush();
        var result = Encoding.UTF8.GetString(mem.ToArray());
        Console.WriteLine(result);
    }
}

It is using a simple StringWriter instance needed by the library's CsvWriter and is using InvariantCulture info for content serialization. The third parameter leaveOpen must be set to true in this case because you're interested in the content of the StringWriter within the scope of the using block. Last but not least, you're currently passing a single instance of User so you have to call WriteRecord method instead of WriteRecords.

If you want to write it to the actual file though, you have to do it a bit differently:

public void SaveToCSV(List<User> users)
{
    var csvConfig = new CsvConfiguration(CultureInfo.CurrentCulture)
    {
        HasHeaderRecord = true,
        Delimiter = ",",
        Encoding = Encoding.UTF8
    };

    using (var writer = new StreamWriter("path\\to\\your\\file.csv"))
    using (var csvWriter = new CsvWriter(writer, csvConfig))
    {
        csvWriter.WriteHeader<User>();
        csvWriter.WriteRecords(users);
    }
}

You don't need to call Flush in the second example because the end of using block calls it for you and you're not interested in the CSV content within its scope.

Answered By - msmolcic

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to ignore empty rows in CSV when reading

Issue

Trying to read a CSV file that has empty rows (usually at the end) using CsvHelper.GetRecords<T>().

Without the empty rows this works a treat. However if the CSV file has an empty row (defined as , , , , , ) then it throws a TypeConverterException

Text: ''
MemberType: IntelligentEditing.PerfectIt.Core.DataTypes.Styles.StyleRuleType
TypeConverter: 'CsvHelper.TypeConversion.EnumConverter'

I have gone through the documentation (https://joshclose.github.io/CsvHelper/api/CsvHelper.Configuration/Configuration/) and have tried setting up the configuration object to IgnoreBlankLines = true however this has not worked.

Simplified for an example:

public enum ItemTypeEnum
{
    Unknown = 0,
    Accounts = 1,
    HR = 2,
}


public class CsvItemDto
{
    public int Id { get; set; }

    public string Value { get; set; }

    public ItemTypeEnum ItemType { get; set; }
}

.
.
.
var configuration = new Configuration()
{
    HasHeaderRecord = true,
    HeaderValidated = null,
    MissingFieldFound = null,
    IgnoreBlankLines = true,

};
var csv = new CsvReader(textReader, configuration);
var rows = csv.GetRecords<CsvItemDto>();


if (rows != null)
{
    var items = rows.ToList();
    //Throws exception here
}

The CSV would usually contain something like this:

Id,Value,ItemType
1,This,Unknown
2,That,Accounts
3,Other,HR
,,
,,

I expected the IgnoreBlankLines to ignore the blank rows in the CSV but it is not. Any ideas?

Solution

you can try to implement ShouldSkipRecord on Configuration to choose skip or not

var configuration = new Configuration () {
                HasHeaderRecord = true,
                HeaderValidated = null,
                MissingFieldFound = null,
                IgnoreBlankLines = true,
                ShouldSkipRecord = (records) =>
                {
                    // Implement logic here
                    return false;
                }
            };

Answered By - Phat Huynh

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More