CODEWITHSUNDEEP
c#csv
benjiiiii
benjiiiii

Reputation: 478

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

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.

enter image description here

The raw CSV data is below:

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

Upvotes: 0

Views: 4400

Answers (3)

user2306617
user2306617

Reputation: 61

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

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

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; }
}

Upvotes: 2

Venom
Venom

Reputation: 1106

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();

Upvotes: 1

Related Questions