How to read CSV file using c#

Question

Hello All, I am reading csv file and creating dataset.

string MyPath = System.IO.Path.GetDirectoryName(Filename);
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
//string CmdText = "select * from JDL-NurseryParts" + System.IO.Path.GetFileName(Filename);
string CmdText = "select * from ABC";
OleDbConnection Con = new OleDbConnection(ConnectionString);
OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
DataSet Ds = new DataSet();
adptr.Fill(Ds);
return Ds;

I am getting error "syntax error near from". When i opened that csv file it contained workedsheet.

File name - ABC

Workedsheet Name- ABC

I am using c#

Answer 1

In my current project, I used File Helpers, and I wasn't disappointed !

Edit: As a sample of how simple it is, here is one of the classes I'm using. Everything works with attributes. (The sample is in VB.Net, but you'll get the idea)

<DelimitedRecord(";")> _
<IgnoreFirst(1)> _
<IgnoreEmptyLines()> _
Public Class ClientsClass            
    <FieldConverter(ConverterKind.Date, "dd/M/yyyy"), FieldQuoted(QuoteMode.OptionalForBoth)> Public DateField As Date
    <FieldTrim(TrimMode.Both), FieldQuoted(QuoteMode.OptionalForBoth)> Public ClientCountry As String
    <FieldTrim(TrimMode.Both), FieldQuoted(QuoteMode.OptionalForBoth)> Public AccountId As String
    <FieldTrim(TrimMode.Both), FieldQuoted(QuoteMode.OptionalForBoth)> Public Name As String
    <FieldTrim(TrimMode.Both), FieldQuoted(QuoteMode.OptionalForBoth)> Public FNumber As String
End Class

So basically, you create a class where each field is a field in your CVS file (order DOES matter), and then you add the attributes which will tell what treatment must be applied to the field (such as trimming, converting, ...). You can also add class properties, like <DelimitedRecord(";")>, which tells which separator is used in the file, or IgnoreFirst(1), which tells the engine to not parse the first line. Every attribute is explained in their documentation, and the website has many samples for you to learn.

Then, parsing the file to the class is really easy:

Dim filePath as string = "Path to your file.csv"
Dim fhe As New FileHelperEngine(Of ClientsClass)
Dim vals as ClientClass() = CType(fhe.ReadFile(filePath), ClientClass())

And you get an array containing 1 ClientClass object per parsed line.

Note that they also built a tool which helps you to create your classes, which can be downloaded on their website.

Answer 2

I think there may a problem with your CmdText string. Try

string CmdText = "SELECT * FROM [" + System.IO.Path.GetDirectoryName(Filename) + "]";

I have used the following class to read CSV files, and it seems to work well (returns a DataTable, but you can return a DataSet just as easily:

            public System.Data.DataTable GetDataTable(string strFileName)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
            conn.Open();
            string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet("CSV File");
            adapter.Fill(ds);
            return ds.Tables[0];
        }

Hope it helps :-)