Microsoft.ACE.OLEDB.12.0 CSV ConnectionString

Question

I know questions this kind are asked from time to time but i can't find any satisfying solution.

How can I open a CSV-File using MS ACE OLEDB 12? I try it with the following code.

DbConnection connection = new OleDbConnection();
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Documents;Extended Properties=\"Text;HDR=Yes\"";
connection.Open();
DbCommand cmd;

cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [Mappe1#csv]";
DbDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    for (int i = 0; i < reader.FieldCount; i++)
        Console.Write("(" + reader.GetValue(i).ToString() + ")");

    Console.WriteLine();
}

cmd.Dispose();
connection.Dispose();
Console.WriteLine("Done");
Console.ReadKey();

The Problem is that only one column is found. The Text is delimited by ';'. Even when i specifiy the delimiter with "Delimited(|)" f.e. it will not work.

I can't find any documentation for this provider...

Sayed

We are moving more and more away from ACE. It has a lot problem (almost no documentation, no support, issues with specific characters in the data or worksheet names, ...). We found out that it is faster, easier and more reliable to use Interop instead, if you have a clean design, than ACE. Additional for CSV we are using another API (LumenWorks CSV reader: codeproject.com/Articles/9258/A-Fast-CSV-Reader) – Teno

Answer 1

This helped me getting a semicolon-delimited csv to parse in C# using ACE.OLEDB.12.0: http://sqlserverpedia.com/blog/sql-server-bloggers/use-ace-drivers-and-powershell-to-talk-to-text-files/:

Create a schema.ini text file in the same directory as the csv file you want to import with the following contents:

[fileIwantToImport.csv]
Format=Delimited(;)
ColNameHeader=True

Worked for me. But so yucky.

Seems like the FORMAT=Delimited(;) in the connection string has gone out of fashion...

Requiescat

Answer 2

Try:

connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Documents;Extended Properties=\"Text;HDR=Yes;FORMAT=Delimited\"";

(inserting "FORMAT=Delimited" into the extended properties of the connection string...)

Artefact

Nope; no change. I also tried FMT=TabDelimited (for a Tab file). – Teno

Hard to say then... That is the exact connection string I use in my project to read a csv and it works like a charm. The only other difference would be in the select statement. I have "select * from file.csv", specifying the filename directly. I haven't seen the "[mappe1#csv]" syntax before -- is the name of the file "mappe1#csv" or is that just another way to specify "mappe1.csv"? I've had it fail before if the file extension isn't ".csv". – Artefact

I will give it a try. mappe1#csv is the name which will be provided by connection.GetSchema(). – Teno

Another question: which format had your file? Seperated with ;? Newline as \n and which encoding? I generated the file with Excel Office 2007... – Teno

My files are comma-delimited, using " as a text qualifier. Not sure about encoding -- probably UTF-8. I am able to read files saved as CSV from Excel no problem. – Artefact

I still have no solution. Currently I haven't time to look at this problem. Maybe later... – Teno

The problem isn't HOW to import a text file, it's how to import it with a non-comma delimiter! – Factitious

Answer 3

Have you considered creating DataSet?

    public static DataSet ConvertTabFiles(string File, string TableName, string delimiter)
    {
        //The DataSet to Return
        DataSet result = new DataSet();

        //Open the file in a stream reader.
        StreamReader s;
        try
        {
            s = new StreamReader(@File);
        }
        catch
        {
            MessageBox.Show("Can't perform operation on file: " + File);
            return result;
        }

        //Split the first line into the columns  
        string[] columns = null;
        try
        {
            columns = s.ReadLine().Split(delimiter.ToCharArray());
        }
        catch
        {
            MessageBox.Show("Can't parse the file " + File + ", please try again!");
            return result;
        }

        //Add the new DataTable to the RecordSet
        result.Tables.Add(TableName);
        //MessageBox.Show("Add the new DataTable to the RecordSet");

        //Cycle the colums, adding those that don't exist yet 
        //and sequencing the one that do.
        foreach (string col in columns)
        {
            bool added = false;
            string next = "";
            int i = 0;
            while (!added)
            {
                //Build the column name and remove any unwanted characters.
                string columnname = col + next;

                //See if the column already exists
                if (!result.Tables[TableName].Columns.Contains(columnname))
                {
                    //if it doesn't then we add it here and mark it as added
                    result.Tables[TableName].Columns.Add(columnname);
                    added = true;
                }
                else
                {
                    //if it did exist then we increment the sequencer and try again.
                    i++;
                    next = "_" + i.ToString();
                }
            }
        }

        //Read the rest of the data in the file.        
        string AllData = s.ReadToEnd();

        string[] rows = AllData.Split("\r\n".ToCharArray());

        //Now add each row to the DataSet        
        foreach (string r in rows)
        {
            //Split the row at the delimiter.
            string[] items = r.Split(delimiter.ToCharArray());
            //Add the item
            result.Tables[TableName].Rows.Add(r);
        }
        //Return the imported data.
        return result;
    }

Epirogeny

This is like to implement your own CSV-Reader. We currently are using another CSV-Reader. – Teno

This will fail on data with embedded delimiters, e.g. a comma inside of what should be a single field in a CSV file. – Ragen