What is best way to read CSV data?

Question

Hi Guys,

I am using C# and trying to read a CSV by using this connection string;

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\rajesh.yadava\Desktop\orcad;Extended Properties="Text;HDR=YES;IMEX=1;FMT=Delimited"

This works for tab delimited data.

I want connection string which should for tab delimited as well as comma(,) and pipe(|).

How can I make a generic connection string for CSV.

Thanks Rajesh

Answer 1

Is the filehelpers library an option?

Answer 2

I know this doesn't answer your questions, but here's a word of warning.

I've had to create my own reader as you don't get the correct drivers if you ever run on a 64 bit system.

If your software will ever run on a 64 bit system, make sure you test it first and that the oledb or odbc drivers will be present.

Answer 3

Here's a few links from the net discussing this issue:

• Manipulating CSV Files
• ConnectionStrings.com
• How To Open Delimited Text Files Using the Jet Provider's Text IIsam

Answer 4

In case that you need a fast sequential access to the CSV file, the Fast CSV Reader could be an option. I have used it on a project some time ago with great success. It is supposed to be optimized quite well and also provides a cached version, if you need it. Additionally, it was updated several times since it was first released back in 2005 (last update in 2008-10-09) and it supports basic databinding by implementing System.Data.IDataReader.

Answer 5

Is using the TextFieldParser class an option?

http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx

Answer 6

Without rolling a custom solution, I'm not sure there's a straightforward way to support more than one delimiter. This page suggests that through schema.ini you can choose between:

• TabDelimited
• CSVDelimited
• one specific character (except double quote)
• fixed width