To Restructure data in Excel File

[NISHITHA]

Hii Team

We have Sheet1 in Excel file (Have attached Reference)

1. Is it possible to convert the data format of Column A in Sheet 1 to numeric data format without any decimal points?

2. How can we rearrange the columns in Sheet 1 to match the order in the Final Template?
   Additionally, is there a way to insert two new columns, named “Active” and “Old”?

I attempted using invoke code, but encountered an exception due to differences in column no’s in Sheet 1

The data format Example that we should have as output have defined in FinalTemplate Sheet

Final Template.xlsx (6.1 KB)

Any suggestions on resolving this issue would be highly valuable.

[Palaniyappan (Palaniyappan P)]

@NISHITHA

Is it possible to convert the data format of Column A in Sheet 1 to numeric data format without any decimal points?

Yes it is possible
Check this out

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

@NISHITHA

How can we rearrange the columns in Sheet 1 to match the order in the Final Template?
Additionally, is there a way to insert two new columns, named “Active” and “Old”?

You can rearrange by reading that excel file as a datatable and using DefaultView method

Once after reading datatable named as dt use a assign activity like this

dt = dt.DefaultView.ToTable(False, “column-2”, “column-3”, “column-1”…,”column-n”)

U can rearrange as u need

Then you can ofcourse add columns with ADD DATACOLUMN activity

Once after doing all these u can write it to the excel file u want using WRITE RANGE ACTIVITY

Hope this helps

Cheers @NISHITHA

[Yoichi]

Hi,

Can you try the following sample?

Sample20231026-6.zip (12.3 KB)

Is it possible to convert the data format of Column A in Sheet 1 to numeric data format without any decimal points?

In the sample, all the data in ColumnA is integer. How do you need to convert the value: rounding, round off etc?

Regards,

[NISHITHA]

Thanks @Palaniyappan and @Yoichi for the detailed explaination will surely try

[NISHITHA]

Hi @Palaniyappan and @Yoichi

Just one quick question

Could you suggest mechanism in UiPath we could use to resolve this

We cannot use external package as suggested above video reference

Everything else is resolved

[Yoichi]

HI,

How about FormatCells activity?

Regards,

[NISHITHA]

Hii @Yoichi

Is it doable column wise?

Example convert to no only for column A , C, E

[Yoichi]

HI,

Yes, as the following, for example.

Regards,

[NISHITHA]

Thank you @Yoichi for the references provided