Split String in Excel file

Help
1

Hi,
I need to split the strings in an excel file with “:” as my reference and anything that is left of the colon should be my column name and the corresponding value on the right side of the colon should be placed below that column name. Cells which are empty/ doesn’t have a colon must be deleted. I don’t know how to do this. Can anyone help me with this?

Thanks in advance :slight_smile:

PF the excel file attached.Book1.xlsx (8.3 KB)

2

Need one confirmation -

In this case,
you lose the countries list right. In the below image, as per your requirement countries list contains only one value Thailand and will miss the other two. Is it expected?

image

3

Yeah is it possible to add Malaysia and Singapore along with thailand under countries list column in a single cell seperated by commas??

Regards,
Lokesh R

4

But the problem is how to identify these values. Because it is not separated by “:”. As per the requirement it has to be deleted.

May be you must change the way how the countries list is defined in this excel.

Regards,
Karthik Byggari

5

Okay. Can we able to format the other cells??

Regards,
Lokesh R

6

Yes.

7

That’s good. Can you solve this one?

8

Will send you the xaml after a while.

1 Like
9

Please find the attached xaml and sample output file.Book1.xlsx (9.7 KB)
Main.xaml (14.2 KB)

1 Like
10

image

This activity isn’t displaying on my UiPath Studio. Im using 2016.2 version. Any alternatives for this??

11

The missing activity here is “For Each” Loop Activity.
I developed the bot with the latest UIPath version.

There are just couple of assign activities in the for loop.
Do you want me to write those assign activities here. So you can build the missing activity.

12

Yeah. Go ahead.

13 
FOR EACH [row] in [ExtractData]

--------BODY OF FOR LOOP-----------------

      IF row(0).ToString.Contains(":")

          ---------If condition is TRUE---------

                ASSIGN: iIndex = row(0).ToString.IndexOf(":")
                ASSIGN: colName = row(0).ToString.Substring(0, iIndex-1).Trim
                ASSIGN: tempValue = tempValue + "," + row(0).ToString.Substring(iIndex+1,row(0).ToString.Length - iIndex -1).Trim

                ADD DATA COLUMN ACTIVITY: 
                  In the PROPERTIES -
                    ColumnName: colName
                    DataTable: outDT
                    AllowDBNull: True

          ---------End of IF Activity-----------

---------END OF FOR EACH Activity--------
2 Likes
14

image

image

Hi Man, Im getting an exception for this assign activity, what’s wrong with it?

15

tempvalue is not assigned before assigning. So you are getting this error.
Add a if condition for this assign activity. If tempvalue is null, do not assign anything.