How to add array of string to the specific DataTable column using LINQ or Query?

Hi Everybody,
I want to add array of string to specific column in DataTable.
Eg.
arrEmpDepartment={HR,IT,Account,RPA,DS,QA}
arrEmpName={A,B,C,D,E,F}
arrEmpID={1,2,3,4,5,6}
Result should be,
All values should be placed in EmpName, EmpDepartment and EmpID datatable column accordingly.

Can anyone help me please?

Thank you in advance.

1 Like

@Guru_Koli21,

This code will give you a string out of your array.
String.Join(",", strVariable)

You can pass this to your desired row and cell as per your logic.

Thanks,
Ashok :slight_smile:

@Guru_Koli21

image

Use 
EmpDepartment=String.Join(",",arrEmpDepartment)
EmpName=String.Join(",",arrEmpName)
EmpID=String.Join(",",arrEmpID)

Lets assume all is to create newly:

Build Datatable Activity and configure the DataTable with its columns
out: dtData

Assign Activity:
dtData =

(From i In Enumerable.Range(0,arrEmpID.length)
Let ds  = New List(Of IEnumerable(Of Object)) From  {arrEmpName.Cast(Of Object),arrEmpDepartment.Cast(Of Object),arrEmpID.Cast(Of Object)}
Let ra = ds.Select(Function (x) x(i)).Cast(Of Object).toArray
Select r = dtData.Rows.Add(ra)).CopyToDataTable

we can adapt, and also decompose to essential activitiy modellings

UPD1 - adaptions and visuals:


we handle string and int32 arrays in parallel

grafik

@rlgandu @ashokkarale Thank you for your reply,

Already I have array Input and the count is min 2000 in one array. Now I want add this array to particular datatable column.
I have below solution but can’t able to add array values to specific column,

arrItemNumber.Select(Function (x) dtOutputData.Rows.Add(new Object(){x})).CopyToDataTable

Regards,
Guru

just share with us the expected output sample as your request can be interpret into multiple directions

Hi @ppr,
Input array’s
arrEmpDepartment={HR,IT,Account,RPA,DS,QA}
arrEmpName={A,B,C,D,E,F}
arrEmpID={1,2,3,4,5,6}

Result should be as DataTable,
image

perfect so above

is adressing the output.

1 Like

Hi @Guru_Koli21

You want to convert the data in the array variables to the datatable, Am I right.

If yes, then you can achieve it by using the Vb code in invoke code activity, follow the below steps,
→ Use the Build datatable activity to build the datatable and add the column in it and create a variable called dt in the Datatable options in properties.
→ Use the Multi assign activity to initialize the items in Array variables,

- Assign -> arrEmpDepartment = {"HR","IT","Account","RPA","DS","QA"}
- Assign -> arrEmpName = {"A","B","C","D","E","F"}
- Assign -> arrEmpID = {"1","2","3","4","5","6"}

→ Then use the Invoke code activity and click on Edit code then give the below vb code in it.

' Populate the DataTable using LINQ
dt = arrEmpName.
    Select(Function(name, index) New With {
        .EmpName = name,
        .EmpDepartment = arrEmpDepartment(index),
        .EmpID = arrEmpID(index)
    }).
    Aggregate(dt, Function(dtable, emp) 
        Dim row = dtable.NewRow()
        row("EmpName") = emp.EmpName
        row("EmpDepartment") = emp.EmpDepartment
        row("EmpID") = emp.EmpID
        dtable.Rows.Add(row)
        Return dtable
    End Function)

→ Click on Edit arguments and pass the arguments to the code as below,


→ Then use the Write range workbook activity to write the dt to the excel.

Check the below image for better understanding,
Sequence5.xaml (16.2 KB)

Check the below output excel file,
image

Hope it helps!!

1 Like

@Guru_Koli21


dt = dtEmployees
Dim empDepartments() As String = arrEmpDepartment
Dim empNames() As String = arrEmpName
Dim empIDs() As Integer = arrEmpID

For i As Integer = 0 To empNames.Length - 1
    dt.Rows.Add(empNames(i), empDepartments(i), empIDs(i))
Next

Output:
image

1 Like

Hi @rlgandu & @mkankatala ,

I tried both codes but getting an same error,
image

And Studio is not allowing me to delete that namespace. How to overcome?

Thanks,
Guru

Have you tried the same code in the above post… @Guru_Koli21

' Populate the DataTable using LINQ
dt = arrEmpName.
    Select(Function(name, index) New With {
        .EmpName = name,
        .EmpDepartment = arrEmpDepartment(index),
        .EmpID = arrEmpID(index)
    }).
    Aggregate(dt, Function(dtable, emp) 
        Dim row = dtable.NewRow()
        row("EmpName") = emp.EmpName
        row("EmpDepartment") = emp.EmpDepartment
        row("EmpID") = emp.EmpID
        dtable.Rows.Add(row)
        Return dtable
    End Function)

Try creating new project and use the same code and check weather you facing same error or not.

Hope you understand!!

@mkankatala, I tried the same code.

Okay Thanks, I will create new project and try again.

And what if the length of one of array is mismatched with the other? is it work in this scenario? @mkankatala

Okay @Guru_Koli21

Try to delete and again import the System.Collections.ObjectModel Namespace from the Imports panel if not works, use the LINQ Expression in the assign activity.

Delete the Invoke code activity and take an assign activity and give the below expression,

- Assign -> dt = (From index In Enumerable.Range(0, arrEmpID.length-1)
                  Let EmpName = arrEmpName(index).ToString
                  Let EmpDepartment = arrEmpDepartment(index).ToString
                  Let EmpID = arrEmpID(index).ToString
                  Select dt.Rows.add({EmpName,EmpDepartment,EmpID})
                          ).CopyToDataTable

Check the below workflow,

Hopeit helps!!

1 Like

@Guru_Koli21

Please refer this threads once

1 Like

Thank you so much for the multiple solutions. It works for me @mkankatala :+1:

1 Like

It’s my pleasure… @Guru_Koli21

Happy Automation!!

1 Like

can you check also for the last array item entry when using

Enumerable.Range(0, arrEmpID.length-1)

Sorry @ppr

It not works I have to specify Enumerable.Range(0, arrEmpID.length)

@Guru_Koli21 Do this change in the LINQ Expression.

Thanks @ppr & @mkankatala for correction.