Author Posts

August 25, 2018 at 6:38 pm

I have a csv file with 1600 lines from which top ten lines given below
N,EQ,ADANIPORTS,ADANI PORT & SEZ LTD,384.5,385,387.8,375,376.75,792818726.1,2085488,Y, ,40850,452.35,350.45
N,EQ,ASIANPAINT,ASIAN PAINTS LIMITED,1394.75,1395,1411,1385.05,1393.5,1284559258,919355,Y, ,36117,1490.6,1090.1
N,EQ,AXISBANK,AXIS BANK LIMITED,631.75,638.05,643.4,634,639.9,9599936309,15035968,Y, ,144038,644.65,447.5
N,EQ,BAJAJ-AUTO,BAJAJ AUTO LIMITED,2685.55,2683.9,2697,2664,2682.25,1476618943,551229,Y, ,23611,3468.35,2605
N,EQ,BAJAJFINSV,BAJAJ FINSERV LTD.,7092.1,7092,7129,7025.25,7050.65,909166393.3,128111,Y, ,19707,7200,4500
N,EQ,BAJFINANCE,BAJAJ FINANCE LIMITED,2893.85,2892,2943.4,2891.05,2916.6,3884349778,1327710,Y, ,52356,2943.4,1511.2
N,EQ,BHARTIARTL,BHARTI AIRTEL LIMITED,369.9,370,370.8,365,368.95,768282183.8,2089422,Y, ,26515,564.8,331
N,EQ,BPCL,BHARAT PETROLEUM CORP LT,357.75,358.25,362,353.5,356.95,1738725370,4865929,Y, ,77863,551.55,353.5
N,EQ,CIPLA,CIPLA LTD,657.95,658,658,645,651.2,1235846442,1904031,Y, ,38575,665,507.2
N,EQ,COALINDIA,COAL INDIA LTD,289.05,287.85,293.6,287.8,291,791484837,2713583,Y, ,55421,316.95,235.85

I wanted to sort 10 the column in descending order so that top 20 I can find out.
The file name is Pd240818.csv
my powershell code is as below.
# To remove unwanted few lines
sls ",BE,",",EQ," .\Pd240818.csv | select -exp line | Where-Object {$_ -notmatch ',EQ, ,'} > .\temp.csv
#Sorting line is as follows
gc .\temp.csv | Where-Object {$_ -notmatch 'MKT,'}|%{$_.split(",")[9]}|Sort-Object -Descending| Select-Object -first 20 > temp.txt
Sorted

I get temp.txt as follows
99988.7
99896.5
9989273.6
99769.75
996134.55
9933960.45
99228.65
99199.95
989418.15
988423057.7
9884111.1
98572145.2
982146.5
981497584.9
97982.75
9786178.9
9775915.05
9760482.5
97384498.85
971033.85

Where as if I sort the same column in excel, I get as below.

28818819313

9599936309

8459873415

6175554483

5889553012

5690666055

5439638100

5121938441

5079530750

5042021707

4972762046

4889394601

4742835986

3884349778

3690976213

3486309023

3388956937

3336437125

3206801588

3114870807

Where am I doing wrong. How to correct it?

August 25, 2018 at 9:42 pm

Is there a special reason why you're not using the cmdlet made to work with csv files, Import-CSV?
You simply import the data, filter out whatever you need to filter out and sort it the way you like to sort it.

August 25, 2018 at 10:10 pm

I don't have any specific reason. I used gc. I will be thankful for any solution.

August 25, 2018 at 10:51 pm

hmmm ... it works for me like this:

Import-Csv -Path C:\sample\Pd240818.csv -Delimiter ',' -Header 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 |
    Sort-Object -Property '10' |
        Export-Csv -Path C:\sample\Pd240818-sorted.csv -Delimiter ',' -NoTypeInformation

I chose numbers as headers for your csv data. Of course you could use more meaningful names if needed. 😉

BTW: You could do us (and yourself) a favor when you post code here you should format it as code, please. That's even good for posting file contents or errors you get.
Thanks in advance.

August 26, 2018 at 1:54 am

Thank you very much.

I got help from other sources also. By adding -Property { $_ -as [decimal] } to |Sort-Object -Descending as |Sort-Object -Descending -Property { $_ -as [decimal]  in my code, problem was solved. This is my first post here. Hence I could not follow all facilities. Kindly excuse me.