CODEWITHSUNDEEP
c#csvdatatableoledbdelimited
masospaghetti
masospaghetti

Reputation: 348

C# - Trouble reading a tab delimited file using Microsoft Jet OLEDB

I am trying to read an Excel .csv file that is tab delimited. There's eight columns with mixed data in each. I would post a picture of the output but I don't have enough reputation to do so (this is my first post, so bear with me). Essentially, the output table dt only contains a single column with a gibberish column heading.

Here is the code I am using:

From App.config:

<add key="cnProvider" value="Microsoft.Jet.OLEDB.4.0"/>
<add key="cnProperties" value="&quot;text;HDR=Yes;FMT=TabDelimited;CharacterSet=65001&quot;"/>

From Program.cs:

string sqlStatement_1 = ConfigurationSettings.AppSettings["sqlStatement_1"];
string cnProperties = ConfigurationSettings.AppSettings["cnProperties"];
string cnStr = "Provider=" + cnProvider + ";Data Source=" + folder + ";Extended Properties=" + cnProperties + ";";

OleDbDataAdapter Adp;
Adp = new OleDbDataAdapter(sqlStatement_1, cnStr);
using (Adp) {
Adp.Fill(dt);
}

This code worked fine on a comma-delimited file when changing FMT to Delimited instead of TabDelimited. Any ideas? The file size is less than 1000 lines, so execution speed is not a big deal. The code does not produce any exceptions.

Thanks in advance!

Upvotes: 0

Views: 1028

Answers (1)

masospaghetti
masospaghetti

Reputation: 348

I wasn't able to get the JET driver to work - I suspect there's something non-standard about my .csv file. But anyway, this is my solution, which works very well. Thanks again to everyone who responded.

public static DataTable readCSV()
    {
        //create a data table and add the column's
        DataTable table = new DataTable("table_name");
        int i = 0;
        table.Columns.Add("city", typeof(String));
        table.Columns.Add("owner", typeof(String));
        table.Columns.Add("unit_number", typeof(String));
        table.Columns.Add("equipment_desc", typeof(String));
        table.Columns.Add("wo_type", typeof(String));
        table.Columns.Add("wo_number", typeof(String));
        table.Columns.Add("wo_desc", typeof(String));
        table.Columns.Add("wo_status", typeof(String));
        table.Columns.Add("days_oos", typeof(String));

        //start reading the textfile
        StreamReader reader = new StreamReader(@"c:\...\...csv");
        string line;
        while ((line = reader.ReadLine()) != null)
        {
            if (i == 0)
            {
                //skip headings
            }
            else
            {
                string[] items = line.Split('\t');
                //make sure it has 3 items
                if (items.Length == 9)
                {
                    DataRow row = table.NewRow();
                    row["city"] = items[0];
                    row["owner"] = items[1];
                    row["unit_number"] = items[2];
                    row["equipment_desc"] = items[3];
                    row["wo_type"] = items[4];
                    row["wo_number"] = items[5];
                    row["wo_desc"] = items[6];
                    row["wo_status"] = items[7];
                    row["days_oos"] = items[8];
                    table.Rows.Add(row);
                }
            }
            i++;
        }
        reader.Close();
        reader.Dispose();

        // make use of the table
        return table;
    }

Upvotes: 2

Related Questions