csv reading using OLEDB command

Question

Am trying to read a csv file using oledb command. Following is am using. This code execute smoothly. But i need to remove first row from csv file and set second row as column header of the datatable. Is it possible?

static DataTable ImportCsvFileToDataTable(string filename, string fullPath)
{
    FileInfo file = new FileInfo(fullPath);

    using (OleDbConnection con =
               new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
               file.DirectoryName + "\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
    {
        using (OleDbCommand cmd = new OleDbCommand(string.Format
                                  ("SELECT * FROM [{0}]", file.Name), con))
        {
            con.Open();

            // Using a DataTable to process the data
            using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
            {
                DataTable tbl = new DataTable(filename);
                adp.Fill(tbl);
                return tbl;
            }
        }
    }
}

Taosique · Accepted Answer · 2014-01-22 14:45:40Z

I shed much blood trying to develop the perfect method to import CSV's into DataTable. Here's the result. It parses first row as column headers. This method works with Ace OleDB 12, but should with no problem work with Jet OleDB 4 as well.

public static DataTable FromCSV(string FilePath, char Delimiter = ',')
{
    DataTable dt = new DataTable();
    Dictionary<string, string> props = new Dictionary<string, string>();

    if (!File.Exists(FilePath))
        return null;

    if (FilePath.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
    {
        props["Provider"] = "Microsoft.Ace.OLEDB.12.0";
        props["Extended Properties"] = "\"Text;FMT=Delimited\"";
        props["Data Source"] = Path.GetDirectoryName(FilePath);
    }
    else
        return null;

    StringBuilder sb = new StringBuilder();

    foreach (KeyValuePair<string, string> prop in props)
    {
        sb.Append(prop.Key);
        sb.Append('=');
        sb.Append(prop.Value);
        sb.Append(';');
    }

    string connectionString = sb.ToString();

    File.Delete(Path.GetDirectoryName(FilePath) + "/schema.ini");
    using (StreamWriter sw = new StreamWriter(Path.GetDirectoryName(FilePath) + "/schema.ini", false))
    {
        sw.WriteLine("[" + Path.GetFileName(FilePath) + "]");
        sw.WriteLine("Format=Delimited(" + Delimiter + ")");
        sw.WriteLine("DecimalSymbol=.");
        sw.WriteLine("ColNameHeader=True");
        sw.WriteLine("MaxScanRows=1");
        sw.Close();
        sw.Dispose();
    }

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM [" + Path.GetFileName(FilePath) + "] WHERE 1=0";
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(dt);

        using (StreamWriter sw = new StreamWriter(Path.GetDirectoryName(FilePath) + "/schema.ini", true))
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string NewColumnName = dt.Columns[i].ColumnName.Replace(@"""", @"""""");
                int ColumnNamePosition = NewColumnName.LastIndexOf("#csv.", StringComparison.OrdinalIgnoreCase);
                if (ColumnNamePosition != -1)
                    NewColumnName = NewColumnName.Substring(ColumnNamePosition + "#csv.".Length);
                if (NewColumnName.StartsWith("NoName"))
                    NewColumnName = "F" + (i + 1).ToString();
                sw.WriteLine("col" + (i + 1).ToString() + "=" + NewColumnName + " Text");
            }   
            sw.Close();
            sw.Dispose();
        }

        dt.Columns.Clear();
        cmd.CommandText = "SELECT * FROM [" + Path.GetFileName(FilePath) + "]";
        da = new OleDbDataAdapter(cmd);
        da.Fill(dt);
        cmd = null;
        conn.Close();
    }

    File.Delete(Path.GetDirectoryName(FilePath) + "/schema.ini");
    return dt;
}

mrpotocnik · Accepted Answer · 2014-01-22 14:48:08Z

0

Perhaps something like this:

    tbl.Rows[0].Delete();
    DataRow r = tbl.Rows[0];
    foreach(DataColumn c in tbl.Columns)
    {
        tbl.Columns[c.ColumnName].ColumnName = r[c.ColumnName].ToString();

     }
     tbl.Rows[0].Delete();

answered Jan 22, 2014 at 14:48

mrpotocnik

1,1511 gold badge10 silver badges16 bronze badges

Comments

Heinzi · Accepted Answer · 2014-01-22 15:11:29Z

0

There is no built-in way to tell OLEDB to use the second row for the headers instead of the first row.

You will have to open the CSV file, remote the first row, and save it again. Fortunately, this is fairly easy to do:

var lines = File.ReadAllLines(oldFileName);
File.WriteAllLines(newFileName, lines.Skip(1));

(Add .ToArray() after Skip(1) if you are using a .NET version < 4.)

edited Jan 22, 2014 at 15:11

answered Jan 22, 2014 at 14:49

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Collectives™ on Stack Overflow

csv reading using OLEDB command

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related