More Group Sites
Education Books
School Rankings
Jobless Net
Better Home
Enviro++
更好教育论坛


Help | Subscribe/Unsubscribe | Rules | Other Group Sites: Better Education | Better Education Forum
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
View
Go to last post Go to first unread
hong  
#1 Posted : Sunday, 12 April 2009 3:35:55 AM(UTC)
hong

Rank: Administration

Reputation:

Groups: AcademicCoachingSchool, admin, Administration, BookSeller, CatholicSchool, CoachingAdult, CoachingProfessional, CoachingSports, ExtraCurriculumCoaching, IndependentSchool, Moderator, MusicTeacher, PrivateSchool, PublicSchool, SelectiveSchool, tutor
Joined: 23/11/2008(UTC)
Posts: 520

CSV file Readers and Writers in C#

Questions to ask yourself first: Do you want to import it into a database? If so, which type? Or just display its contents? Make changes to it? Then select the right solution for your problem:
1. OLEDB JET driver;
2. ODBC driver;
3. StreamReader + "Split";
4. TextFieldParser
5. LINQ
6. various regular expressions;


1. OLEDB JET driver - import CSV file to DataTable
Code:
class CSVReader
{
    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];
    }
}

Code:
    private void MyFunction()
    {
        //...

        CSVReader reader = new CSVReader();
        DataTable dt = reader.GetDataTable("C:\\MyFile.csv");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            DataRow dataRow = dt.Rows[i];
            string strLastName = dataRow["LastName"].ToString();
            //...
        }
    }

2. ODBC
You can use the following connection string for accessing the CSV File.

Code:
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;" 


This will create a connection string. After opening a connection string, you can access the File as a Datatable.


3. Basic StreamReader + "Split"
Simple, but does not handle field values with commas, escaped quotes, spaces before and after fields
a.
Code:
using ( StreamReader sr = new StreamReader("MyFile.csv") ) 
{ 
    // loop through the file, line by line 
    while(sr.Peek() >= 0) { 
    // get the each line inside the loop 
    string strLine = sr.ReadLine(); 
    // split the line into columns 
    string[] columns = strLine.Split(','); 
    // ... 
} 

b.
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
public List<string[]> parseCSV(string strPath)
{
  List<string[]> strParsedData = new List<string[]>();

  try
  {
    using (StreamReader readFile = new StreamReader(strPath))
    {
      string strLine;
      string[] row;

      while ((strLine = readFile.ReadLine()) != null)
      {
        row = strLine.Split(',');
        strParsedData.Add(row);
      }
    }
  }
  catch (Exception e)
  {
    MessageBox.Show(e.Message);
  }

  return strParsedData;
}

4. TextFieldParser
Code:
// Add a reference to Microsoft.VisualBasic.
using Microsoft.VisualBasic.FileIO;

using (TextFieldParser parser = new TextFieldParser(@"c:\temp\test.csv"))
{
    parser.TextFieldType = FieldType.Delimited;
    parser.SetDelimiters(",");
    while (!parser.EndOfData) 
    {
        //Processing row
        string[] fields = parser.ReadFields();
        foreach (string field in fields) 
        {
            //TODO: Process field
        }
    }
}

// Open a TextFieldParser using these delimiters.
string[] delimiters = { ";", ",", "-" };
using (TextFieldParser parser =
    FileSystem.OpenTextFieldParser("Names.txt", delimiters))
{
    // Process the file's lines.
    while (!parser.EndOfData)
    {
        try
        {
            string[] fields = parser.ReadFields();
            foreach (string field in fields) 
            {
                //TODO: Process field
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

5. LINQ
Code:
string[] lines = File.ReadAllLines(@txtCSVFile.Text);
var csv = from line in lines
          select (line.Split(',')).ToArray();

var query = from line in lines
  let data = line.Split(',')
  select new
  {
   Col0 = data[0],
   Col1  = data[1],
   ...
  };

6. Regular expressions
Regular expressions can be used to parse the strings with various delimiters or other patterns like quotes, but it's not the nest way to parse a CSV file.
More CSV Readers and Writers
a. A Fast CSV Reader
Many implementations merely use some splitting method like String.Split(). This will, obviously, not handle field values with commas. Better implementations may care about escaped quotes, trimming spaces before and after fields, etc., but none I found were doing it all, and more importantly, in a fast and efficient manner.

b. links to other CSV readers
http://www.heikniemi.net...004/10/csv-parser-for-c/

Appendix
CSV file format - CSV files have a very simple structure):
Each record is one line (with exceptions)
Fields are separated with commas
Leading and trailing space-characters adjacent to comma field separators are ignored
Fields with embedded commas must be delimited with double-quote characters
Fields that contain double quote characters must be surrounded by double-quotes, and the embedded double-quotes must each be represented by a pair of consecutive double quotes.
A field that contains embedded line-breaks must be surrounded by double-quotes
Fields with leading or trailing spaces must be delimited with double-quote characters
Fields may always be delimited with double quotes
The first record in a CSV file may be a header record containing column (field) names

Edited by user Saturday, 27 October 2018 6:16:33 PM(UTC)  | Reason: Not specified

Sponsor
Rss Feed  Atom Feed
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.