Reading Excel and splitting values into three variables based on the first two characters?

Help Activities
, ,
1

Hi All,

I have an Excel file with a column “Vendor Code”.

If the values starts with 74, I want it in a variable called “non_trade”.

If the values starts with 75, I want it in a variable called “ced”.

If the values starts with 76, I want it in a variable called “bsd”.

How do I achieve this ?

image

2

Hi @uio

Use Else If activity

ElseIf Condition: CurrentRow("Vendor Code").ToString.StartsWith("74")
    Assign: non_trade = CurrentRow("Vendor Code").ToString

Else If Condition: CurrentRow("Vendor Code").ToString.StartsWith("75")
    Assign: ced = CurrentRow("Vendor Code").ToString

Else If Condition: CurrentRow("Vendor Code").ToString.StartsWith("76")
    Assign: bsd = CurrentRow("Vendor Code").ToString

image
image828×1528 70 KB

Regards,

3

HI,

It may be better to use Dictionary if there are multiple rows regarding same category for example in case there are 74123 and 74001 in the same sheet.

image
image737×793 27.2 KB

dict = dt.AsEnumerable.Select(Function(r) r("Vendor Code").ToString).GroupBy(Function(s) s.Substring(0,2)).ToDictionary(Function(g) g.Key, Function(g) g.toArray)

Sample
Sample20240705-3.zip (9.5 KB)

Regards,

1 Like
4

Hi @uio

Read Range Workbook
Output-> dt_VendorCodes

Use below linq expression syntaxes in Assign acitvity:

Assign Acitivty -> non_trade = (From row In dt_VendorCodes.AsEnumerable()
             Where row.Field(Of String)("Vendor Code").StartsWith("74")
             Select row.Field(Of String)("Vendor Code")).ToList()

Assign Activity -> ced = (From row In dt_VendorCodes.AsEnumerable()
       Where row.Field(Of String)("Vendor Code").StartsWith("75")
       Select row.Field(Of String)("Vendor Code")).ToList()

Assign Activity-> bsd = (From row In dt_VendorCodes.AsEnumerable()
       Where row.Field(Of String)("Vendor Code").StartsWith("76")
       Select row.Field(Of String)("Vendor Code")).ToList()

Datatypes:
non_trade (DataType: System.Collections.Generic.List(System.String))
ced (DataType: System.Collections.Generic.List(System.String))
bsd (DataType: System.Collections.Generic.List(System.String))

If you have multiple values in the datatable the above linq espressions will store that valuss in a list.

You can print a list by using the syntax:

String.Join(Environment.NewLine, non_trade)

In the same qay you can print ced and bsd also.

Hope it helps!!

5

Hi @uio

You can use the below LINQ Expressions to store the required data in a specified variables.
→ Use the Read range wokbook activity to read the excel and store in a datatable called Datatable.
→ Then use the assign activities and create variable and use the below LINQ Expressions,

-> Assign -> non_trade = Datatable.AsEnumerable.Select(Function(X) X("Vendor Code").toString.StartsWith("74")).ToString

- Assign -> ced = Datatable.AsEnumerable.Select(Function(X) X("Vendor Code").toString.StartsWith("75")).ToString

- Assign -> bsd = Datatable.AsEnumerable.Select(Function(X) X("Vendor Code").toString.StartsWith("76")).ToString

Hope it helps!!