Reading through a csv file in C#

Question

I am using Microsoft.Jet.OleDb.4.0 to read through a csv file. I compare the information inside and if it meets certain criteria add it to a dropdown. My problem is that Microsoft.Jet.OleDb.4.0 is only compatible with x86. However, I have other functions that need to run as x64. Is there an update or an alternative to do this?

below is my code. Which currently works if I am in x86.

DataTable dataTable = new DataTable("table1");
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Directory.GetCurrentDirectory() + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""))
{
    conn.Open();
    string strQuery = "SELECT * FROM [" + "report.csv" + "]";
    OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
    adapter.Fill(dataTable);
    foreach (DataRow rows in dataTable.Rows) {
        if (rows[1].ToString() == "False")
        {
            unlicensed.Items.Add(rows[0].ToString());
        }
        if (rows[2].ToString() == "False")
        {
            litigation.Items.Add(rows[0].ToString());
        }
    }
}

Yikes. Forget OLEDB, that's a bad way to do this. http://stackoverflow.com/questions/2081418/parsing-csv-files-in-c-sharp — Stu, Jul 25 '12 at 18:27
@user541597 there's an answer in Stu's link that mentions the FileHelpers library. Recently I've been doing a lot of reading/writing of .csv files from C# and that library is a huge time saver for me. — Dan, Jul 25 '12 at 18:30

score 0 · Answer 1 · edited May 23 '17 at 11:52

I have some code in another question here that may help you:

Reading CSV files in C#

Using the class in my answer, your new code would look very close to this:

unlicensed.DataSource = CSV.FromFile("report.csv")
                     .Where(r => r[1] != "False").Select(r => r[0]);
litigation.DataSource = CSV.FromFile("report.csv")
                     .Where(r => r[2] != "False").Select(r => r[0]);

It sounds like your "False" criteria may be a little different, but that's going to be pretty close to the result. You may also want to use an Append method with your list. And to head off the other question: yes, this will read the file twice. If this really becomes a performance issue, you could write code so it only reads it once, but it would look a lot more like what you have above. Odds are, the maintenance benefit of the shorter code outweighs the performance issue here.

score 0 · Answer 2 · edited Dec 11 '13 at 16:41

Yes, there is an alternative way, instead of using Microsoft.Jet.OleDb.4.0 you can use below method..

static void Main()
        {
            string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";

            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);

            Console.WriteLine("Rows count:" + csvData.Rows.Count);

            Console.ReadLine();
        }


private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();

            try
            {

            using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }

                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }

score 0 · Answer 3 · answered Feb 03 '16 at 21:14

I had this issue recently. Not with Microsoft.Jet.OleDb.4.0, but with a dependency that requied x86 but many parts of my app needed to be run in x64. I solved it by doing 2 things:

Separating that x86 dependency into its own project. I changed the target of that one project to x86 (and left every other project target as AnyCPU - my app being deployed to a 64-bit server)
I reregistered the x86 dependency in the GAC. Still not sure why that made a big difference though, but after doing so, it worked.

My guess is that this issue would require a similar approach. This link helped me quite a bit: https://lostechies.com/gabrielschenker/2009/10/21/force-net-application-to-run-in-32bit-process-on-64bit-os/

A radical approach which I explored, worked, but ended up not going with was wrapping the x86 code in it's own executable that writes the output to the console and using Process.Start() and ProcessStartInfo.RedirectStandardOutputthough I don't recommend doing that. Was a fun experiment though.

Diego D · Answer 4 · 2012-07-25T18:41:51.403

if you don't want to be Strongly Typed you can just rely on List

        public List<List<string>> ReadCsvTable(string path) {
            List<List<string>> table = new List<List<string>>();
            string[] lines = System.IO.File.ReadAllLines(path);
            foreach (string line in lines) {
                table.Add(new List<string>(line.Split(',')));
            }
            return table;
        }

and if you want to pay much attention to the problem highlighted by a user below this should handle quoted fields I hope:

        public List<List<string>> ReadCsvTable(string path) {
            List<List<string>> table = new List<List<string>>();
            string[] lines = System.IO.File.ReadAllLines(path);
            foreach (string line in lines) {
                List<string> rawFields = new List<string>(line.Split(','));                
                List<string> processedFields = new List<string>();
                foreach(string field in rawFields){
                    Match m = Regex.Match("^\"?(^<value>.*)\"?$", line);
                    processedFields.Add(m.Groups["value"].Value);
                }                
                table.Add(processedFields);
            }
            return table;
        }

ok that was just my shallow interpration of csv. Anyway just a proof of concept to show how to read a file using the System.IO.File class and split contents with string methods. But if that's enough to downvote...well congratulations — Diego D, Jul 25 '12 at 18:34

score -1 · Answer 5 · edited Jul 25 '12 at 19:59

-1

Try to compile as a 32 bit application (Platform target: x86 in build option).

edited Jul 25 '12 at 19:59

Servy

202,030
26
332
449

answered Jul 25 '12 at 18:34

Aghilas Yakoub

28,516
5
46
51

OP: "I have other functions that need to run as x64" – Eric J. Jul 25 '12 at 19:09

Reading through a csv file in C#

5 Answers5