Adding Variable As A Column To Datatable

[GenAi]

How do I add a variable as a column to each row of a datatable? Example

Datatable contains Names and Ages, in this case all the members of the group are males so I want value of the variable "sex". which is Male to appear on each row of Datatable.

Thank you

Before

After, should look like this

[VishnuReddy1997 · Accepted solution]

Hi @GenAi ,

Please find the below.If you want you can set that Sex into variable and call that in Update data table item.

Code:

Variables.CreateNewDatatable InputTable: { ^['Name', 'Age', 'Gender'], [$'''Sam''', 10, $''''''], [$'''Peter''', 20, $''''''], [$'''Mike''', 30, $''''''], [$'''Ray''', 40, $''''''] } DataTable=> DataTable
SET RowValue TO 0
LOOP FOREACH CurrentItem IN DataTable
    Variables.ModifyDataTableItem DataTable: DataTable ColumnNameOrIndex: $'''Gender''' RowIndex: RowValue Value: $'''Male'''
    Variables.IncreaseVariable Value: RowValue IncrementValue: 1
END

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[GenAi]

I see your logic and love it but realized that I asked the wrong question.  My datatable was extracted from an html table and doesn't contain a column header "Account".  I have updated the flow based on your logic but not sure how I can add the column header " Account" to the table. Please see below my exact flow.  The single datatable "Header_048" is what I want to appear in the column " Account" for each row. How do I get this done? Thank you

[Deenuji · Accepted solution]

@GenAi 

Lets make it simple. I go with your iterated example:

Then I pass this datatable to .Net script where it will do all the magics.

After executing .Net script:

Overall flow screenshot:

To integrate this logic into your workflow:

1. Drag and drop the .Net script into your workspace.

2. Click the Edit button on the script parameter and create a .Net parameter as shown in the screenshot below.

3. Insert the C# code as displayed below and modify it if you require a different default value (for instance, "Male" is set as the default here).

The function will accept your datatable as input, insert an "Account" column with the default value "Male," and then return the updated datatable.

Full workflow code (To understand how it works, simply copy and paste the full workflow code into your PAD flow):

Variables.CreateNewDatatable InputTable: { ^['Name', 'Age'], [$'''Sam''', 10], [$'''Peter''', 22], [$'''Mike''', 30], [$'''Ray''', 40] } DataTable=> Header_048
Scripting.RunDotNetScript Imports: $'''System.Data''' Language: System.DotNetActionLanguageType.CSharp Script: $''' // Check if the Gender column already exists
        if (!dt.Columns.Contains(\"Gender\"))
        {
            // Add the Gender column with default value \"Male\"
            DataColumn genderColumn = dt.Columns.Add(\"Gender\", typeof(string));
            genderColumn.DefaultValue = \"Male\";

            // Set default value for existing rows (if any)
            foreach (DataRow row in dt.Rows)
            {
                row[\"Gender\"] = genderColumn.DefaultValue;
            }
        }''' @'name:dt': Header_048 @'type:dt': $'''Datatable''' @'direction:dt': $'''InOut''' @dt=> Header_048

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[GenAi · Accepted solution]

I tried your suggestion an got an error message (Please see below). Also, I was wondering with your solution, do I need to state all the variable inputs? There are lots of rows, almost impossible to state all of them.   What do you suggest? Sorry I am a beginner and making it harder to get help. Thank you

Variables.CreateNewDatatable InputTable: { ^['Name', 'Age'], [$'''Sam''', 10], [$'''Peter''', 22], [$'''Mike''', 30], [$'''Ray''', 40] }

[GenAi]

I took @VishnuReddy1997 procedure and used @Deenuji code to add the column. It worked beautifully. Thank you two so much. I don't know which one to accept as the solution since its a joint effort.

[Deenuji · Accepted solution]

@GenAi 

Please accept both suggestions as solution. Thank you and Happy Automation!!