Hi Have excel file and i have to change the format of one the column which contains general as well as number cell type i want to make the column as general so that values having thousand seperator should be a single value.
eg. in my excel there are values as 230 ,1.23, but after reading it should be 230,1230 like that
Any idea how i can achieve it . i tried using format cell but it is not working.
@vishal_nachankar
Normally the number value will not have thousand separator when it’s format is general in Excel.
To achieve it, you may set the format as below.
no no i want to format that whole column to text or general means if the columns contains value as 1.23 numeric cell type then it should be 1230 in actually . please find the below screenshot
Apart from the thousand separator (it’s related to Excel or System setting), I tested the ‘Format Cells’ works fine.
Original data (data type is Custom):
Then use the Format Cells:
Result:
If your goal is to retrieve the data table with proper value only, you may just set the ‘Read formatting’ to ‘Raw value’.
what is your system or excel setting can you share the ss
can i share my excel file you with?
You could find the related setting as: Excel Options → Advanced → Editing options → Use system separators.
do i have to disable it or enable it.
Above separators setting only affects the decimal or thousands separator.
When I open the excel at my side, the value is showing 1.26. What’s the version of your Excel?
(Version 2308 Build 16.0.16731.20542) 64-bit
-
Could you show the cell type of field C11?
-
Could you try setting the ‘Read formatting’ to ‘Raw value’ and read range. Then check the value in data table is valid or not?
C10,C11 both are number
tried the second option as well below is the screenshot of the datatable
Maybe some information such as the format type is corrupted when the old .xls is opened by new version of Excel.
If possible, use .xlsx instead. I tested UiPath could read the value correctly by reading ‘Raw value’.
the report is generated is from SAP and it is having .xls format only
When I downloaded your excel file in my home environment, the value is showing 1.260 instead of 1260. Maybe you have to try to invoke VB code to read the data table.
can you share the code if possible please i not good in invoke code please
The root cause of your problem is that your “excel” file is in fact a text file…
I guess it is an export from SAP, right?
When you open the file in Excel the application will transform it into native Excel format and interpret contained data using your system locale.
In my case it interprets “.” as decimal point and value “1.260” (one thousand two hundreds sixty) as 1.26 (one point twenty six)
yes it is an export from SAP
Anil_G
ashokkarale
kouhei_yamaguchi
ppr
Nabin_Timalsina
sharazkm32
banksmegacorp
alexis.mendoza.rpa
m.takamiya
Honda