CSV support in .NET Core #37711
Replies: 12 comments 47 replies
-
|
@sparticus1701 have you seen this https://devblogs.microsoft.com/dotnet/an-introduction-to-dataframe/ ? |
Beta Was this translation helpful? Give feedback.
-
|
Does DataFrame help @sparticus1701 ? |
Beta Was this translation helpful? Give feedback.
-
|
No, it doesn't. |
Beta Was this translation helpful? Give feedback.
-
|
Does DataFrame have a CSV writer? |
Beta Was this translation helpful? Give feedback.
-
|
FWIW, I wrote my own CSV reader/writer because I found the support in .NET lacking. One of the copies can be found here. |
Beta Was this translation helpful? Give feedback.
-
It will once dotnet/corefxlab#2947 is merged. |
Beta Was this translation helpful? Give feedback.
-
@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? 😎 |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
Generally we want to support community libraries. Could you share more about the issues you have with them? |
Beta Was this translation helpful? Give feedback.
-
|
This feels like a sales plug for your own work, but there's quite a few holes in your logic:
CSV files are not exclusively used for relational databases. IDataReader is not the best choice.
It's the perfect reason to get MS involved.
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.
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.
I would trust Microsoft, the .NET team and community any day over a single person, or 3rd party group.
Of course it does, because it standardizes and consolidates under a single community which has the resources for continuous improvement.
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.
Blatant self-promotion |
Beta Was this translation helpful? Give feedback.
-
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. |
Beta Was this translation helpful? Give feedback.
-
|
Let’s focus on the content not the person please. 🙂 |
Beta Was this translation helpful? Give feedback.
-
Sales plug implies that I'm asking for money. It's open source. What's wrong with me promoting my own hard work?
Then what is the best choice?
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.
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.
Does it now? https://xkcd.com/927/
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. |
Beta Was this translation helpful? Give feedback.
-
I just used CsvHelper and used a custom CultureInfo to make it output dates as ISO-8601. CultureInfo.InvariantCulture doesn't do that. |
Beta Was this translation helpful? Give feedback.
-
|
Have you looked at the |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
Historical accident. There are a few other unique VB API that are interesting beyond VB. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
It's simply split with |
Beta Was this translation helpful? Give feedback.
-
|
Except it isn't. This is valid CSV and represents a a table with two rows and two columns. |
Beta Was this translation helpful? Give feedback.
-
|
Hm, your right. This would great, if it works. But I think the most people uses normal splited CSV. |
Beta Was this translation helpful? Give feedback.
-
|
Right, which is why people are asking for a proper parser :-) |
Beta Was this translation helpful? Give feedback.
-
|
It's far past time for .NET to have a proper CSV parsing library.
The BCL should fix this shortcoming and make a standard CSV parser implementation everyone can use. |
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
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.
I don't think you'd expect people to use the IDataReader directly. They would have an instance, and simply hand it to the 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
"Simple", "underestimate", "buggy", all referring to the same task; does not compute. |
Beta Was this translation helpful? Give feedback.
-
.... this depends completely on audience. In a web api, current trend is to JSON (hence 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. |
Beta Was this translation helpful? Give feedback.
-
|
Not sure I agree that CSV can't handle "dynamic" or "nested" data. Tabular data can represent anything. |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
Somewhat related: https://github.com/kevin-montrose/Cesil |
Beta Was this translation helpful? Give feedback.
-
|
Also related: |
Beta Was this translation helpful? Give feedback.
-
|
@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? |
Beta Was this translation helpful? Give feedback.
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
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.
|
Beta Was this translation helpful? Give feedback.
-
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) |
Beta Was this translation helpful? Give feedback.
-
|
There seem to be 2 parts to this problem: 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. |
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
-
|
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;
}
} |
Beta Was this translation helpful? Give feedback.
-
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.
Beta Was this translation helpful? Give feedback.
All reactions