Microsoft.ACE.OLEDB.12.0 CSV ConnectionString

c# csv connection-string oledb provider
Daniel Bişar picture Daniel Bişar · Mar 3, 2011 · Viewed 42.9k times · Source

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...

Answer

almher picture almher · Nov 24, 2011

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...

Related questions

Reading CSV file and storing values into an array

I am trying to read a *.csv-file. The *.csv-file consist of two columns separated by semicolon (";"). I am able to read the *.csv-file using StreamReader and able to separate each line by using the Split() function. I want to store …

Read More
Writing data into CSV file in C#

I am trying to write into a csv file row by row using C# language. Here is my function string first = reader[0].ToString(); string second=image.ToString(); string csv = string.Format("{0},{1}\n", first, second); File.WriteAllText(filePath, csv); The whole …

Read More
How to read a CSV file into a .NET Datatable

How can I load a CSV file into a System.Data.DataTable, creating the datatable based on the CSV file? Does the regular ADO.net functionality allow this?

Read More