sort csv file | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

sort csv file

Shell Programming and Scripting


Tags
linux, linux commands

Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 02-09-2008
mozart mozart is offline
Registered User
 
Join Date: Feb 2008
Last Activity: 9 February 2008, 3:44 AM EST
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
sort csv file

hi guys.
i am quite new to shell scripting. and i've been practicing coding
in a CSV (comma separated value) file is it possible to sort a specific block?

what do i do if i want to sort these contacts by their birthdays in ascending order (Jan. to Dec.)? And i want only to display their name and birthday?

Here is the file

Sharapova,Maria,19-apr-1987,Brandenton FLorida,USA,admin@mariasharapova.com,+1(954)3387488
Bryant,Kobe,23-aug-1978,L.A. California,USA,kobe@lakers.comk+1(310)87890
Spielberg,Steven,18-dec-1946,L.A.California,USA,steven@dreamworks.com,+1(389)89098
Harnois,Elisabeth,26-may-1979,Detroit Michigan,USA,liz@1treehill.net,+1(313)74564536
Ryan,Michelle,22-apr-1984,Enfield London,UK,mryan@nbc.com,+44(207)2988410
Belle,Camilla,2-oct-19886,Miami Florida,USA,cam_65@hollywood.org, +1(954)3790491
Gellar,Sarah Michelle,14-apr-1977,L.A. California,USA,smg@buffy.com,+1(213)3398848m
Underwood,Carrie,10-mar-1983,Checotah Oklahoma,USA,carrieunderwood@rca.com,+1(918)655423

many thanks!!!
Sponsored Links
    #2  
Old 02-09-2008
KevinADC KevinADC is offline Forum Advisor  
Registered User
 
Join Date: Jan 2008
Last Activity: 11 September 2009, 3:44 PM EDT
Posts: 729
Thanks: 0
Thanked 0 Times in 0 Posts
Is perl acceptable?
Sponsored Links
    #3  
Old 02-09-2008
KevinADC KevinADC is offline Forum Advisor  
Registered User
 
Join Date: Jan 2008
Last Activity: 11 September 2009, 3:44 PM EDT
Posts: 729
Thanks: 0
Thanked 0 Times in 0 Posts
Well, hopefully I did this for more than my own amusement:


Code:
#!/usr/bin/perl
use warnings;
use strict;
my @months_numeric = qw(01 02 03 04 05 06 07 08 09 10 11 12);
my @months_alphas  = qw(jan feb mar apr may jun jul aug sep oct nov dec);
my %months;
@months{@months_alphas} = @months_numeric;
open (my $in , '<', 'path/to/celebs.csv') or die "$!";
my @sorted = map {$_->[0]}
             sort{$a->[1] cmp $b->[1]}
             map {chomp;
                  my $bd = (split(","))[2];
                  my ($d,$m,$y) = split("-",$bd);
                  $d = $d<10 ? "0$d" : $d;
                  [$_,"$y$months{$m}$d"]} <$in>;
close $in;
print "$_\n" for @sorted;

Assumes the months are all represented by three characters and are always all lower-case letters.
    #4  
Old 02-10-2008
drl's Avatar
drl drl is offline Forum Advisor  
Registered Voter
 
Join Date: Apr 2007
Last Activity: 29 September 2014, 11:58 AM EDT
Location: Saint Paul, MN USA / BSD, CentOS, Debian, OS X, Solaris
Posts: 1,678
Thanks: 40
Thanked 194 Times in 177 Posts
Hi, mozart.

Welcome to the forum. In the future please surround data with code tags so that we can read them easily, and do the same for scripts.

I added a few extra lines to exercise the sorting of fields. This script uses commands often found on *nix. The standard sort has an option for named months:

Code:
#!/usr/bin/env sh

# @(#) s1       Demonstrate sorting fields by transforming.

#  ____
# /
# |   Infrastructure BEGIN

echo
set -o nounset

debug=":"
debug="echo"

## The shebang using "env" line is designed for portability. For
#  higher security, use:
#
#  #!/bin/sh -

## Use local command version for the commands in this demonstration.

set +o nounset
echo "(Versions displayed with local utility \"version\")"
version >/dev/null 2>&1 && version =o $(_eat $0 $1) tr cut sort
set -o nounset

echo

FILE=${1-data1}
echo " Input file $FILE:"
cat $FILE

# |   Infrastructure END
# \
#  ---

echo
echo " Results from processing:"
tr ' ' '_' <$FILE |
tr ',-' ' ' |
cut -d" " -f1-5 |
sort --key=5,5 --key=4M,4 --key=3,3

exit 0

Producing:

Code:
% ./s1 data2

(Versions displayed with local utility "version")
Linux 2.6.11-x1
GNU bash, version 2.05b.0(1)-release (i386-pc-linux-gnu)
tr (coreutils) 5.2.1
cut (coreutils) 5.2.1
sort (coreutils) 5.2.1

 Input file data2:
