Thursday, June 05, 2014

Parse CSV Text File to Data Table Using C#

Find below a code that parses the CSV text file and give you the records as a Data Table

 

Using Section

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

 

Methods for Parsing CSV

public static DataTable ReadCSV(String filename) {
    var csvData = new DataTable();
    StreamReader csvFile = null;
    try {
        csvFile = new StreamReader(filename);

        // Parse header
        var headerLine = csvFile.ReadLine();
        var columns = ParseCSVLine(headerLine);
        columns.ForEach(c => csvData.Columns.Add(c, typeof(String)));

        var line = "";
        while ((line = csvFile.ReadLine()) != null) {
            if (line == "") // Skip empty line
                continue;
            csvData.Rows.Add(
                ParseCSVLine(line) // Parse CSV Line
                    .OfType<Object>() // Convert it to Object List
                    .ToArray()   // Convert it to Object Array, so that it can be added to DataTable
            ); // Add Csv Record to Data Table
        }
    }
    finally {
        if (csvFile != null)
            csvFile.Close();
    }

    return csvData;
}

private static List<String> ParseCSVLine(String line) {
    var quoteStarted = false;
    var values = new List<String>();
    var marker = 0;
    var currPos = 0;
    var prevChar = '\0';

    foreach (Char currChar in line) {
        if (currChar == ',' && !quoteStarted) {
            AddValue(line, marker, currPos - marker, values);
            marker = currPos + 1;
            quoteStarted = false;
        }
        else if (currChar == '\"')
            quoteStarted = (prevChar == '\"' && !quoteStarted)
                ? true
                : !quoteStarted;
        currPos++;
        prevChar = currChar;
    }
    AddValue(line, marker, currPos - marker, values);
    return values;
}

private static void AddValue(String line, Int32 start, Int32 count, List<String> values) {
    var val = line.Substring(start, count);
    if (val == "")
        values.Add("");
    else if (val[0] == '\"' && val[val.Length - 1] == '\"')
        values.Add(val.Trim('\"'));
    else
        values.Add(val.Trim());
}

No comments:

Post a Comment