Hi I have the excel and my required output is the below screenshot. I want Course1 and Course2 in one column and course3 and course4 in one column

Help Studio
, ,
1

Screenshot 2025-02-19 222651
Screenshot 2025-02-19 2226511180×577 71.8 KB

Hi I have the excel and my required output is the below screenshot. I want Course1 and Course2 in one column and course3 and course4 in one column

2

Hi @nikki

Here is a Solution that uses 2 Add Data Row Activities:

Input:

This image shows a data table with information about three colleges, including their names, course codes, and up to four associated courses for each college. (Captioned by AI)
This image shows a data table with information about three colleges, including their names, course codes, and up to four associated courses for each college. (Captioned by AI)807×341 11.9 KB

Build Output DT:

This image shows a "Build Data Table" window with columns: collegeName, CourseCode, Course5, and Course6, all of type String, and options to add or edit entries. (Captioned by AI)
This image shows a "Build Data Table" window with columns: collegeName, CourseCode, Course5, and Course6, all of type String, and options to add or edit entries. (Captioned by AI)547×344 6.45 KB

The Flow:

A flowchart shows a "For Each Row in Data Table" loop that adds rows to a different data table based on values from the current row in the original data table. (Captioned by AI)
A flowchart shows a "For Each Row in Data Table" loop that adds rows to a different data table based on values from the current row in the original data table. (Captioned by AI)426×608 20.5 KB

The Code in ArrayRow of each Add Data Row activity respectively:

New Object() {CurrentRow("collegeName").ToString, CurrentRow("CourseCode").ToString, CurrentRow("Course1").ToString, CurrentRow("Course3").ToString}

New Object() {"", "", CurrentRow("Course2").ToString, CurrentRow("Course4").ToString}

The Output:

image
image360×192 3.73 KB

I hope this helps, If this solves your issue do mark it as a solution.
Happy Automation :star_struck:

3

Can I get the solution as SQL query

4

Hi @nikki

Here is linq solution

Build a dt_final datatable with the required output columns

image
image492×141 5.08 KB

dt_final = dt_input.AsEnumerable().SelectMany(Function(x) New DataRow() {
    dt_final.Rows.Add(x("CollegeName").ToString(), x("CourseCode").ToString(), x("Course1").ToString(), x("Course3").ToString()),
    dt_final.Rows.Add("", "", x("Course2").ToString(), x("Course4").ToString())
}).CopyToDataTable()

Sample input and Output

image

image
image308×102 2.05 KB

Hope this helps

5

Could you try this:
(It may not work if collegeName or CourseCode is primary key and set to NotNull)

INSERT INTO TableName (collegeName, CourseCode, Course5, Course6)
VALUES 
(CurrentRow("collegeName").ToString, CurrentRow("CourseCode").ToString, CurrentRow("Course1").ToString, CurrentRow("Course3").ToString),
('', '', CurrentRow("Course2").ToString, CurrentRow("Course4").ToString)
6

hii,use the below linq code in assign activity
Dt_Ouput=(From row In dtInput.AsEnumerable()
Select New Object() {
row(“collegeName”).ToString(),
row(“CourseCode”).ToString(),
String.Join(Environment.NewLine, row(“Course1”).ToString(), row(“Course2”).ToString()).Trim(),
String.Join(Environment.NewLine, row(“Course3”).ToString(), row(“Course4”).ToString()).Trim()
}).CopyToDataTable()

hope this will work!!.