Reading a csv file and storing the data in a 2-D object array

Question

First, if the csv file (eg: Date and Price) contains some empty cells, how can I solve this problem when I try to store the data in a list?

Second, once the two lists are okay I'd like to know how to create a 2-D object such as PriceByDate[100,2](Date[100,0],Price[100,1]) which can store the data from the csv file.

Thanks in advance

Here is my code:(Not work)

var reader = new StreamReader(File.OpenRead(@"filenames"));

List<string> Dates = new List<string>();
List<string> Prices = new List<double>();
while (!reader.EndOfStream)
{
    var line = reader.ReadLine();
    var values = line.Split(';');

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

DateTime[] s = Convert.ToDateTime(ListA.toArray());
double[] o = ListB.toArray();
object[,] PriceByDate = new object[,]{{s},{o}};

Answer 1

First, a recommendation:

I would recommend using a free 3rd-party CSV library, rather than reinventing the wheel, unless there is some reason you can't.

http://joshclose.github.io/CsvHelper/

Now, the answer:

It sounds like your problem is how to handle empty cells. First, you need to make sure each row is the correct length, in a CSV you'll at least get a delimited between each cell, even if they're empty. (NOTE: I wrote all of this code long-hand without an IDE, it likely won't compile as-is, there may be errors).

var line = reader.ReadLine();
var values = line.Split(';');
if (values.Count != numColumnsExpected)
{
    throw new System.Exception("Expected " + numColumnsExpected + " columns, only found " + values.Count + " columns for a row.");
}

Each column should have an expected type, you could have a validation and processing function for each column if you want to be thorough. You could map the column number to a function in a Dictionary.

private void ProcessorDelegate(string value);

Dictionary<int, ProcessorDelegate> m_processorMethods = new Dictionary<int, ProcessorDelegate>
{
    { 0, DateProcessor },
    { 1, PriceProcessor },
}

private void DateProcessor(string value)
{
    // Make sure 'value' is a date
    DateTime date;
    if (!DateTime.TryParse(value, out date))
    {
        // If this field is required you could throw an exception here, or output a console error.
        // This is the point at which you could check if 'value' was null or empty.

        return;
    }

    // 'value' was a date, so add it to the DateTime[] array.
    Dates.Add(date);
}

int numColumnsExpected = 6;

var Dates = new List<string>();
var Prices = new List<double>();

while (!reader.EndOfStream)
{
    var line = reader.ReadLine();
    var values = line.Split(';');

    if (values.Count != numColumnsExpected)
    {
         throw new System.Exception("Expected " + numColumnsExpected + " columns, only found " + values.Count + " columns for a row.");
    }

    // Sanity check, you must have a processor for each column
    if (values.Count > m_processorMethods.Count)
    {
         throw new System.Exception("Expected " + numColumnsExpected + " processor methods, only found " + m_processorMethods.Count);
    }

    for (int i = 0; i < values.Count; ++i)
    {
        // Pass the value for a column to the processor that handles
        // data for that column.
        m_processorMethods[i](values[i]);
    }
} 
   DateTime[] s=Convert.ToDateTime(ListA.toArray());
   double[] o=ListB.toArray();
   object[,] PriceByDate=new object[,]{{s},{o}} ;
}

Warning:

Storing your data in a series of 2D arrays that are supposed to all map to one another by indices, is very fragile. Even storing it in a 2D object array isn't very useful because you'll need to cast those objects to make any use of them, and you'd need to know what data type each column was in order to cast them anyway.

I would highly recommend creating a class that holds the data for a row. Within that class you can store the date, price, and whatever other data you need. Then you can just have a List or array of those objects, each object representing a row.

public class RowObject
{
    public DateTime date;
    public string price;
}

List<RowObject> m_rowData;

// A delegate that can take the RowObject
private void ProcessorDelegate(string value, RowObject currentRow);

// Pass in the RowObject to your processors
// The processor updates the RowObject with the processed information.
private void DateProcessor(string value, RowObject currentRow)
{
    // Make sure 'value' is a date
    DateTime date;
    if (!DateTime.TryParse(value, out date))
    {
        // If this field is required you could throw an exception here, or output a console error.
        // This is the point at which you could check if 'value' was null or empty.

        return;
    }

    // 'value' was a date, so set this row's date
    currentRow.date = date;
}

Now all of your data for a row is tied together nicely, and if there are empty cells then that row's RowObject is missing that data. You could easily validate a row by adding a validation method to RowObject.

public class RowObject
{
    public DateTime date;
    public string price;

    public bool IsValid()
    {
        if (date == null)
        {
            // Maybe output a warning to console here
            return false;
        }

        if (string.IsNullOrEmpty(price))
        {
            // Maybe output a warning to console here
            return false;
        }

        return true;
    }
}

Finally

Let me reiterate that a lot of this in reinventing the wheel, if you use the CSVHelper library I provided a link to then you don't need most of this code.

Answer 2

This article show how to use ADO.net to read a CSV file. It is pretty simple. Using this method your Date and Price information will be on a row/record object already paired.

Article: Reading CSV files in ADO.NET

If you use the above articles solution, all you need to do is a simple string.IsNullOrEmpty test on the fields of the record. If the function returns true you can skip the row/record.

I'm not sure if this is what you are looking for as in a "2-D" object. If you need to create an object in your code to hold the data after reading it from the record I would use something like this. On a side note you may also want to use a decimal for holding monetary values.

public class PriceAndDate
{
    public DateTime Date {get;set;}
    public Decimal Price {get;set;}
}

List<PriceAndDate> priceAndDate = new List<PriceAndDate>();