C# Reading CSV file with SQL conditions

Question

I am using CsvHelper lib to read CSV file and I can successfully read the file with the lib. However I cannot use SQL condition to filter values. How can I do that without using SQL Server. I am really stuck on it.

It was very easy with Pandas and Pandasql libs in Python but it is being too hard in C#..

My Code:

public static void Main(string[] args)
        {
            var fileInfo = new FileInfo(@"filePath");

            using (TextReader reader = fileInfo.OpenText())
            using (var csvReader = new CsvReader(reader))
            {
                csvReader.Configuration.Delimiter = ",";
                csvReader.Configuration.HasHeaderRecord = false;
                csvReader.Configuration.IgnoreQuotes = true;
                csvReader.Configuration.TrimFields = true;
                csvReader.Configuration.WillThrowOnMissingField = false;


                while (csvReader.Read())
                {
                    var myStrinVar = csvReader.GetField<string>(0);

                    Console.Write(myStrinVar); //SELECT * FROM table...
                }
            }
        }

For instance, select * from table where column_name="abc"; any filters appreciated — nuhkoca
– nuhkoca, Commented Feb 12, 2017 at 21:33
You appear to have forgotten to add the data to any kind of storage, be it an array, a List<someClass>, a database, or anything else. Perhaps your question would be better with a small example of the Python version to show what you actually want to do. Do you want to read the entire CSV file and then filter it, or filter the data before storing it? — Andrew Morton
– Andrew Morton, Commented Feb 12, 2017 at 21:41
So, you can have a look at this link for my example in Python: pastebin.com/p6wDwWY5 ex: Select DISTINCT(column_name) from table GROUP BY colum_name — nuhkoca
– nuhkoca, Commented Feb 12, 2017 at 21:43
@NuhKoca I suggest that you create a Class with properties to hold the data, make a new List<ofThatClass>, read the data into it (CsvHelper has an easy way to do that in one line), and use LINQ for the queries. There are other ways to do it. — Andrew Morton
– Andrew Morton, Commented Feb 12, 2017 at 21:53

Connell.O'Donnell · Accepted Answer · 2017-02-12 22:10:00Z

4

I would suggest using LINQ to filter your results.

https://msdn.microsoft.com/en-us/library/bb397906.aspx

Say you have some class MyClass that you can serialize the lines in your file into. For example:

public class MyClass
{
    public int ID { get; set; }
}

var records = csv.GetRecords<MyClass>().ToList();
var filtered = records.Where(r => r.ID >= 10);

That example is a bit contrived but you can use any boolean expression you like in the where clause.

answered Feb 12, 2017 at 22:10

Connell.O'Donnell

3,74312 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brandon Barkley · Accepted Answer · 2020-03-13 19:25:10Z

I know this is too late for OP, but the issue with the accepted answer is that you have to read in the entire result set to memory which may not be tenable for large files. Also, if you can extend this code below to get the top N rows without having to read the entire CSV if you find matches early in the file.

public static void Main(string[] args)
{
    var fileInfo = new FileInfo(@"filePath");
    var where = ""; //Code to set up where clause part of query goes here

    using (TextReader reader = fileInfo.OpenText())
        using (var csvReader = new CsvReader(reader))
        {
            csvReader.Configuration.Delimiter = ",";
            csvReader.Configuration.HasHeaderRecord = false;
            csvReader.Configuration.IgnoreQuotes = true;
            csvReader.Configuration.TrimFields = true;
            csvReader.Configuration.WillThrowOnMissingField = false;

            DataTable dt = null;
            while (csvReader.Read())
            {
                //Use the first row to initialize the columns.
                if (dt == null)
                {
                    dt = new DataTable();
                    for (var i = 0; i < csvReader.FieldCount; i++)
                    {
                        var fieldType = csvReader.GetFieldType(i);
                        DataColumn dc;
                        if (fieldType.IsNullableType())
                        {
                            dc = new DataColumn(csvReader.GetName(i), Nullable.GetUnderlyingType(fieldType));
                            dc.AllowDBNull = true;
                        }
                        else
                            dc = new DataColumn(csvReader.GetName(i), data.GetFieldType(i));
                        dt.Columns.Add(dc);
                    }
                }
               //Map DataReader to DataRow
               var newRow = dt.Rows.Add();
               foreach(DataColumn col in dt.Columns)
               {
                    newRow[col.ColumnName] = csvReader[col.ColumnName];
               }
            //Create a temporary DataView and filter it with the where clause.
            DataView dv = new DataView(dt);
            dv.RowFilter = where;
            var data = dv.Count > 0 ? dv[0] : null;
            if(data != null)
            {
                //Row in here matches your where clause.
                //Code to read this row or do something with it.
            }
            //Empty the temporary data table.
            dt.Rows.Clear();
        }
    }
}

Collectives™ on Stack Overflow

C# Reading CSV file with SQL conditions

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related