liv2hak liv2hak - 3 years ago 254
C# Question

Extracting a dictionary from sparse csv file

I have a sparsely populated excel file I want to extract two columns into a dictionary in C#. I have tried the following. This fails when it reads the blank lines. Is there a cleaner way to achieve the same. I don't care about any other values here. Just a mapping of

AR ID
to
AR Type
would do.

public class Table
{
private Dictionary<string, string> _ARID_ARTypeValues = new Dictionary<string, string>();
private string _arId;

public Table(string arId)
{
_arId = arId;
}

public void AddValue(string key, string value)
{
_ARID_ARTypeValues.Add(key, value);
}
}

public static IDictionary ParseCsvFile(StreamReader reader)
{
Dictionary<string, Table> tables = new Dictionary<string, Table>();

// First line contains column names.
var columnNames = reader.ReadLine().Split(',');
for (int i = 1; i < columnNames.Length; ++i)
{
var columnName = columnNames[i];
var ntable = new Table(columnName);
if ((columnName == "AR ID") || (columnName == "AR Type"))
{
tables.Add(columnName, ntable);
}
}

var line = reader.ReadLine();
while (line != null)
{
var columns = line.Split(',');

for (int j = 1; j < columns.Length; ++j)
{
var table = tables[columnNames[j]];
table.AddValue(columns[0], columns[j]);
}
line = reader.ReadLine();
}
return tables;
}


sample csv

Answer Source

I would just use a CSV library, like CsvHelper and read the csv file with that.

Dictionary<string, string> arIdToArTypeMapping = new Dictionary<string, string>();

using (var sr = File.OpenText("test.csv"))
{
    var csvConfiguration = new CsvConfiguration
    {
        SkipEmptyRecords = true
    };

    using (var csvReader = new CsvReader(sr, csvConfiguration))
    {
        while (csvReader.Read())
        {
            string arId = csvReader.GetField("AR ID");
            string arType = csvReader.GetField("AR Type");

            if (!string.IsNullOrEmpty(arId) && !string.IsNullOrEmpty(arType))
            {
                arIdToArTypeMapping.Add(arId, arType);
            }
        }
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download