Loop through DataTable rows getting subset based on values in the next row

Question

0.00/5 (No votes)

See more:

I am reading a file into a DataTable from Excel, which is structured like so:

Date        Name        Amount      Split
01/01/2014  John Smith  $150.00     
01/01/2014  John Smith  $75.00      
01/01/2014  Bob Smith               $25.00
01/01/2014  John Smith              $50.00

The way that this is structured, the $150 line is one record, while the remaining three rows are one record as well as the two split amounts total to the $75.00. I need to process the single rows differently than the multi-line rows, so I wrote a function to go through and add a column with an ID like so:

C#

int countID = 0;

for (int index = 0; index < dataGridView1.Rows.Count; index++)
{
    if (index + 1 != dataGridView1.Rows.Count)
        if (dataGridView1.Rows[index].Cells["Split"].Value == null ||
            dataGridView1.Rows[index].Cells["Split"].Value.ToString() == "")
            countID++;

    dataGridView1.Rows[index].Cells["ID"].Value = countID.ToString();
}

And this get's me the same number for the rows that are the same, however I was thinking there might be a better way to loop through these, possibly using LINQ or something (my LINQ knowledge is very basic).

Posted 24-May-14 7:30am

hpjchobbes

Add a Solution

Comments

Maciej Los 24-May-14 15:27pm

As far i understand that you want to add ID column, which stores the same values for each Name. Am i right?

hpjchobbes 24-May-14 22:02pm

That's what I'm doing as I think it'll be easier to grab the rows that are the same to process. There are two scenarios with a row. It could either be just one row is a complete set of data, or there could be multiple rows that make up one set of data. The determining factor is if the next row doesn't have a value in the split column, then I know just the one row is complete. If the row below does have a value in the split column, then the data is comprised of 2 - N number of rows, until I get to a row that doesn't have a value in that column.

I implemented a loop through that will assign a made up ID number so I can pull the rows that represent one transaction, but if feels a little 'kludgy' and I have been looking into LINQ and was thinking there is a better way. I'm still fairly new to LINQ, though.

Maciej Los 25-May-14 6:22am

Not sure we understand each other. Row is a row and data set is data set. Please, do not mix it. One row could be a complete data set only in case when data set contains single record.
Finally, do you want to achieve the same what does your code using Linq?

hpjchobbes 25-May-14 10:13am

Sorry, when I say a set of data, I don't mean a DataSet, I mean a transaction. This data represents sales commission. A sales transaction can have one or more commission reps assigned to it. In the example, the first row is one sale, while the next three rows are a second sale. The data that I get doesn't come with the sale ID number, so the only way that I know they are related is when I come to a row and the next row doesn't have a value in the Amount field. When that happens, then all the following rows that have a value in the Split column represent one sale.

Maciej Los 25-May-14 13:10pm

Thank you for explanation. Please, see my answer.

Maciej Los 25-May-14 6:23am

By The Way: How do you load data from Excel? Using Interop ot via OLEDB?

hpjchobbes 25-May-14 10:14am

I'm using OLEDB, from this answer:
http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data-into-DataTable

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)

Maciej Los · Answer 1 · 2014-05-25T07:10:00

hpjchobbes wrote:
The data that I get doesn't come with the sale ID number, so the only way that I know they are related is when I come to a row and the next row doesn't have a value in the Amount field. When that happens, then all the following rows that have a value in the Split column represent one sale.

If you would like to manage data like a database, you need to create relationships between clients and their sales/transactions. Have a look at below 'diagram':

1 client      <----------> ∞ transactions 
1 transaction <----------> ∞ articles/products

etc.
There is no relationship like one-row-to-rows-below. Think of it: when you change sort order, you'll loose your "relationship".

Conclusion: I'd strongly recommend to read about relational database[^].