C # Reading a specific value from a CSV file
I am a contributor to C #. I want to read a specific value from a CSV file. I found out how to get a csv file in a datatable via view. Have a look at the following code (thanks to surendra jha) and my CSV file format. Let's say I want to get what is "Volume" for "ID" = 90.
CSV file
ID:Volume:Name
100:5600:A
95:5000:B
90:4500:C
85:4000:D
Code to get all 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();
}
}
}
source to share
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 builds on the fly, maybe there are small bugs. Check out OleDbConnection. When you return a DataTable, you can search the table using LINQ.
var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("ID") == 90
select myRow;
Here you can take the line with ID = 90!
source to share
Instead of parsing the file manually into a DataTable, then using Linq, use Linq directly on it using this library .
It works very well and is very efficient with large files.
For example.
1) Add nuget package to your project and the following line to use it:
using LINQtoCSV;
2) define a class that ages 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 find 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();
source to share
For filtering DataTable you can use DataTable.Select method like this
var filtered = dataTable.Select("ID = '90'");
filtered
above is a datarow array suitable for the condition, so to get the value from the first filtered row you can use something like
if(filtered.Length>0){
var Volume = filtered[0]["VOLUME"];
}
source to share