Moving data between datatable columns

[bd528]

Hello,

I have a datatable with 2 columns, with the following structure


[TABLE="width: 500"]
[TR]
[TD]Column1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

What I'd like to do is move the data from column 2 into column 1, leaving

[TABLE="width: 500"]
[TR]
[TD]Column 1[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[/TR]
[/TABLE]

Please could someone help with with the code to move the data from column 2.

Many thanks.

 

[jmcilhinney]

You most likely already know how to do this. Have you ever looped through the Rows of a DataTable before? That is the first step. Inside the loop you will get the data from one field, which I'm guessing that you've already done before, and then set the data of the other field, which I'm also guessing that you have done before. Many programming problems involve taking small tasks that you have already done and putting them together in a new way. Kind of like life really. For instance, if you can cook one meal then there's every chance you can cook a lot of meals, because the basic principles of cooking apply to all meals. You just have to adjust the details.

 

[bd528]

You most likely already know how to do this. Have you ever looped through the Rows of a DataTable before? That is the first step. Inside the loop you will get the data from one field, which I'm guessing that you've already done before, and then set the data of the other field, which I'm also guessing that you have done before. Many programming problems involve taking small tasks that you have already done and putting them together in a new way. Kind of like life really. For instance, if you can cook one meal then there's every chance you can cook a lot of meals, because the basic principles of cooking apply to all meals. You just have to adjust the details.

Thanks for that. To use your analogy, I can cook, but I don't know the best mthod to cook every meal. It's quicker to cook bacon in the microwave than under the grill. I will try looping through the cells once I figure out the syntax. I was hoping there was a way to move all the data from column 2 to column 1 in one chunk, with a single command.

 

[jmcilhinney]

Your first post gives no indication that you might know one way of doing it but were hoping for a simpler way. We only know what you tell us. There is a way that you could do it in one line of code but that one line wouldn't be significantly simpler than the alternative and you wouldn't understand it if you don't understand the alternative anyway.

 

[bd528]

Your first post gives no indication that you might know one way of doing it but were hoping for a simpler way. We only know what you tell us. There is a way that you could do it in one line of code but that one line wouldn't be significantly simpler than the alternative and you wouldn't understand it if you don't understand the alternative anyway.

You make a good point. Unfortunately, in the past on different boards, I have spent a great deal of time explaining my problem and past experience in an initial post, only to have the post unanswered, which can be demoralising. Personally, I prefer to state my issue, take a person’s solution and learn from that.
There may a perfect vb.net command called “MoveDataFromColAToColB”, but unless I know that command exists, I can’t look it up. I thought it would make sense to ask here, find the code to do it, and in the future I would know what to do. I guess people learn in different ways.
I have spent a fair amount of time trying to work out how to do this before posting – to no avail. Someone may kindly spend 3 minutes showing me what I need. Or I can spend more time searching a command I don’t know exists.

 

[jmcilhinney]

Fair enough. So are you saying that you don't know how to perform those three elementary steps I mentioned earlier? Have you performed For Each loops before? Have you moved data into and out of a DataTable before?

 

[bd528]

Fair enough. So are you saying that you don't know how to perform those three elementary steps I mentioned earlier? Have you performed For Each loops before? Have you moved data into and out of a DataTable before?

In vb.net, I've only imported a Excel worksheet into a datatable, and merged datatables - using code I found online. This merging has left me with the structure I displayed in my first post. I've used For loops in VBA, but it's the syntax of moving cell values from one place to another that I need to learn. I'm at lunch now, so I'm able to practise.

 

[bd528]

Ok, this seems to work

For i As Integer = 0 To dt2.Rows.Count - 1 
dt2.Rows(i).Item(0) = dt2.Rows(i).Item(1)
Next

Is there a way to move 2 columns of data across at the same time?

 

[jmcilhinney]

If you read my reply in post #2, your code is pretty much exactly what I described: loop through the rows, get the data from one field and assign it to another. That said, that code is not going to produce the result you showed in post #1. That will overwrite the values already in the first column with the blanks in the second. It also won't change anything in the second column, which I don't know whether you want or not.

As for moving data from two columns, you just move the second the same way you did the first. You obviously don't need more than one loop though.

 

[bd528]

If you read my reply in post #2, your code is pretty much exactly what I described: loop through the rows, get the data from one field and assign it to another. That said, that code is not going to produce the result you showed in post #1. That will overwrite the values already in the first column with the blanks in the second. It also won't change anything in the second column, which I don't know whether you want or not.

As for moving data from two columns, you just move the second the same way you did the first. You obviously don't need more than one loop though.

Yes, it is your method in post #2 - it's the syntax that I previously didn't know. My example above was just to show I had grasped the principle.

If I wanted to move 10 columns for example, I assume I would need a nested loop to cycle through each item?

 

[Herman]

You could also use a Linq query to get the result you want:

        Dim query = From row As DataRow In dt.Rows
                    Select field1 = If((row("col1").ToString = ""), row("col2").ToString, row("col1").ToString),
                           field2 = If((row("col3").ToString = ""), row("col4").ToString, row("col3").ToString)

Just add more Select arguments to process multiple column pairs at once.

 

[bd528]

Thanks for your reply.

I am now pulling the data from Excel into my app in a different format :-

Table 1
AAA
AAA
AAA

Table 2
BBB
BBB
BBB

Looping by row, and then by column, I am able to compile my information into Table 3 giving me :-

Table 3
AAA
AAA
AAA
BBB
BBB
BBB

...which is what I want. Is it possible to complete this task using Linq, and if so, how can it be done.

Also, if it can be done, is there any performance dis/advantage?

Many thanks,