Read Selected Column From Excel and split using LINQ

Help Activities
,
1

Hi ,

I have a huge excel from which I need to select specific columns with Alias Name and also split a columns. Can this be done with Linq queries

COLUMN A COLUMN B COLUMN C
TEST TEST01 999999 Not Required

I need COLUMN A (Split by space) with Alias Name XXX and YYY and Column B with Alias Name GGG

XXX YYYY GGG
TEST TEST01 999999

Thanks in advance

2

Hi Nimesh,

You can achieve this by using LINQ.
Can you try this code block in Invoke Code activity:
(You must set the property to C#)

    result.Columns.Add("XXX", typeof(string));
    result.Columns.Add("YYYY", typeof(string));
    result.Columns.Add("GGG", typeof(string));

    // Convert DataTable using LINQ
    foreach (DataRow row in dataTable.AsEnumerable())
    {
        string[] splitColumnA = row.Field<string>("Column A").Split(' ');

        DataRow newRow = result.NewRow();
        newRow["XXX"] = splitColumnA.Length > 0 ? splitColumnA[0] : "";
        newRow["YYYY"] = splitColumnA.Length > 1 ? splitColumnA[1] : "";
        newRow["GGG"] = row.Field<string>("Column B");

        result.Rows.Add(newRow);
    }

    // Print result
    foreach (DataRow row in result.Rows)
    {
        Console.WriteLine($"{row["XXX"]}\t{row["YYYY"]}\t{row["GGG"]}");
    }

image
image883×603 25.6 KB

You can also find the workflow attached:

Sequence.zip (2.2 KB)

Kind Regards,
Kardelen

3

Thanks a lot. Will try the same and update you.

4

Hi @nimesh2382

=> Build Data Table
image
Output-> newDt

=> Read Range Workbook
image
Output-> dt

=> Use below syntax in Assign activity:

newDt = (From row In dt.AsEnumerable()
         Let splitValues = row.Field(Of String)("COLUMN A").Split(" "c)
         Select newDt.Rows.Add(splitValues(0), If(splitValues.Length > 1, splitValues(1), Nothing), row.Field(Of Double)("COLUMN B"))).CopyToDataTable()

=> Write Range Workbook newDt back to excel
image

image
image1239×794 46.5 KB

Sequence9.xaml (10.6 KB)

Hope it helps!!

5

Thanks a lot for your help…Yes this also works