Reading CSV file and storing values into an array

Question

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 each column into a separate array and then display it.

Is it possible to do that?

Answer 1

You can do it like this:

using System.IO;

static void Main(string[] args)
{
    using(var reader = new StreamReader(@"C:\test.csv"))
    {
        List<string> listA = new List<string>();
        List<string> listB = new List<string>();
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            var values = line.Split(';');

            listA.Add(values[0]);
            listB.Add(values[1]);
        }
    }
}

Answer 2

Still wrong. You need to compensate for "" in quotes. Here is my solution Microsoft style csv.

               /// <summary>
    /// Microsoft style csv file.  " is the quote character, "" is an escaped quote.
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="sepChar"></param>
    /// <param name="quoteChar"></param>
    /// <param name="escChar"></param>
    /// <returns></returns>
    public static List<string[]> ReadCSVFileMSStyle(string fileName, char sepChar = ',', char quoteChar = '"')
    {
        List<string[]> ret = new List<string[]>();

        string[] csvRows = System.IO.File.ReadAllLines(fileName);

        foreach (string csvRow in csvRows)
        {
            bool inQuotes = false;
            List<string> fields = new List<string>();
            string field = "";
            for (int i = 0; i < csvRow.Length; i++)
            {
                if (inQuotes)
                {
                    // Is it a "" inside quoted area? (escaped litteral quote)
                    if(i < csvRow.Length - 1 && csvRow[i] == quoteChar && csvRow[i+1] == quoteChar)
                    {
                        i++;
                        field += quoteChar;
                    }
                    else if(csvRow[i] == quoteChar)
                    {
                        inQuotes = false;
                    }
                    else
                    {
                        field += csvRow[i];
                    }
                }
                else // Not in quoted region
                {
                     if (csvRow[i] == quoteChar)
                    {
                        inQuotes = true;
                    }
                    if (csvRow[i] == sepChar)
                    {
                        fields.Add(field);
                        field = "";
                    }
                    else 
                    {
                        field += csvRow[i];
                    }
                }
            }
            if (!string.IsNullOrEmpty(field))
            {
                fields.Add(field);
                field = "";
            }
            ret.Add(fields.ToArray());
        }

        return ret;
    }
}

Answer 3

LINQ way:

var lines = File.ReadAllLines("test.txt").Select(a => a.Split(';'));
var csv = from line in lines
          select (from piece in line
                  select piece);

^^Wrong - Edit by Nick

It appears the original answerer was attempting to populate csv with a 2 dimensional array - an array containing arrays. Each item in the first array contains an array representing that line number with each item in the nested array containing the data for that specific column.

var csv = from line in lines
          select (line.Split(',')).ToArray();

Answer 4

You can use Microsoft.VisualBasic.FileIO.TextFieldParser dll in C# for better performance

get below code example from above article

static void Main()
{
    string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";

    DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);

    Console.WriteLine("Rows count:" + csvData.Rows.Count);

    Console.ReadLine();
}


private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
    DataTable csvData = new DataTable();

    try
    {

    using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
        {
            csvReader.SetDelimiters(new string[] { "," });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == "")
                    {
                        fieldData[i] = null;
                    }
                }
                csvData.Rows.Add(fieldData);
            }
        }
    }
    catch (Exception ex)
    {
    }
    return csvData;
}

Answer 5

I usually use this parser from codeproject, since there's a bunch of character escapes and similar that it handles for me.

Answer 6

Hi all, I created a static class for doing this. + column check + quota sign removal

public static class CSV
{
    public static List<string[]> Import(string file, char csvDelimiter, bool ignoreHeadline, bool removeQuoteSign)
    {
        return ReadCSVFile(file, csvDelimiter, ignoreHeadline, removeQuoteSign);
    }

    private static List<string[]> ReadCSVFile(string filename, char csvDelimiter, bool ignoreHeadline, bool removeQuoteSign)
    {
        string[] result = new string[0];
        List<string[]> lst = new List<string[]>();

        string line;
        int currentLineNumner = 0;
        int columnCount = 0;

        // Read the file and display it line by line.  
        using (System.IO.StreamReader file = new System.IO.StreamReader(filename))
        {
            while ((line = file.ReadLine()) != null)
            {
                currentLineNumner++;
                string[] strAr = line.Split(csvDelimiter);
                // save column count of dirst line
                if (currentLineNumner == 1)
                {
                    columnCount = strAr.Count();
                }
                else
                {
                    //Check column count of every other lines
                    if (strAr.Count() != columnCount)
                    {
                        throw new Exception(string.Format("CSV Import Exception: Wrong column count in line {0}", currentLineNumner));
                    }
                }

                if (removeQuoteSign) strAr = RemoveQouteSign(strAr);

                if (ignoreHeadline)
                {
                    if(currentLineNumner !=1) lst.Add(strAr);
                }
                else
                {
                    lst.Add(strAr);
                }
            }

        }

        return lst;
    }
    private static string[] RemoveQouteSign(string[] ar)
    {
        for (int i = 0;i< ar.Count() ; i++)
        {
            if (ar[i].StartsWith("\"") || ar[i].StartsWith("'")) ar[i] = ar[i].Substring(1);
            if (ar[i].EndsWith("\"") || ar[i].EndsWith("'")) ar[i] = ar[i].Substring(0,ar[i].Length-1);

        }
        return ar;
    }

}