How do I read lines in CSV file, sum a number of rows

Question

0.00/5 (No votes)

See more:

I formated this data using c#, streamreader and writer and created this csv file. Here is the sample data:

A------B-C-----D------------E------------F-------G-------H---------I
NEW C A123 08/24/2011 08/24/2011 100.00 100.00 X123456 276135
NEW C A125 08/24/2011 08/24/2011 200.00 100.00 X123456 276135
NEW C A127 08/24/2011 08/24/2011 50.00 100.00 X123456 276135
NEW T A122 08/24/2011 08/24/2011 5.00 100.00 X225511 276136
NEW T A124 08/24/2011 08/24/2011 10.00 100.00 X225511 276136
NEW T A133 08/24/2011 08/24/2011 500.00 100.00 X444556 276137

I would like the following output:

NEW C A123 08/24/2011 08/24/2011 100.00 100.00 X123456 276135
NEW C A125 08/24/2011 08/24/2011 200.00 100.00 X123456 276135
NEW C A127 08/24/2011 08/24/2011 50.00 100.00 X123456 276135
NEW C A001 08/24/2011 08/24/2011 350.00 100.00 X123456 276135
NEW T A122 08/24/2011 08/24/2011 5.00 100.00 X225511 276136
NEW T A124 08/24/2011 08/24/2011 10.00 100.00 X225511 276136
NEW T A001 08/24/2011 08/24/2011 15.00 100.00 X225511 276136
NEW T A133 08/24/2011 08/24/2011 500.00 100.00 X444556 276137
NEW T A001 08/24/2011 08/24/2011 500.00 100.00 X225511 276137

With each change in field "I", I would like to add a line, sum column F, add a "A001" to C, and copy the contents
of the other fields into that newly ADDed line. The letters on the columns are for illustrative purposes only. There are no headers.

First, what should I do first? How do I sum column F, copy contents of all fields, and add "A001" to C? How do I add a line and copy the fields w/ each change in I?

Thank you in advance for your help.

Posted 1-Sep-11 13:58pm

DPBNYC

Updated 1-Sep-11 14:12pm

v4

Add a Solution

Comments

DPBNYC 1-Sep-11 21:03pm

I know I left the commas out. Visualize them please.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2011-09-01T19:00:00

You commas are visualized :-). I feel pretty generous today. Do the following:

Create a class or structure representing one line (record); let's call it Record.

Use the list of those records. Literally, add:

C#

using RecordList = System.Collections.Generic.List<Record>;

For reading a file, use System.IO.StreamReader; create it under "using" statement:

C#

RecordList list = new RecordList();
using (System.IO.StreamReader reader = new System.IO.StreamReader(fileName) {
    //read lines here:
    string line = reader.ReadLine();
    //parse and validate each line
    Record record = //create instance of Record from parsed line
    list.Add(record);
} //at this point, reader.Dispose will be called automatically, even if an exception was thrown

Write a method of reading one line from a record, parsing and validating it. (It's shown as a comment above.) Start parsing using string.Split:

C#

//...
string[] values = line.Split(',', System.StringSplitOptions.RemoveEmptyEntries);
//convert strings into field of the type Record
//use DateTime.Parse, double.Parse, uint.Parse, etc. See appropriate MSDN help pages
//validate values as you parse
//for example:
record.F = double.Parse(values[5]); //may throw exception; decide how to handle it

For finding sums, iterate through the list, for example:

C#

double sumF = 0d;
/...
foreach (Record record in list) {
    sum.F += record.F;
    //...
}

In case you might need an array for some other purposes, the previous step may look alternatively:

C#

Record[] records = list.ToArray();
double sumF = 0d;
/...
foreach (Record record in records) {
    sum.F += record.F;
    //...
}

PROFIT!

—SA