Visit Our UNIX and Linux User Community


sorting csv file based on column selected


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sorting csv file based on column selected
# 1  
Old 07-03-2009
sorting csv file based on column selected

Hi all,

in my csv file it'll look like this, and of course it may have more columns

US to UK;abc-hq-jcl;multimedia
UK to CN;def-ny-jkl;standard
DE to DM;abc-ab-klm;critical
FD to YM;la-yr-tym;standard
HY to MC;la-yr-ytm;multimedia
GT to KJ;def-ny-jrt;critical

I would like to group them based on the 3rd column first, then sort them based on 2nd column so it'll look like this

DE to DM;abc-ab-klm;critical
GT to KJ;def-ny-jrt;critical
US to UK;abc-hq-jcl;multimedia
HY to MC;la-yr-ytm;multimedia
UK to CN;def-ny-jkl;standard
FD to YM;la-yr-tym;standard

it would also be nice if somehow I can make it look like this so it'll look more presentable :P,

CRITICAL
DE to DM;abc-ab-klm;critical
GT to KJ;def-ny-jrt;critical

Multimedia
US to UK;abc-hq-jcl;multimedia
HY to MC;la-yr-ytm;multimedia

Standard
UK to CN;def-ny-jkl;standard
FD to YM;la-yr-tym;standard
# 2  
Old 07-03-2009
hmmm use sort command
Code:
sort -t";" -k3 -k2 filename

and for second req use awk..
Code:
sort -t";" -k3 -k2 filename|awk -F\; '{A[$3]=A[$3]"\n"$0}END{for (i in A){print toupper(i)A[i]}}'

# 3  
Old 07-04-2009
Using awk rule idea.
Code:
ff=file.txt

sort -t";" -k3 -k2 $ff | \
   awk -F";" -v keyfld=3 '
      # - next key value
      $keyfld != prevkey { 
                print toupper($keyfld) 
                }
      # - every line
                { 
                print $0
                prevkey=$keyfld
                }
   '

Or using ksh
Code:
prevkey=""
oifs="$IFS"
typeset -u key
sort -t";" -k3 -k2 $ff | while read line
do
        IFS=";"
        set -A flds -- $line    # to array, 1st index 0 
        IFS="$oifs"
        key=${flds[2]}
        [ "$prevkey" != "$key" ] && print "$key"
        print "$line"
        prevkey=$key
done

# 4  
Old 07-04-2009
Because you said

Quote:
...it may have more columns
A slight modification to vidyadhar85's code
Code:
sort -t";" -k3 -k2 filename|awk -F\; '{A[$NF]=A[$NF]"\n"$0}END{for (i in A) {print toupper(i)A[i]}}'

# 5  
Old 07-05-2009
how about below perl:

Code:
my @arr=<DATA>;
print map {$_->[0]}
      sort {$a->[1]->[2] cmp $b->[1]->[2] or $a->[1]->[1] cmp $b->[1]->[1]}
      map {my @tmp=split(";",$_);[$_,\@tmp]}
      @arr;
__DATA__
US to UK;abc-hq-jcl;multimedia
UK to CN;def-ny-jkl;standard
DE to DM;abc-ab-klm;critical
FD to YM;la-yr-tym;standard
HY to MC;la-yr-ytm;multimedia
GT to KJ;def-ny-jrt;critical


Previous Thread | Next Thread
Test Your Knowledge in Computers #502
Difficulty: Medium
If a function uses a particular process or algorithm such as a Fast Fourier Transform to perform an operation, it would not be appropriate to document it in a series of comments in the source code.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

2. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

3. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

4. Shell Programming and Scripting

Fetching values in CSV file based on column name

input.csv: Field1,Field2,Field3,Field4,Field4 abc ,123 ,xyz ,000 ,pqr mno ,123 ,dfr ,111 ,bbb output: Field2,Field4 123 ,000 123 ,111 how to fetch the values of Field4 where Field2='123' I don't want to fetch the values based on column position. Instead want to... (10 Replies)
Discussion started by: bharathbangalor
10 Replies

5. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

6. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has... (2 Replies)
Discussion started by: Ganesh L
2 Replies

7. Shell Programming and Scripting

[Solved] Sorting a column in a file based on a column in a second file

Hello, I have two files as the following: File1: F0100020 A G F0100030 A T F0100040 A G File2: F0100040 A G BTA-28763-no-rs 77.2692 F0100030 A T BTA-29334-no-rs 11.4989 F0100020 A G BTA-29515-no-rs 127.006 I want to sort the second file based on the... (6 Replies)
Discussion started by: Homa
6 Replies

8. Shell Programming and Scripting

Sorting file based on a numeric column

Hi, I use UBUNTU 12.04. I have a file with this structure: Name 2 1245787 A G 12 14 12 14 .... Name 1 1245789 C T 13 12 12 12..... I would like to sort my file based on the second column so to have this output for example: Name 1 1245789 C T 13 12 12 12..... Name 2 1245787 A G 12 14... (4 Replies)
Discussion started by: Homa
4 Replies

9. UNIX for Dummies Questions & Answers

Sorting a file based on the absolute value of a column

I would like to sort a tab delimited text file based on the absolute value of its second column. How do I go about doing that? Thanks! Example input: A -12 B 0 C -6 D 7 Output: A -12 D 7 C -6 B 0 (4 Replies)
Discussion started by: evelibertine
4 Replies

10. Shell Programming and Scripting

sorting based on a specified column in a text file

I have a tab delimited file with 5 columns 79 A B 20.2340 6.1488 8.5086 1.3838 87 A B 0.1310 0.0382 0.0054 0.1413 88 A B 46.1651 99.0000 21.8107 0.2203 89 A B 0.1400 0.1132 0.0151 0.1334 114 A B 0.1088 0.0522 0.0057 0.1083 115 A B... (2 Replies)
Discussion started by: Lucky Ali
2 Replies

Featured Tech Videos