Excel sorting problem

excel

#1

I have this weird problem that when i sort an excel file about the price of an iphone x then it doesnt understand that 900 is lower than 1000.

Its like the program think that 900 and 1000 are 2 different types.

Ill attach screenshots


#2

Hi @Ibra (lol I like your name)

It seems to be a format problem. As I can see in the Screenshot, UiPath is reading like: 1’006, 1’150, 900’00,999’00

So you will have to format the values of the 1’000 cases. Replace the “,” with a “.” or something like this, making the same format for all the values.


#3

Thanks :smiley:

But the purpose is not to edit the excel file manually as it count as “cheating” for me personally… i need to change it through uipath studio. Any suggestion how to change the “,” to “.”?


#4

Load the data from excel into UiPath data table and replace the contents of a desired column. Then sort the data table based on the desired column and proceed.


#5

Lol Ibra, you can change “,” to “.” with Replace(",",".")

Im with my phone, but I believe I’ve wrote the correct syntax

Bests,

Pablo


#6

It’s sorting alphabetically because of the currency character.
How are you doing the Sort?
I suppose there are a few ways to sort so depends on that. I know if you were to use a Data Table, you would need to look at the value as a number when the sort is performed.


#7

Hi @Ibra

Share your .csv or .xlsx with that table and I will try to fix the problem, then i’ll atach the .xaml here

But, i believe you need something like this:


#8

amazoniPhoneX.xlsx (9.2 KB)

This is the xlsx file


#9

that file is so weird, I’ve delete all the dots, commas and £. And then, after that, I tried to manualy sort the data and its not working well.

So, the problem is in the excel file, the data you have got is weird, I dont know why, but excel thinks 99900 is greater than 134600… If I write manualy 99900, 10000, 134600 in 3 different cells, Excel is sorting right (with the same values i’ve got after replace de dots, commas…) .So you can guess that the values that you are getting in “Price” Column (dont know how) haven’t the correct format, values or just is weird data.

Regards,


#10

Hmm i see… well i dont disagree with that the data is weird because when i was extracting the data it took quite some time compared to the data i extracted from Ebay. But thanks alot for the help!


#11

@ibra your welcome,

Maybe you can share the url from where you are getting this data to get more help.

Regards


#12

https://www.amazon.co.uk/s/ref=sr_nr_p_36_2?rnid=389035011&keywords=iphone+x&rh=n%3A560798%2Cn%3A1340509031%2Cn%3A5362060031%2Ck%3Aiphone+x%2Cp_n_operating_system_browse-bin%3A1601697031%2Cp_36%3A80000-300000&qid=1534410528&low-price=900&high-price=3%2C000


#13

@ibra i’ll check this but now im busy at work… need to do some things…

i’ll answer you when i’ll be able to do something

Regards friend


#14

Ok thanks alot! I appreciate the time you using to help a noobie like me :slight_smile:


#15

x

This is my sorting variables. The first one inside the “To” =

amazonData.Default.Sort


#16

UPDATE: I managed to print out the LOWEST value to a messagebox but i dont know how to get that replace-method i used in the picture to make a Write Range so it ALSO can be saved to excel and not only a string