Bryant,Kobe4,23-sep-1979,L.A. California,USA,kobe@lakers.comk+1(310)87890
Sharapova,Maria,19-apr-1987,Brandenton FLorida,USA,admin@mariasharapova.com,+1(954)3387488
Bryant,Kobe1,23-aug-1978,L.A. California,USA,kobe@lakers.comk+1(310)87890
Spielberg,Steven,18-dec-1946,L.A.California,USA,steven@dreamworks.com,+1(389)89098
Harnois,Elisabeth,26-may-1979,Detroit Michigan,USA,liz@1treehill.net,+1(313)74564536
Ryan,Michelle,22-apr-1984,Enfield London,UK,mryan@nbc.com,+44(207)2988410
Belle,Camilla,2-oct-19886,Miami Florida,USA,cam_65@hollywood.org, +1(954)3790491
Gellar,Sarah Michelle,14-apr-1977,L.A. California,USA,smg@buffy.com,+1(213)3398848m
Underwood,Carrie,10-mar-1983,Checotah Oklahoma,USA,carrieunderwood@rca.com,+1(918)655423
Bryant,Kobe3,23-sep-1978,L.A. California,USA,kobe@lakers.comk+1(310)87890
Bryant,Kobe2,24-aug-1978,L.A. California,USA,kobe@lakers.comk+1(310)87890

 Results from processing:
Spielberg Steven 18 dec 1946
Gellar Sarah_Michelle 14 apr 1977
Bryant Kobe1 23 aug 1978
Bryant Kobe2 24 aug 1978
Bryant Kobe3 23 sep 1978
Harnois Elisabeth 26 may 1979
Bryant Kobe4 23 sep 1979
Underwood Carrie 10 mar 1983
Ryan Michelle 22 apr 1984
Sharapova Maria 19 apr 1987
Belle Camilla 2 oct 19886

I like your music, I hope you like my opus s1 . See man pages for details ... cheers, drl
Sponsored Links
    #5  
Old 02-10-2008
sitney sitney is offline
Registered User
 
Join Date: Feb 2008
Last Activity: 3 December 2010, 3:10 AM EST
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Quote:
what do i do if i want to sort these contacts by their birthdays in ascending order (Jan. to Dec.)? And i want only to display their name and birthday?
Your requirements don't specify whether you want to sort by year first, but only by month. So this one liner should meet that requirement:


Code:
sort -t"," -k 3 -t"-" -k 2 -M birthdays.txt |awk -F"," '{print $1, $2, $3}'

Sponsored Links
    #6  
Old 02-10-2008
alamitab alamitab is offline
Registered User
 
Join Date: Jan 2007
Last Activity: 11 February 2010, 10:59 AM EST
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Sort by month

cat file | awk -F"," '{print $1, $2, $3}' | sort -M -t"-" -k 2
Sponsored Links
    #7  
Old 02-11-2008
summer_cherry summer_cherry is offline Forum Advisor  
Registered User
 
Join Date: Jun 2007
Last Activity: 23 July 2014, 3:17 AM EDT
Location: Beijing China
Posts: 1,294
Thanks: 0
Thanked 24 Times in 24 Posts
sed+sort

Hi,

This one should be ok for you.


Code:
sed '
s/\([0-9]*\)-jan-\([0-9]*\)/\201\1/;
s/\([0-9]*\)-feb-\([0-9]*\)/\202\1/;
s/\([0-9]*\)-mar-\([0-9]*\)/\203\1/;
s/\([0-9]*\)-apr-\([0-9]*\)/\204\1/;
s/\([0-9]*\)-may-\([0-9]*\)/\205\1/;
s/\([0-9]*\)-jun-\([0-9]*\)/\206\1/;
s/\([0-9]*\)-jul-\([0-9]*\)/\207\1/;
s/\([0-9]*\)-aug-\([0-9]*\)/\208\1/;
s/\([0-9]*\)-sep-\([0-9]*\)/\209\1/;
s/\([0-9]*\)-oct-\([0-9]*\)/\210\1/;
s/\([0-9]*\)-nov-\([0-9]*\)/\211\1/;
s/\([0-9]*\)-dec-\([0-9]*\)/\212\1/
' filename > filename.temp
sort -t"," +2 filename.temp | cut -d"," -f1,2,3
rm filename.temp

Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Script to sort the files and append the extension .sort to the sorted version of the file pankaj80 UNIX for Advanced & Expert Users 3 06-07-2011 09:28 AM
sort the csv file nagendramv Shell Programming and Scripting 2 07-23-2009 06:13 AM
awk -- sort out the out put file hegdeshashi Shell Programming and Scripting 3 06-02-2009 01:09 AM
Need to sort file learnerlearner Shell Programming and Scripting 4 04-17-2009 07:03 AM
how to sort a file tao UNIX for Dummies Questions & Answers 2 03-24-2002 10:34 AM



All times are GMT -4. The time now is 08:15 AM.