CSV File with cells containing commas, Reading data into C# Program

Question

I'm writing a program to read in CSV files and validate the data. The csv file is comma delimited.

The csv file contains a sales order that is retrieved online so we can't actually edit the CSV file itself. I need to read in the file and split it into the cells. However, the product description will contain further commas which is affecting how I access the data.

My code for pulling the values out is below.

private void csvParse()
    {
        List<string> products = new List<string>();
        List<string> quantities = new List<string>();
        List<string> price = new List<string>();

        using (var reader = new StreamReader(txt_filePath.Text.ToString()))
        {
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(',');

                products.Add(values[0]);
                quantities.Add(values[2]);

                values[3] = values[3].Substring(4);
                price.Add(values[3]);
            }
        }
        if (validateData(products, quantities, price) != "")
        {
            MessageBox.Show(validateData(products, quantities, price));
        }
    }

Is there anyway to ignore the columns in a set cell or can the columns distinguished by another delimiter?

A snippet of a row in my csv file is below.

The raw CSV data is below:

TO12345,"E45 Dermatological Moisturising Lotion, 500 ml",765,GBP 1.75

TO12345,"E45 Dermatological Moisturising Lotion, 500 ml",765,GBP 1.75 — benjiiiii, Aug 02 '18 at 09:53
In the raw data it is surrounded by speech marks, and ideas how I can ammend my function to accommodate this? — benjiiiii, Aug 02 '18 at 09:55
Use something like this: https://www.nuget.org/packages/CsvHelper/ — Stevo, Aug 02 '18 at 10:06
Please google for "c# read csv" and follow ANY of the thousands of examples. There are built-in classes to handle this as well. — Davesoft, Aug 02 '18 at 10:22
I did the above @Davesoft, hence the code I have provided in my question. I was simply asking if there is a quick solution using the code I've provided. Clearly there isn't so now I will move onto trying to use the CsvHelper package as kindly suggested by Steve J — benjiiiii, Aug 02 '18 at 10:28
.Net actually has a native CSV reader, though it's somewhat hidden away if you're using C#. Add `Microsoft.VisualBasic` to your imports and you'll have access to `TextFieldParser` in the `Microsoft.VisualBasic.FileIO` namespace. — Nyerguds, Aug 06 '18 at 20:59

Cetin Basoz · Accepted Answer · 2018-08-02T11:04:30.613

You can use LinqToCSV from nuGet. ie:

void Main()
{
    List<MyData> sample = new List<MyData> {
        new MyData {Id=1, Name="Hammer", Description="Everything looks like a nail to a hammer, doesn't it?"},
        new MyData {Id=2, Name="C#", Description="A computer language."},
        new MyData {Id=3, Name="Go", Description="Yet another language, from Google, cross compiles natively."},
        new MyData {Id=3, Name="BlahBlah"},
    };
    string fileName = @"c:\temp\MyCSV.csv";

    File.WriteAllText(fileName,"Id,My Product Name,Ignore1,Ignore2,Description\n");
    File.AppendAllLines(fileName, sample.Select(s => $@"{s.Id},""{s.Name}"",""ignore this"",""skip this too"",""{s.Description}"""));

    CsvContext cc = new CsvContext();

    CsvFileDescription inputFileDescription = new CsvFileDescription
    {
        SeparatorChar = ',',
        FirstLineHasColumnNames = true, 
        IgnoreUnknownColumns=true
    };

    IEnumerable<MyData> fromCSV = cc.Read<MyData>(fileName, inputFileDescription);

    foreach (var d in fromCSV)
    {
        Console.WriteLine($@"ID:{d.Id},Name:""{d.Name}"",Description:""{d.Description}""");
    }
}

public class MyData
{
    [CsvColumn(FieldIndex = 1, Name="Id", CanBeNull = false)]
    public int Id { get; set; }
    [CsvColumn(FieldIndex = 2, Name="My Product Name",CanBeNull = false, OutputFormat = "C")]
    public string Name { get; set; }
    [CsvColumn(FieldIndex = 5, Name="Description",CanBeNull = true, OutputFormat = "C")]
    public string Description { get; set; }
}

Awesome thank you! Only issue I'm having now is, the column names in my CSV file may have a space in. So the product code header is "Model Number". I've got your code working by removing the space and naming the field in the MyData class the same. Is there anyway of naming the field in MyData with a space in it? — benjiiiii, Aug 02 '18 at 10:50
@benjiiiii, I think there is, I don't know all the settings of LinqToCSV off the top of my head. You can check its samples and tests on its GitHub page. — Cetin Basoz, Aug 02 '18 at 10:53
@benjiiiii, hold on. There is control for it an more. I will update code soon. — Cetin Basoz, Aug 02 '18 at 10:57
@benjiiiii, edited to show with attributes, skipping columns and allowing null, field ordering and naming... — Cetin Basoz, Aug 02 '18 at 11:05

score 1 · Answer 2 · answered Aug 02 '18 at 10:50

It should work..:)

var csvSplit = new Regex("(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)", RegexOptions.Compiled);
            string[] csvlines = File.ReadAllLines(txt_filePath.Text.ToString());

            var query = csvlines.Select(csvline => new
            {
                data = csvSplit.Matches(csvline)
            }).Select(t => t.data);

            var row = query.Select(matchCollection =>
                (from Match m in matchCollection select (m.Value.Contains(',')) ? m.Value.Replace(",", "") : m.Value)
                .ToList()).ToList();

score 0 · Answer 3 · answered Feb 25 '21 at 11:29

0

You can also use the Microsoft.VisualBasic.FileIO.TextFieldParser class. More detailed answer here: TextFieldParser

answered Feb 25 '21 at 11:29

user2306617

61
1
1
6

CSV File with cells containing commas, Reading data into C# Program

3 Answers3

Linked