Rearrange the column in CSV file..

govi1234 · 08-25-2010, 10:26 AM

Hi All,

I have the csv file with the below column,

Gender Modifier LastNameMiddleName FirstName
Female Nurse AASER L ROCHELLE
Female Nurse AASER L ROCHELLE
Female Nurse ABBOTT L MARY
Female Nurse ABBOTT L MARY

I need a script to rearrange the column but i can do like below,

awk -F"," '{ print $1",",$4",",$5",",$6",",$3",",$8",",$7",",$2 }' file2.csv > file3.csv

But the column postion will be change everytime, may be next time will be like this

LastNameMiddleName FirstName Gender Modifier
AASER L ROCHELLE Female Nurse
AASER L ROCHELLE Female Nurse
ABBOTT L MARY Female Nurse
ABBOTT L MARY Female Nurse

Please provide the scripts.

amani · 08-25-2010, 10:29 AM

This is extremely easy to do in R for example

ufmale · 08-26-2010, 07:25 PM

why not using excel or Openoffice to open it. Re-arrange and then save it as csv.

grail · 08-26-2010, 07:38 PM

Well I am not sure there is an easy way if it is going to be constantly changing, but the following idea could work:

Code:

#!/usr/bin/awk -f

BEGIN{  OFS=FS=","
        one=1
        two=2
        . . .
        seven=7
        eight=8
}

{   print $(one),$(two),. . .,$(seven),$(eight) }

Now all you would need to do each time there is a change is set the variables in the BEGIN to the order you require.

govi1234 · 08-27-2010, 04:52 AM

no it through error and want to change in to CSV file column heading and arrange accordingly ..

please help me out.

grail · 08-27-2010, 04:58 AM

Sorry is that directed at me? Assuming it is, you do know you had to fill in the missing parts where I put . . .?

terencemo · 08-27-2010, 05:02 AM

perl -ne 'chomp $_; @a = split(/\s+/, $_); print join(",", $a[2], $a[3], $a[4], $a[0], $a[1]) . "\n"'

estabroo · 08-27-2010, 08:06 AM

what I'd do in perl is grab the first line and split it, insert that into a hash with the position in the split as the value, then when you split every line after that you just print the string you want using the label

chomp;
@data = split(' ')
print join(",", $data[$pos{'LastNameMiddleName'}], $data[$pos{'FirstName'}], $data ...

then it's irrelevant the order the data comes in

MTK358 · 08-27-2010, 09:19 AM

I don't understand the problem.

theNbomr · 08-27-2010, 07:15 PM

You have a 'CSV' (where are the 'C's?), with 5 columns of data. So there are a fairly small number of possible orders for the columns. Use an input string that contains the column numbers, ordered from 0 of course. Untested...

Code:

#! /usr/bin/perl -w
#
# LQ-govi1234.pl
#
#  Usage: LQ-govi1234.pl 31245 csvFileName.dat
#
use strict; # of course...
my $order=shift;
my @columns = split //, $order;
while(<>){
  @csv=split /\s+/, $_;
  foreach my $column ( @columns ){
    print "$csv[$column] ";
  }
  print "\n";
}

Should get you close.

--- rod.