LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 08-25-2010, 11:26 AM   #1
govi1234
LQ Newbie
 
Registered: Aug 2010
Posts: 2

Rep: Reputation: 0
Rearrange the column in CSV file..


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.
 
Old 08-25-2010, 11:29 AM   #2
amani
Senior Member
 
Registered: Jul 2006
Location: Kolkata, India
Distribution: 64-bit GNU/Linux, Kubuntu64, Fedora QA, Slackware,
Posts: 2,758

Rep: Reputation: Disabled
This is extremely easy to do in R for example
 
Old 08-26-2010, 08:25 PM   #3
ufmale
Member
 
Registered: Feb 2007
Posts: 385

Rep: Reputation: 30
why not using excel or Openoffice to open it. Re-arrange and then save it as csv.
 
Old 08-26-2010, 08:38 PM   #4
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,655

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
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.
 
Old 08-27-2010, 05:52 AM   #5
govi1234
LQ Newbie
 
Registered: Aug 2010
Posts: 2

Original Poster
Rep: Reputation: 0
no it through error and want to change in to CSV file column heading and arrange accordingly ..

please help me out.
 
Old 08-27-2010, 05:58 AM   #6
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,655

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
Sorry is that directed at me? Assuming it is, you do know you had to fill in the missing parts where I put . . .?
 
Old 08-27-2010, 06:02 AM   #7
terencemo
LQ Newbie
 
Registered: Aug 2010
Location: Bangalore, India
Distribution: Debian, Ubuntu
Posts: 3

Rep: Reputation: 0
Lightbulb Perl can do this easily

perl -ne 'chomp $_; @a = split(/\s+/, $_); print join(",", $a[2], $a[3], $a[4], $a[0], $a[1]) . "\n"'
 
Old 08-27-2010, 09:06 AM   #8
estabroo
Senior Member
 
Registered: Jun 2008
Distribution: debian, ubuntu, sidux
Posts: 1,095
Blog Entries: 2

Rep: Reputation: 111Reputation: 111
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
 
Old 08-27-2010, 10:19 AM   #9
MTK358
LQ 5k Club
 
Registered: Sep 2009
Posts: 6,443
Blog Entries: 3

Rep: Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714Reputation: 714
I don't understand the problem.
 
Old 08-27-2010, 08:15 PM   #10
theNbomr
LQ 5k Club
 
Registered: Aug 2005
Distribution: OpenSuse, Fedora, Redhat, Debian
Posts: 5,396
Blog Entries: 2

Rep: Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903
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.
 
  


Reply

Tags
column, csv, file


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to rearrange a text file with awk keenboy Programming 9 11-04-2009 08:07 AM
replace line in CSV file and rename file connected to that name wademac Linux - Newbie 3 07-15-2009 02:09 PM
Rearrange lines in a file coady77 Linux - Newbie 17 06-12-2009 11:35 AM
Read text file column by column RVF16 Programming 11 05-31-2009 08:16 AM
Comparing two csv files and write different record in third CSV file irfanb146 Linux - Newbie 3 06-30-2008 10:15 PM


All times are GMT -5. The time now is 01:43 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration