CSV Parsing Question

I have a csv file that contains 10 columns. I specifically need to use 2 of those columns: reportid and amount. I could have multiple lines for each report id. I'd like to get the sum for each report id. What is the best way to accomplish this? Thanks.

Depending on the exact set up of your csv file, something like this should work:

using System;

using System.IO;

using System.Collections.Generic;

class Program

{

static void Main()

{

string filePath = "bill_hand.csv"; // or whatever it is

int reportidColNum = 0; // first column say

int amountColNum = 1; // second column say

// it's assumed reportid is a string

var dict = new Dictionary();

char sep = ','; // column separator in csv file say

int headerLines = 1; // say

using (StreamReader sr = new StreamReader(filePath))

{

int count = 0;

string line;

while((line = sr.ReadLine()) != null)

{

count++;

if(count <= headerLines || line.Trim() == "") continue; // skip header or blank lines

string[] items = line.Trim().Split(sep);

string reportid = items[reportidColNum].Trim();

decimal amount;

decimal.TryParse(items[amountColNum].Trim(), out amount);

if(!dict.ContainsKey(reportid))

{

dict.Add(reportid, amount);

}

else

{

dict[reportid] += amount;

}

// check it worked

foreach(string id in dict.Keys)

{

Console.WriteLine("ReportId = {0}, Total Amount = {1}", id, dict[id]);

}

Console.ReadKey();

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Depending on the exact set up of your csv file, something like this should work:

using System;
using System.IO;
using System.Collections.Generic;

class Program
{
static void Main()
{
string filePath = "bill_hand.csv"; // or whatever it is
int reportidColNum = 0; // first column say
int amountColNum = 1; // second column say

// it's assumed reportid is a string
var dict = new Dictionary();
char sep = ','; // column separator in csv file say
int headerLines = 1; // say

using (StreamReader sr = new StreamReader(filePath))
{
int count = 0;
string line;

while((line = sr.ReadLine()) != null)
{
count++;
if(count <= headerLines || line.Trim() == "") continue; // skip header or blank lines
string[] items = line.Trim().Split(sep);
string reportid = items[reportidColNum].Trim();
decimal amount;
decimal.TryParse(items[amountColNum].Trim(), out amount);
if(!dict.ContainsKey(reportid))
{
dict.Add(reportid, amount);
}
else
{
dict[reportid] += amount;
}
}
}

// check it worked
foreach(string id in dict.Keys)
{
Console.WriteLine("ReportId = {0}, Total Amount = {1}", id, dict[id]);
}

Console.ReadKey();
}
}