CSV support in .NET Core

[sparticus1701]

I apologize if this is not the correct place to make such a request, but I believe it is really past time for .NET to provide CSV layout functionality natively.

Developers are currently at the mercy of doing it manually or using CsvHelper, Lumenworks or a plethora of different packages, each with their own set of peculiarities. Admittedly 'standard' should be a loose term when referring to CSV, but MS could do a lot with their own implementation to cut down the confusion.

I believe there was some support for it in VB, and some messy ways of referencing it in C#, but first class support for it would be spectacular.

[AlgorithmsAreCool]

@sparticus1701 have you seen this https://devblogs.microsoft.com/dotnet/an-introduction-to-dataframe/ ?

https://github.com/dotnet/corefxlab/blob/6b0545e49b618a844231a3c7f7f1a8b49663bb91/src/Microsoft.Data.Analysis/DataFrame.IO.cs#L101

[danmoseley]

Does DataFrame help @sparticus1701 ?

[sparticus1701]

No, it doesn't.

[terrajobst]

Does DataFrame have a CSV writer?

[terrajobst]

FWIW, I wrote my own CSV reader/writer because I found the support in .NET lacking. One of the copies can be found here.

[eerhardt]

Does DataFrame have a CSV writer?

It will once dotnet/corefxlab#2947 is merged.

[am11]

FWIW, I wrote my own CSV reader/writer because I found the support in .NET lacking. One of the copies can be found here.

