Segregation function in CSV

Help Activities
, , , , ,
1

Hi

How can I segregate particular information from cell “K2” into multiple columns ?
example - “Marketplace;US”,“Product;Book”,“Marketplace;UK”,Product;Pen".

Write the above output as
Marketplace Product
US Book
UK Pen

Regards
Naman

2

Have a look at the generate DataTable activity
The string looks close to a csv format and we could also do some adaptions before parsing when it is needed

3
  1. read that pertucular cell and split by ,
  2. iterate that array which just you have split
    use split again by ; an extract the required information

if you alrady have excel and have headers then directly write information in a specific column by write range or write cell activity

if you dont have excel the read the input excel make schema in build data table
and add extracted information in Schema
at the use write range

4

Hi @Naman_Arora

Can you try the following?

image
image743×632 27.6 KB

image
image895×260 10.4 KB

Code:

Dim keyValuePairs = inputText.Split(","c).
    Select(Function(pair) pair.Split(";"c)).
    GroupBy(Function(pair) pair(0).Trim()).
    ToDictionary(Function(Group) Group.Key, Function(Group) Group.Select(Function(pair) pair(1).Trim()).ToList())

outputDataTable = New DataTable()
For Each key In keyValuePairs.Keys
    outputDataTable.Columns.Add(key, GetType(String))
Next

For rowIndex = 0 To keyValuePairs.Values.Select(Function(lst) lst.Count).Max() - 1
    Dim newRow = outputDataTable.Rows.Add()
    For Each key In keyValuePairs.Keys
        If keyValuePairs(key).Count > rowIndex Then
            newRow(key) = keyValuePairs(key)(rowIndex)
        End If
    Next
Next

Output:

image

Regards,

5

if additional help is needed please share with us all details/samples with variations and a screenshot from the data.

We are very detailed relying all specific like split chars, surrounding " …

But with the shared information we can help to setup a working suggestion/prototype

Thanks for support

6

@lrtetala

Hi

I’m getting this error -
image

Regards
Naman

7

Hi @Naman_Arora

→ Given Input:

inputText= "Marketplace;US,Product;Book,Marketplace;UK,Product;Pen"

→ Use the below code in Invoke Code:

Dim keyValuePairs = inputText.Split(","c).
    Select(Function(pair) pair.Split(";"c)).
    GroupBy(Function(pair) pair(0).Trim()).
    ToDictionary(Function(Group) Group.Key, Function(Group) Group.Select(Function(pair) pair(1).Trim()).ToList())

outputDataTable = New DataTable()
For Each key In keyValuePairs.Keys
    outputDataTable.Columns.Add(key, GetType(String))
Next

For rowIndex = 0 To keyValuePairs.Values.Select(Function(lst) lst.Count).Max() - 1
    Dim newRow = outputDataTable.Rows.Add()
    For Each key In keyValuePairs.Keys
        If keyValuePairs(key).Count > rowIndex Then
            newRow(key) = keyValuePairs(key)(rowIndex)
        End If
    Next
Next

Below are Invoked Arguments:

image
image1067×685 28 KB

→ Use Write CSV to write the data to excel.
Output:
image
Workflow:

image
image900×629 30.3 KB

xaml:
Sequence.xaml (8.3 KB)

Regards

8

Please find the below xaml for your reference

BlankProcess18.zip (154.5 KB)

Cheers!!

9

Check the datatype of your variable, it must be string convert it to System.Data.Datatable

10

@lrtetala

I trying with your code, But it’s showing the pop up ?
Am I making any mistake in the variable ?

image
image1501×319 30.6 KB

Regards
Naman

11

@Naman_Arora

Change DT Variable from String type to DataTable

Try to run the below code and change the modifications according to it

12

@lrtetala

Thanks, it worked but when I ran it.
Got this -
image

13

@Naman_Arora

In my environment it is working perfectly are you extracted the above zip file and run the bot

14

@lrtetala

Am I getting this error because I want to get dynamic values for Marketplace & Product ?

I have created a variable like Input = “Marketplace,Product”.

Whatever the value present for marketplace & product and I want to take out that particular value.

Is there any way to do it ?

Regards
Naman

15

@lrtetala

Hi

Is there any way I can fix the above issue ?

Regards
Naman