Excel file column separation

[nirmalya.sarkar]

In this image, under “location” both city and state exists. I have to separate them as “city” and “State” column.
How will I do it??

[pravin_calvin (pravin kumar)]

Hi @nirmalya.sarkar

Please Build datatable activity to add a column of city and state.

Read range the input excel and the split the row values using.

Split(row(“Location).ToString,”/")

Use add datatrow activity.

{Splitvariable(0).replace(“:”,“”),Splitvatiable(1)}

write range the Dt.

Regards

[sai_krishna_somisetty (sai krishna)]

@nirmalya.sarkar you can try this

Splitvariable = Split(row(“Location").ToString,”/")

City = Splitvariable(0).replace(“:”,“”).Trim
State = Split(Splitvatiable(1),“-”)(0).Trim

if want pincode

pincode = Split(Splitvatiable(1),“-”)(1).Trim

Happy automation

[nirmalya.sarkar]

Thank You @pravin_calvin. It’s done!!

But can you explain me this line “{Splitvariable(0).replace(”:“,”“),Splitvatiable(1)}”?? Specifically I want to know significance of (0) and (1).

[pravin_calvin (pravin kumar)]

Hi @nirmalya.sarkar

While splitting the string : Kolkata/West Bengal - 700001 based on slash

Its get separated as two values

Index(0) holding the value : Kolkata

Index(1) holding the value West Bengal - 700001

Regards

[nirmalya.sarkar]

@pravin_calvin , I have followed the steps which you said and it’s done. But, now I have to write these “city” and “State” column in a pre-existing excel file or write in a new page with whole data

What will be the steps??

[pravin_calvin (pravin kumar)]

Hi @nirmalya.sarkar

Please refer to the xaml below.

I have done with excel already having the column name city and state.

newSplitProcess.xaml (8.0 KB)

Regards

[nirmalya.sarkar]

Hi @pravin_calvin , after messaging you, I am trying it for sometime. And it worked.
Btw, thank you for your constant support .

If I have any problem regarding automation in future, can I message you ??

[pravin_calvin (pravin kumar)]

@nirmalya.sarkar

Yes!

Happy Automation!

Regards