Remove ' from a column in csv

[Abhi15 (Abhi)]

Hi Community,

Can any one help me how to remove the ’ from a column using uipath. The Call ID column has an ID which is 19 digits long. If removing the ’ from start it is showing up in scientific notation. I have attached the sample file here.
(Sample file is in csv format but could not upload here as csv format is not supported).
Test1.xlsx (10.5 KB)

[rohith.prabhu]

Hello @Abhi15 ,

To remove the ’ from the Call ID column :
Once you read the data and assigned it to a Data Table variable say DT1, use for loop to loop through its items. Use an assign activity iniside and
row(“Call ID) = row(“Call ID).ToString.Replace(”'”,“”)

This will replace all the single quotes from call ID column.

Hope it helps!

Regards,
Rohith

[Gokul001 (Gokul Balaji)]

Hi @Abhi15

You can also try with Split expression also

CurrentRow("Call ID") = Split(CurrentRow("Call ID").ToString,"'")(1)

Here is the workflow

FourmTest.xaml (10.5 KB)

Regards
Gokul

[Gokul_Jayakumar (Gokul Jai)]

Hello @Abhi15
Kindly refer this flow, it may helps you
SampleCSVprocess.zip (77.2 KB)
in folder, “Test1” is inpur file, “Output” is ’ removed output file

Regards
Gokul Jai

[Abhi15 (Abhi)]

Thanks for the solution , but in the output file the column is showing up in the scientific notation (7.13366E+18 = 7133660926383810000) and the value is getting changed completely. (original ID=7133660926383817253)

[Abhi15 (Abhi)]

Thanks for the solution , but in the output file the column is showing up in the scientific notation (7.13366E+18 = 7133660926383810000) and the value is getting changed completely. (original ID=7133660926383817253)
[/quote]

[Gokul001 (Gokul Balaji)]

Hi @Abhi15

you can try to Change cell format using BalaReva package

Refer this thread and video link

UiPathGo - BalaReva Excel Activities

Hi , In the release Version 2019.2.0. I have added a new activity called Change Cell Type. It changes the format of the value like below. [image] In the Cell Format , can specify the custom format what we have in the excel Like below image. [image] Sample : UiPathGoExcel.zip (18.4 KB) This is the video to configure the package in local. Here is the link to know about the formats. Regards Balamurugan.S

UiPath | How to change the cell or column format in Excel | Guide (datatype)

Regards
Gokul

[Gokul_Jayakumar (Gokul Jai)]

@Abhi15
Try this , use double instead of string
Assign to CDbl

CurrentRow("Call ID")=CDbl(CurrentRow("Call ID").ToString.Replace("'","").Trim)

[Abhi15 (Abhi)]

could you elaborate , didn’t get it where to use double ?

[Gokul_Jayakumar (Gokul Jai)]

@Abhi15 After remove the ’ single quote use CDbl to convert string to double, it may helps you.

[Gokul001 (Gokul Balaji)]

Hi @Abhi15

Have you tried with BalaReva package

Regards
Gokul

[Abhi15 (Abhi)]

Hi @Gokul001

I have tried with bala reva activity and also by using different format codes , but not getting the required output.

[Gokul001 (Gokul Balaji)]

Hi @Abhi15

Can you share the XAML file

Regards
Gokul

[Gokul001 (Gokul Balaji)]

File is not uploaded properly @Abhi15

[Shubham_Dutta]

Hi @Abhi15, I hope you are doing well.
For your current problem just you have to format the column data.

For that select the entire column right click, then click on format cells, then select numbers and format it. You can even use a macros for it.

Thanks & Regards,
Shubham Dutta