Help needed for working with csv file in powershell

Welcome Forums General PowerShell Q&A Help needed for working with csv file in powershell

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
3 months, 3 weeks ago.

  • Author
    Posts
  • #109969

    Participant
    Points: 1
    Rank: Member

    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?

  • #110002

    Participant
    Points: 269
    Helping Hand
    Rank: Contributor

    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.

    • #110005

      Participant
      Points: 1
      Rank: Member

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

  • #110008

    Participant
    Points: 269
    Helping Hand
    Rank: Contributor

    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.

    • #110011

      Participant
      Points: 1
      Rank: Member

      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.

The topic ‘Help needed for working with csv file in powershell’ is closed to new replies.