C# Read a particular value from CSV file

Question

I am a learner in C#. I want to read a particular value from the CSV file. I have learned the getting the csv file into a datatable through browsing. Please see the following code (Thanks to surendra jha) and my CSV file format. Say, I want to get what is the 'Volume' for 'ID' = 90.

CSV file

ID:Volume:Name
100:5600:A
95:5000:B
90:4500:C
85:4000:D

Code for getting all the values:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;              
namespace DVHConsolePrj
{
   class Program
     {
       static void Main(string[] args)
         {
             readCsvFileData();
          }
        static void readCsvFileData()
         {            
            string path = @"C:\IDVolumeName.txt";           
            StreamReader streamreader = new StreamReader(path);
            DataTable datatable = new DataTable();
            int rowcount = 0;
            string[] columnname = null;
            string[] streamdatavalue = null;
            while (!streamreader.EndOfStream)
            {
               string streamrowdata = streamreader.ReadLine().Trim();
                 if (streamrowdata.Length > 0) 
                    {
                       streamdatavalue = streamrowdata.Split(':'); 
                       if (rowcount == 0)
                       {
                          rowcount = 1;
                          columnname = streamdatavalue;
                          foreach (string csvheader in columnname)
                          {
                             DataColumn datacolumn = new DataColumn(csvheader.ToUpper(), typeof(string));
                             datacolumn.DefaultValue = string.Empty;
                             datatable.Columns.Add(datacolumn);
                           }
                        }
                        else
                        {
                          DataRow datarow = datatable.NewRow();
                          for (int i = 0; i < columnname.Length; i++)
                          {
                           datarow[columnname[i]] = streamdatavalue[i] == null ?             string.Empty : streamdatavalue[i].ToString();
                           }
                           datatable.Rows.Add(datarow);
                         }
                   }
          }
          streamreader.Close();
          streamreader.Dispose();                         
          foreach (DataRow dr in datatable.Rows)
          {
              string rowvalues = string.Empty;
              foreach (string csvcolumns in columnname)
              {
                  rowvalues += csvcolumns + "=" + dr[csvcolumns].ToString() + "    ";
               }
              Console.WriteLine(rowvalues);
            }
           Console.ReadLine();
           }              
        }
     }

Answer 1

Instead of parsing the file manually in a DataTable, then doing some Linq, use Linq directly on it, using this library.

It works pretty well and is very efficient with big files.

For instance.

1) Add nuget package in your project, and the following line to be able to use it:

using LINQtoCSV;

2) define the class that olds the data

public class IdVolumeNameRow
{
    [CsvColumn(FieldIndex = 1)]
    public string ID { get; set; }

    [CsvColumn(FieldIndex = 2)]
    public decimal Volume { get; set; }

    [CsvColumn(FieldIndex = 3)]
    public string Name{ get; set; }
}

3) and search for the value

    var csvAttributes = new CsvFileDescription
    {
        SeparatorChar = ':',
        FirstLineHasColumnNames = true
    };

    var cc = new CsvContext();

    var volume = cc.Read<IdVolumeNameRow>(@"C:\IDVolumeName.txt", csvAttributes)
            .Where(i => i.ID == "90")
            .Select(i => i.Volume)
            .FirstOrDefault();

Answer 2

public DataTable CSVToDataTable(string filename, string separator)
{
    try
    {
        FileInfo file = new FileInfo(filename);

        OleDbConnection con = 
            new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
            file.DirectoryName + "\";
            Extended Properties='text;HDR=Yes;FMT=Delimited(" + separator + ")';")

        OleDbCommand cmd = new OleDbCommand(string.Format
                                  ("SELECT * FROM [{0}]", file.Name), con);

        con.Open();

        DataTable tbl = new DataTable();

        using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
        {
            tbl = new DataTable("MyTable");
            adp.Fill(tbl);        
        }

        return tbl;
     }
     catch(Exception ex)
     {
         throw ex;
     }
     finally()
     {
        con.Close();
     }  
} 

You can try this code, it is build on the fly, it is possible little errors to exist. Check OleDbConnection. When you return the DataTable you can search in the table using LINQ.

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("ID") == 90
select myRow;

Here you can take the row with ID=90 !

Answer 3

For filtering DataTable you can use DataTable.Select method like this

var filtered = dataTable.Select("ID = '90'");

filtered above is array of datarow that suitable for the condition, so for get value from first filtered row you can use something like

if(filtered.Length>0){
    var Volume = filtered[0]["VOLUME"];
}