When copying excel datatable an inserting, it changes the format

[eltmo]

I am copying a datatable from one sheet to another. If the data contains comma, it inserts this ’ in front og the data.

It makes it look like this

I need the ’ not to appear.

[Sudharsan_Ka]

HI @eltmo

This may solve your problem try like this
if you are using Write range of excel activities use write range of workbook and vice versa

And also if possible remove the error in the cell which contains 2788,5 and try again too

Regards
Sudharsan

[ushu (Usha kiranmai)]

@eltmo Try the below one

Using LINQ to replace specific value in DataTable without For-Each

Hi, this is the right side (From d In dt_Combined.AsEnumerable() Let ra = d.ItemArray.Select(Function (x) x.ToString.Replace(“-”,“”).ToArray) Select rc = dt_Corrected.Rows.Add(ra)).CopytoDataTable

[Gokul001 (Gokul Balaji)]

Hi @eltmo

While Reading the data using Read Range activity

Properties panel

PreserveFormat → Check

How to preserve Format while copying from an excel file

I am simply reading from an Excel file and writing to a new excel file and it works. I have questions though. [dummy1] [dummy2] If you see, my source file(dummy1.xlsx - on the left hand side) and my target file(dummy2.xlsx) has different format, although content is exactly same. So why UiPath can’t copy with extact format also? When reading from the file I am using PreserveFormat…I thought it would preserve the format. Secondly the sizes of the two files are also different. Dummy1 is 18kb w…

Regards
Gokul

[eltmo]

I actually already had the PreserveFormat checked, because it changed up some dates. I just unchecked it, and now it dosnt add the ', but is does change the dates…

[Gokul001 (Gokul Balaji)]

Hi @eltmo

can you share the input file.

Try with expression to change the date format in the excel

Use For Each Row in DataTable

Use Assign Activity

LHS → CurrentRow(“Date”)
RHS →

DateTime.ParseExact(CurrentRow("Date").ToString,ArrString,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("dd.MM.yyyy")

ArrString

DataType → String of Array
Default → New String(){“dd.MM.yyyy”,“dd-MM-yyyy”}

In Default You can declarer any type of date format

Regards
Gokul