C# .NET - Can I fire a sql query with where clause on csv file

Asked By Tejaswini Prashant J on 21-Jan-10 12:08 AM

Hi,
I have a csv file which contain a value operator Id. I want to access the records fro the csv file, only of a perticular operator.. How can do that?

re - Santhosh N replied to Tejaswini Prashant J on 21-Jan-10 12:56 AM

There is no direct way of handling the queries from csv files as its basically considered to be a flat file and you could actually load the csv file either to datatable or xml and then query from there which could be easier as well

you could check here on how to read the parse csv files and load into datatbles..

http://www.switchonthecode.com/tutorials/building-a-simple-csv-parser-in-csharp

http://www.akamarketing.com/blog/256-csv-datatable.html

solution for reference - Tejaswini Prashant J replied to Tejaswini Prashant J on 21-Jan-10 01:01 AM

Hi,
   I found a solution finally... adding here to help others ..

//This is the connection string to connect to your csv file
                    string strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + _tempFilePath  + "; Extended Properties='Text;HDR=YES;'";
                    // open connection
                    OleDbConnection oCon = new OleDbConnection(strConString);
                    oCon.Open();

                    // fill data set
                    string strSql = "SELECT * FROM loggedinusersessioninfo.csv Where OperatorId =3"; //csvfile should be present in c:
                    OleDbDataAdapter oDA = new OleDbDataAdapter(strSql, oCon);
                    DataSet oData = new DataSet();
                    oDA.Fill(oData, "ABC");
                    oCon.Close();

 

fire a sql query with where clause on csv file - Sagar P replied to Tejaswini Prashant J on 21-Jan-10 01:02 AM

I think its somewhat difficult to fire where clause with query on CSV file.

So you need to think about any other alternative like; once you get all data in DataSet you can sort it by using data view according to ID you want to use in where clause and you can get data of perticulat ID only, something like this. so you need to do some workaround like this;

See this code to FILL ds from csv data;

using System;
using System.Data;
using System.IO; //not used by default
using System.Data.OleDb; //not used by default

namespace CSVParserExample
{
  class CSVParser
  {
    public static DataTable ParseCSV(string path)
    {
      if (!File.Exists(path))
        return null;

      string full = Path.GetFullPath(path);
      string file = Path.GetFileName(full);
      string dir = Path.GetDirectoryName(full);

      //create the "database" connection string
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        + "Data Source=\"" + dir + "\\\";"
        + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

      //create the database query
      string query = "SELECT * FROM " + file;

      //create a DataTable to hold the query results
      DataTable dTable = new DataTable();

      //create an OleDbDataAdapter to execute the query
      OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

      try
      {
        //fill the DataTable
        dAdapter.Fill(dTable);
      }
      catch (InvalidOperationException /*e*/)
      { }

      dAdapter.Dispose();

      return dTable;
    }

hi - Tejaswini Prashant J replied to Sagar P on 22-Jan-10 03:31 AM

Hi,
thanks for reply..
can u tell me one thing can I fire a inner join query on csv ?