@terrajobst, I ran some RFC 4180 tests on that library, it seems pretty robust (i.e. I couldn't find a failing scenario so far with the part of corpus I tested). Are there any plans to publish it as a NuGet package? 😎

[terrajobst]

Not really. In order to productize it, we'd have to invest quite a bit more than me just putting that stuff in a package. And that would require more deliberation than me just unblocking myself.

[danmoseley]

Generally we want to support community libraries. Could you share more about the issues you have with them?

[sparticus1701]

This feels like a sales plug for your own work, but there's quite a few holes in your logic:

Most of the decent libraries expose data via IDataReader. Oddly, the VisualBasic one does not.

CSV files are not exclusively used for relational databases. IDataReader is not the best choice.

Some libraries have certainly made questionable API decisions. I don't think that's a good reason to create yet another.

It's the perfect reason to get MS involved.

Personally, I'm critical of CsvHelper library's decision to require a CultureInfo in the constructor. CSV is for data interchange I think it should default to InvariantCulture. I don't know what you're looking for here.

This is an extremely narrow view. Software (including Excel) around the world are producing localized CSV files. I've personally done user research with international customers about these issues. Not having locale support is simply not implementing all the requirements for a CSV reader.

Sure it does. Tell Lumenworks hasHeaders=false, call Read() once to skip the header row, then access everything by ordinal. Clearly, you can't access by name anyway, since that would be ambiguous.

Using a hack to avoid the fact that duplicated headers aren't supported doesn't constitute supporting duplicate headers. Your 'solution' is actually the problem.

And you think Microsoft will more responsive?

I would trust Microsoft, the .NET team and community any day over a single person, or 3rd party group.

Adding yet another implementation obviously doesn't fix this [fragmenation].

Of course it does, because it standardizes and consolidates under a single community which has the resources for continuous improvement.

Don't use those ones! User a mature one, there are plenty.

Mature packages die all the time! I for one don't want to keep skipping from package to package. Even 'mature' packages start having problems as the world rages on (.NET 5, etc.) and don't get updated.

Try mine...

Blatant self-promotion

[AlgorithmsAreCool]

@sparticus1701

Blatant self-promotion

Kinda harsh. It is self promotion, but unless he is profiting or selling something I don't think offering alternatives is anything but an attempt to help.

[danmoseley]

Let’s focus on the content not the person please. 🙂

[MarkPflug]

This feels like a sales plug for your own work B
Blatant self-promotion

Sales plug implies that I'm asking for money. It's open source. What's wrong with me promoting my own hard work?

CSV files are not exclusively used for relational databases. IDataReader is not the best choice.

Then what is the best choice? IEnumerable<IEnumerable<string>>? If there is a better choice then it seems odd to me that the majority of the CSV implementation in the ecosystem chose to use IDataReader. I would assume MS would come to the same conclusions if they chose to implement one.

Software (including Excel) around the world are producing localized CSV files.

That's fine, and I didn't say: "don't support localization", I said "default to Invariant". Excel has earned a special notoriety for the "data sets" that business people produce with it.

Using a hack to avoid the fact that duplicated headers aren't supported

See, to me, the hack is to work around a poorly designed data set, not a failing of the Lumenworks API.

Maybe give a concrete example of a data set that has duplicate columns and how you would like to process it? I don't think talking about this stuff in the abstract is particularly productive.

Of course it does, because it standardizes and consolidates under a single community

Does it now? https://xkcd.com/927/

Mature packages die all the time!

Josh Close still actively maintains CsvHelper. Package updates come very frequently. If there is a shortcoming in it's capabilities have you offered a PR to fill the gap?

Lumenworks is 15 years old. And, while Sebastien Lorion no longer supports it, I wouldn't consider it dead. The main fork maintained by Paul Hatcher currently has 0 open pull requests. It hasn't been updated in some time, but mature software shouldn't expect to have significant updates all that often.

[jtheisen]

Personally, I'm critical of CsvHelper library's decision to require a CultureInfo in the constructor. CSV is for data interchange I think it should default to InvariantCulture. I don't know what you're looking for here.

I just used CsvHelper and used a custom CultureInfo to make it output dates as ISO-8601. CultureInfo.InvariantCulture doesn't do that.

[pgovind]

Have you looked at the Microsoft.VisualBasic.FileIO(can be used in C# too) namespace ? It already ships with .NET Core, so the ability to read csv files already exists in some form. I opened a new PR in corefxlab to refactor DataFrame's LoadCsv method to use TextFieldParser and it worked for the use case I was targeting. I don't have too much experience with TextFieldParser, but it might solve all your use cases too without needing an external dependency. And since it's part of the libraries, it addresses a majority of the points you listed? You might also be interested in https://stackoverflow.com/a/3508572.

[sparticus1701]

I've always wondered why that has been tucked away ostensibly under VB, when it really should be under System.Text.Csv or somewhere more visible.

It's a start, but it does not support internationalization (at best you can play with the delimiter) nor has async abilities.

I do think it could be a starting point for what I'm proposing, but currently not ready for the job.

[danmoseley]

I've always wondered why that has been tucked away ostensibly under VB, when it really should be under System.Text.Csv or somewhere more visible.

Historical accident. There are a few other unique VB API that are interesting beyond VB.

[samsp-msft]

The VB runtime library was originally created to enable easier porting of VB6 code to .NET, and then got extended to provide a simpler API for some common scenarios that seemed really alien in .NET such as File IO. There is still some really useful APIs in there, which can be used from any .NET language, not just VB.

[NoNamePro0]

It's simply split with ;. So I think it's not needed.

[terrajobst]

Except it isn't. This is valid CSV and represents a a table with two rows and two columns.

First,Second
"Hello, my name is Immo",Single line with a comma
"Multiple
lines
are also supported",Multiple lines

[NoNamePro0]

Hm, your right. This would great, if it works. But I think the most people uses normal splited CSV.

[terrajobst]

Right, which is why people are asking for a proper parser :-)

[MgSam]

It's far past time for .NET to have a proper CSV parsing library.

• DataFrame is great, but you don't want a parser to be tied to a specific data structure (and to a library which may or may not ever make it into the BCL...)
• Developers frequently overestimate the seeming simplicity of parsing CSV and roll their own implementations by hand which inevitably have bugs (Even Microsoft has done this)
• Experienced developers know that the best format for transferring data is not XML, not JSON, not YAML, not binary, not Excel, but CSV. Every spreadsheet program in the world can read CSV. Every programming stack written in the last 30+ years can read CSV (except .NET!) Every text editor can open CSV and both read and edit it trivially.
• Libraries such as CsvHelper are helpful, but not being part of the BCL means that no part of the BCL can depend on it (such as DataFrame). It also means that the code doesn't benefit from the same rigor that code in the BCL is subjected to.

The BCL should fix this shortcoming and make a standard CSV parser implementation everyone can use.

[MarkPflug]

Libraries such as CsvHelper are helpful, but not being part of the BCL means that no part of the BCL can depend on it

That's not quite true. CsvHelper (and lumenworks, and NLight, and FlatFiles, and my own Sylvan.Data.Csv) implement IDataReader, which is part of the BCL. In my opinion, it is a mistake that DataFrame has it's own (somewhat dodgy) CSV implementation, instead of just consuming an IDataReader (or DbDataReader perhaps). It is also ironic, that the MS.VB.FileIO.TextFieldParser is one of the few CSV parsers in the ecosystem that doesn't implement IDataReader. That alone is enough to disregard the VB implementation, not to mention the horrendous performance.

[sparticus1701]

IDataReader is designed for reading rows from relational databases. Some may want to use a CSV as a relational database, but not all CSV files are, and forcing everything through that interface is not a good idea.

[MarkPflug]

Well, I can only speak from my experience, but 95% (probably more like 99, but I don't want to be hyperbolic) of the csv files I've ever had to deal with were rectangular, tabular data. IDataReader is the obvious interface to use to access such data. If you were to design an API that supported jagged CSV data, I suspect it would adversely impact the common 99% case. Forcing everything through that interface is a good idea, because it covers the 99% case. IDataReader is the interface in the BCL for abstractly working with tabular data sets.

[MgSam]

Implementing an interface is not enough to be useful. It's very useful for a data frame library to be able to have a "FromCsv" helper method. It can't do that without either implementing CSV parsing itself or taking a dependency somewhere else. That dependency should be in the BCL.

You don't want users to have to install several packages and have 10 lines of code where they instantiate one library, get an IDataReader, and pass that to the data frame (or worse yet, read directly from the IDataReader). Trying to use an IDataReader directly is a crappy, overcomplicated experience. People see over-complicated code like that in .NET and they say to themselves, "this is why Python is so much nicer". IMHO, this is a problem ADO .NET has all over the place- it is very verbose to do simple tasks.

The BCL should make doing common simple tasks easy. Reading data from a CSV is a simple, universal task that developers often underestimate and make buggy implementations of. Frankly, it should have been in .NET Framework 1.0.

[MarkPflug]

Implementing an interface is not enough to be useful.

I disagree. Consuming the IDataReader interface is strictly more flexible than consuming a specific data format. That doesn't mean that a FromCsv method can't also exist; ideally implemented via Load(IDataReader). IDataReader means you can consume data from any source that implements the interface, which includes all relational data sources, and most CSV implementations. It could also cover a variety of other, esoteric formats; tabular JSON, specific log file formats, etc.

It doesn't need to be more than one line of code:

DataFrame.Load(CsvDataReader.Create("foo.csv"));

That requires two package perhaps, but it then also offers the flexibility of loading data from a variety of sources.

This might be a dumb question, because I haven't used DataFrame beyond some very basic API exploration, but how do you load data from a relational source? Do I need to format it into CSV first, and use LoadCsv? Or do I need to write a bunch of code to adapt the IDataReader interface to load it into the frame? I honestly don't know.

Trying to use an IDataReader directly is a crappy, overcomplicated experience.

I don't think you'd expect people to use the IDataReader directly. They would have an instance, and simply hand it to the Load method. The Load method would be the one responsible for understanding the "complexity" (it's not that complex) of working with the data reader. The user would just treat it as an opaque object.

Working with an IDataReader is certainly less complicated than implementing your own CSV parser.

Not to be too critical of the DataFrame API, because they are probably designing for a specific use-case; but an API that accepts Stream/Encoding, but doesn't accept TextReader is poorly designed, IMO.

Reading data from a CSV is a simple, universal task that developers often underestimate and make buggy implementations of.

"Simple", "underestimate", "buggy", all referring to the same task; does not compute.

[Clockwork-Muse]

Experienced developers know that the best format for transferring data is not XML, not JSON, not YAML, not binary, not Excel, but CSV.

.... this depends completely on audience. In a web api, current trend is to JSON (hence System.Text.Json). And that's leaving out things like gRPC. This is mostly because objects can be dynamic and nested, which CSV does poorly, or not at all.

CSV is commonly used with relational databases (and hence Excel) - but to represent one result set at a time (where the format will be constant). Note that pretty much all the major ones have built-in export/import tools to handle this, which usually means you let the database do that for you.

[MgSam]

Not sure I agree that CSV can't handle "dynamic" or "nested" data. Tabular data can represent anything.

[Clockwork-Muse]

Sure, but there isn't any "default syntax" for it. If the two of us want to exchange CSVs with nested elements, we're going to have to come up with our own scheme (dynamic can be handled ish by including extra columns, potentially). Contrast that with JSON/XML/YAML that have nested/dynamic structures by the very nature of their defined syntax.

[AlgorithmsAreCool]

Somewhat related: https://github.com/kevin-montrose/Cesil

[MarkPflug]

Also related:
https://www.joelverhagen.com/blog/2020/12/fastest-net-csv-parsers

[MgSam]

@danmoseley If a more concrete proposal is made for this will the BCL team triage it and vote up or down whether to undertake this?

[danmoseley]

I stand by my original comment. Have you tried all the major CSV libraries, including the Sylvan library recommended above?

I would like to challenge the notion that in order for a library to be high quality it needs to be in the core libraries. If that's the case, we have bigger problems with our ecosystem and/or tools and we should be fixing that. We do have a JSON parser but that's because JSON is absolutely fundamental to cloud apps, and it's used by ASP.NET so versioning would be easier. if we were starting that today, we would likely have either contributed to a library or created it as a standalone project with the aim of bringing in other collaborators.

[MgSam]

CsvHelper by Josh Close is a great library which I use in many projects. It doesn't remove the need for Csv parsing to be in .NET.

• You guys cannot rely on a community library anywhere in the BCL. This means instead you write your own CSV parsers in various places.
• Developers underestimate the complexity of CSV parsing. This leads to duplicate code and bugs as people inevitably roll their own CSV parsers and do it incorrectly (including Microsoft's buggy implementation in DataFrame, as I pointed out earlier)
• CSV is a core data-interchange format that has been around for decades. It is crazy that .NET has built-in support for XML and JSON but not CSV. Can you point to any other major framework that has made similar choices?
• In fact, I'd argue that given the existence of CSV parsing functionality in both VB and F# libraries, it puts C# at a particular disadvantage given it is beholden entirely to the BCL and doesn't ship a bunch of extra goodies like VB and F# does. Surely the fact that these other two languages ship this support show it is worth being in-box?
• 3rd party libraries have absolutely no standard. They don't have to conform to BCL best practices, they don't have to conform to naming standards, they don't have to accept community suggestions or even Microsoft's suggestions. They don't have to adhere to certain performance standards. They don't have to adhere to security standards or get security patches from Microsoft. This means they can never have the same level of SLA that the BCL has
• Many organizations, including mine, restrict what 3rd party libraries can be used. (For exactly the SLA reasons mentioned above) Using anything outside the BCL can be hard or impossible for these organizations. By not including CSV parsing in the BCL you are therefore forcing such organizations to roll their own CSV parsers (which again, are often buggy).

[danmoseley]

Many organizations, including mine, restrict what 3rd party libraries can be used. (For exactly the SLA reasons mentioned above) ... you are therefore forcing such organizations to roll their own CSV parsers

I recognize this, and I realize this isn't a policy you have created yourself, but how is the SLA on code you wrote yourself any better than the SLA on equivalent open source code? In the worst case, where the open source code is not well supported, you can copy it into your own tree and then it's arguably no different than if you wrote it - hopefully better, as it's had more contributors and time to stabilize.

Reality is more complex, of course, but I want to probe on how you get a better SLA writing itself than (if forced) forking an existing project (at worst)

[samsp-msft]

There seem to be 2 parts to this problem:
a) Having a library that supports the richness of functionality that you need.
b) Does that library have the required governance, reputation, maintainers etc

One of the big differences between classic .NET framework and the new OSS model that we have taken for core and .NET 5+ is to acknowledge that Microsoft can't supply every API needed by applications.

If there is a library that meets the first goal, then we can work with the .NET Foundation on the latter so that there is better governance, more maintainers, support etc.

[pgovind]

FWIW, I just want to say that I recently ported the csv parser from VB to C# for DataFrame. The DataFrame csv parser now lives in it's own file: https://github.com/dotnet/machinelearning/blob/main/src/Microsoft.Data.Analysis/TextFieldParser.cs. It can handle quotes, line breaks within quotes, errors on malformed lines etc. If someone wanted a csv reader that doesn't need a record definition to parse the files, I think TextFieldParser will go a long way. It is in no way a replacement for dedicated csv readers though.

1 option that I don't see mentioned in this discussion is using parser generators. Using, say https://github.com/antlr/antlr4, one can define their own grammar for csv, run the antlr tool, and generate a parser. A starter csv grammar is here: https://github.com/antlr/grammars-v4/blob/master/csv/CSV.g4

[frankhaugen]

I have many times wanted to see CSV data be more of a first-party supported datastructure, but the need is usually handled by very few lines of code, but I have come across issues when I am presenting that on an API, and even CsvHelper, (which is my opinion is currently the best library for CSV), has some frustrations.

Mostly what I want is to get the CSV-data into a DataTable-like structure that is flexible and typed, (but only on actual read), and it has a coordinate system so errors can be expressed as "Row 5, Cell 15". Problem is that the enormous amouts of Cultures

What the user experience might be:

var document = new CsvDocument();

// Populate document

document.GetValue<decimal>(12, 15);

Incomplete example structure:

public class CsvCell
{
    public string? Value { get; set; }
    public TypeCode Type { get; set; }
}

public class CsvCell<T> : CsvCell
{
    public T GetValue()
    {
        return default;
    }
}

public class CsvRow : List<CsvCell>
{
}

public class CsvDocument : List<CsvRow>
{
    private readonly CsvOptions _options;

    public CsvDocument(CsvOptions options)
    {
        _options = options;
    }
}

public class CsvOptions
{

    public readonly static CsvOptions Default = new();

    public string Delimiter { get; set; } = ";";
    public string QuoteStart { get; set; } = "\"";
    public string QuoteEnd { get; set; } = "\"";
    public string NewLine { get; set; } = Environment.NewLine;
    public bool HasHeaderRow { get; set; } = true;
    public bool IgnoreEmptyLines { get; set; } = true;
}

public static class CsvParser
{
    public static CsvRow ParseRow(string source, CsvOptions options = null)
    {
        options ??= CsvOptions.Default;

        var row = new CsvRow();
        // QuoteStart and QuoteEnd is not respected so a delimiter or line-break in a quote will mess things up
        var cellValues = source.Split(options.Delimiter);
        foreach (var cellValue in cellValues)
        {
            var cell = new CsvCell();
            cell.Value = cellValue;
            row.Add(cell);
        }
        return row;
    }
    
    public static CsvDocument ParseDocument(string source, CsvOptions options = null)
    {
        options ??= CsvOptions.Default;

        var document = new CsvDocument(options);
        // QuoteStart and QuoteEnd is not respected so a delimiter or line-break in a quote will mess things up
        var rowValues = source.Split(options.NewLine);
        // Header is not handled
        foreach (var rowValue in rowValues)
        {
            if (options.IgnoreEmptyLines && string.IsNullOrWhiteSpace(rowValue))
                continue;

            var row = CsvParser.ParseRow(rowValue, options);
            document.Add(row);
        }
        return document;
    }
}

[MgSam]

While you guys start planning .NET 10... it would be a great time to reconsider adding CSV parsing into .NET proper. This is still, by far, the biggest gap in the .NET BCL, in my opinion. If I had a dollar for the number of times I've seen devs (incorrectly) implement CSV parsing with a simple String.Split() I'd be retired by now.

[davidfowl]

Agreed, I’d someday we may end up needing a sufficiently advanced csv parser at a low enough level in the stack then maybe it would follow the same trajectory. Until then, we’re not going to include one.

PS: Lots of people still use JSON.NET

[neon-sunset]

@MgSam There are excellent CSV parser implementations in .NET like https://github.com/nietras/Sep/ and https://github.com/MarkPflug/Sylvan/blob/main/docs/Csv/Sylvan.Data.Csv.md both of which go way beyond the scope of what a more conservative implementation in CoreLib would do.

Not everything has to live in the standard library, .NET already implements so many features most other languages prefer to standardize on community packages for. It leads to ever increasing maintenance burden, it can also lead to code rot - an independent high-effort package is much more likely to evolve quickly and be able to make important choices that lead to good UX and/or >95th performance percentile in a way that might not fit CoreLib.

As you correctly noted, some teams ask for this as a solution to NIH or, sometimes, they also ask this because of broken third-party dependency approval/acceptance process if such is in place. However, this is not a solution to a problem which should be addressed at level it occurred at - organizational. Especially so for components that are quite replaceable like CSV parser/serializer.

[stephentoub]

It was mentioned higher up in the thread, but FWIW there is a parser included in .NET, albeit not particularly discoverable or efficient, e.g.

using Microsoft.VisualBasic.FileIO;

string text = """
    a, b, "c, d", e
    f, "g, h", i
    """;

using var parser = new TextFieldParser(new StringReader(text)) { Delimiters = [","] };
while (parser.ReadFields() is { } fields)
{
    Console.WriteLine($"{fields.Length} fields: ");
    foreach (string field in fields)
        Console.WriteLine($"\t{field}");
}

but depending on your needs, it could suffice.

[terrajobst]

@scottdorman

I think a case could be made to include it similar to how Json handling was included. That started as a third-party package, that portions of. Net depended on and even shipped with, they eventually got replaced by System.Text.Json. I think the perception, or at least the hope, in asking for built-in support is in a similar vein.

The key part is that the platform itself took a dependency on JSON, which isn't the case for CSV.

JSON isn't better because we added it to the platform; the primary benefit of doing JSON in the platform was avoiding conflicts in user graphs involving JSON.NET as well as improving the performance. We discussed doing the latter in JSON.NET directly but the maintainer (James Newton-King) had concerns that it couldn't be done with sufficient compatibility. Neither of these problems exist for CSV.

We generally strive to avoid adding platform components for cases where a vibrant ecosystem of choices exists. One aspect is avoiding the problem of Microsoft sucking out the air of the OSS community (and thus weakening the ecosystem). The other is ensuring we use our resources widely. It's just not smart to invest into a space that the ecosystem not only can provid, but is actively solving already. That almost always reeks of NIH. It makes more sense to invest in things that really need to be done in the platform layer. Or to collaborate with the existing OSS project, if there is a de facto standard.

For these reasons I believe it to be rather unlikely that we'd ever include a new CSV parser in the core libraries (as mentioned above, there is one as part of the VB support, which you can use from C#).