在 .NET 中解析分隔的 CSV [英] Parse Delimited CSV in .NET

问题描述

我有一个以逗号分隔格式的文本文件，在大多数字段中由 " 分隔.我试图将其转化为我可以枚举的内容(例如，通用集合).我无法控制文件的输出方式，也无法控制用作分隔符的字符.

I have a text file that is in a comma separated format, delimited by " on most fields. I am trying to get that into something I can enumerate through (Generic Collection, for example). I don't have control over how the file is output nor the character it uses for the delimiter.

在这种情况下，字段由逗号分隔，文本字段包含在 " 标记中.我遇到的问题是某些字段中有引号(即 8" Tray) 并且不小心被捡起作为下一个字段.在数字字段的情况下，它们周围没有引号，但它们确实以 + 或 - 符号开头(表示正/负数).

In this case, the fields are separated by a comma and text fields are enclosed in " marks. The problem I am running into is that some fields have quotation marks in them (i.e. 8" Tray) and are accidentally being picked up as the next field. In the case of numeric fields, they don't have quotes around them, but they do start with a + or a - sign (depicting a positive/negative number).

我在考虑使用 RegEx，但我的技能不是很好，所以希望有人能提出一些我可以尝试的想法.该文件中大约有 19,000 条记录，因此我正在努力尽可能高效地完成它.以下是几行数据示例:

I was thinking of a RegEx, but my skills aren't that great so hopefully someone can come up with some ideas I can try. There are about 19,000 records in this file, so I am trying to do it as efficiently as possible. Here are a couple of example rows of data:

"00","000000112260   ","Pie Pumpkin                             ","RET","6.99 ","     ","ea ",+0000000006.99000
"00","000000304078   ","Pie Apple caramel                       ","RET","9.99 ","     ","ea ",+0000000009.99000
"00","StringValue here","8" Tray of Food                             ","RET","6.99 ","     ","ea ",-00000000005.3200

还有很多字段，但你可以得到图片....

There are a lot more fields, but you can get the picture....

我正在使用 VB.NET 并且我有一个通用列表设置来接受数据.我曾尝试使用 CSVReader 并且它似乎运行良好，直到您创下记录像第三个(在文本字段中引用).如果我能以某种方式让它处理额外的引号，那么 CSVReader 选项会很好用.

I am using VB.NET and I have a generic List setup to accept the data. I have tried using CSVReader and it seems to work well until you hit a record like the 3rd one (with a quote in the text field). If I could somehow get it to handle the additional quotes, than the CSVReader option will work great.

谢谢！

推荐答案

来自 这里:

Encoding fileEncoding = GetFileEncoding(csvFile);
// get rid of all doublequotes except those used as field delimiters
string fileContents = File.ReadAllText(csvFile, fileEncoding);
string fixedContents = Regex.Replace(fileContents, @"([^^,
])""([^$,
])", @"$1$2");
using (CsvReader csv =
       new CsvReader(new StringReader(fixedContents), true))
{
       // ... parse the CSV

这篇关于在 .NET 中解析分隔的 CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！