Data formatting in Excel via Uipath

Help Activities
, , ,
1

Dear all,

I hope you are all doing well in this uncertain times.

A little problem occurred which I was not able to solve with any solution here on the Forum (at least those I found).

The problem is nothing new to me and the simplified flow goes this way:

data download (CSV)-> copy to Excel → data manipulation (because of Regional conditioned formats) - replace “,” with “,” and removal of “$” sign.

After doing these manipulation, and writing the datatable to Excel (Write range using Workbook, not Excel) the cells have, probably, to all known error: Number Stored as text.

Is there any way to automate this process? I am aware about the manual procedure.

I am attaching two files to make it easier understand about which formats I am talking about.

Thank you!
Before_manipulation|397x167
After_manipulation

3

Hi,

Can you try the following if you can use ExcelApplicationScopr or UseExcelFile activity?

data download (CSV) → Read CSV as datatable → data manipulation (because of Regional conditioned formats) - replace “,” with “,” and removal of “$” sign.
→Paste Excel using ExecelWriteRage (with ExcelAplpicationScope or Use Excel File)

Regards,

4

Hi Yoichi,

thank you for your answer, but unfortunately this is not working.

Still get the little green triangles in each cell where the replace was conducted.

5

Hi,

Which character do you try to replace, from comma to dot or from dot to comma?
If later, probably we don’t need it. So, can you try to remove this replace then use ExcelWriteRange?

Regards,

6

Now I see that I wrote the same sign:

  • in the original CSV file the decimal separator is “.”;
  • after I write the range into an Excel file, with the replace activitiy I am changing it to “,”.

This works all how it should, but I am trying to remove the triangels in Excel. I am sharing the Xaml file and the Excel which should be corrected. if I am missing something to explain.

Using_Workbook_Activity_share.xaml (27.7 KB)

newdata_PFE.xlsx (127.9 KB)

7

Hi,

Thank you for sharing your workflow.

System-File-Workbook-WriteRange activty write data as text. So if you need to write it as other type, please use AppIntegration - Excel - WriteRange with Excel AplpciationScope OR AppIntegration - Excel - Range - WriteRange with UseExcelFile.

Regards,

8

I understand now, thank you, with some additional altering this worked for me!

Thank you!

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.