LinuxQuestions.org
Help answer threads with 0 replies.
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 10-23-2012, 11:33 AM   #1
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Rep: Reputation: Disabled
file reorganisation


hello
i have a file that has columns disorganized columns

see attached,

i want to edit the file so that i can have all the columns with the same fields lined together:
ie

MSIDN,CAT,CSP,NAM,OFA,PWD,SCHAR,OICK
6392557081,CAT-9,CSP-9,NAM-1,OFA-1,PWD-0000,SCHAR-4,OICK-58
Attached Files
File Type: txt 1.txt (1.3 KB, 11 views)
 
Old 10-23-2012, 11:50 AM   #2
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Sorry, but I don't understand what you want to achieve? And how your columns in the original are disorganized?

Your original is fixed width, your output example is csv. Is that what you want to achieve? If so, it sounds like a job for sed.

//Edit
sorry, closer inspection shows that they are tab delimited; still a job for sed

Last edited by Wim Sturkenboom; 10-23-2012 at 12:11 PM.
 
Old 10-23-2012, 12:13 PM   #3
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Code:
wim@i3-2120:~/forums/lq4175433698$ sed -e 's/\t/,/g' 1.txt 
6392557081,CAT-9,CSP-9,NAM-1,OFA-1,PWD-0000,SCHAR-4,OICK-58
6392557082,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-55
6392557083,CSP-7,CAT-10,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58
6392557084,CSP-7,CAT-10,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58
6392557085,CSP-12,CAT-10,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58
6392557086,CSP-7,CAT-10,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58
6392557087,CSP-7,CAT-9,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-55
6392557088,CSP-7,CAT-9,NAM-0,OFA-1,PWD-0000,OFA-1,OICK-58
6392557089,CSP-7,CAT-9,NAM-0,SCHAR-4,OICK-58,OFA-1,PWD-0000
6392557090,CSP-12,CAT-9,NAM-3,SCHAR-4,OICK-55,OFA-1,PWD-0000
6392557091,CSP-7,CAT-9,NAM-3,SCHAR-4,OICK-58,OFA-1,PWD-0000
6392557092,CAT-9,CSP-7,NAM-0,SCHAR-4,OICK-58,OFA-1,PWD-0000
6392557093,CAT-10,CSP-7,NAM-0,SCHAR-4,OICK-58,OFA-1,PWD-0000
6392557094,CAT-10,CSP-12,NAM-3,SCHAR-4,OICK-58,OFA-1,PWD-0000
6392557095,CAT-10,CSP-7,NAM-3,SCHAR-4,OICK-55,OFA-1,PWD-0000
6392557096,CAT-10,SCHAR-4,NAM-3,CSP-7,PWD-0000,OFA-1,OICK-58
6392557097,CAT-9,SCHAR-4,NAM-0,CSP-7,PWD-0000,OFA-1,OICK-58
6392557098,CAT-9,SCHAR-4,NAM-3,CSP-7,PWD-0000,OFA-1,OICK-58
6392557099,CAT-9,SCHAR-4,NAM-3,CSP-7,PWD-0000,OFA-1,OICK-58
6392557100,CAT-10,SCHAR-4,NAM-3,CSP-7,PWD-0000,OFA-1,OICK-58
6392557101,CAT-10,SCHAR-4,NAM-0,CSP-7,PWD-0000,OFA-1,OICK-58
6392557102,CAT-10,SCHAR-4,NAM-0,CSP-7,PWD-0000,OFA-1,OICK-58
wim@i3-2120:~/forums/lq4175433698$
Redirect the output to another file and you're good to go
 
Old 10-24-2012, 01:51 AM   #4
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
Wim, look carefully at the file,i want to order it in a way that $1, has the numbers, $2 has CAT, $3 has CSP's only
 
Old 10-24-2012, 04:15 AM   #5
pixellany
LQ Veteran
 
Registered: Nov 2005
Location: Annapolis, MD
Distribution: Arch/XFCE
Posts: 17,802

Rep: Reputation: 738Reputation: 738Reputation: 738Reputation: 738Reputation: 738Reputation: 738Reputation: 738
Please post a sample of the output you would like to see. For example, do you want to delete any columns---or simply re-arrange them?

To move fields around within a line, I'm thinking that AWK might be better---if nothing else, you can simply grab fields into variables using AWK, and then echo the variables in the desired order.
 
Old 10-24-2012, 04:18 AM   #6
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
Thanks Pixellany, How do i do that?
 
Old 10-24-2012, 04:34 AM   #7
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389
Is this what you are looking for:
Code:
#!/bin/bash

INFILE="infile"

awk 'BEGIN { ln = 1 }
{ _[ln,1] = $1 }
$2 ~ /CAT/   { _[ln,2] = $2 }
$3 ~ /CAT/   { _[ln,2] = $3 }
$4 ~ /CAT/   { _[ln,2] = $4 }
$5 ~ /CAT/   { _[ln,2] = $5 }
$6 ~ /CAT/   { _[ln,2] = $6 }
$7 ~ /CAT/   { _[ln,2] = $7 }
$8 ~ /CAT/   { _[ln,2] = $8 }
$2 ~ /CSP/   { _[ln,3] = $2 }
$3 ~ /CSP/   { _[ln,3] = $3 }
$4 ~ /CSP/   { _[ln,3] = $4 }
$5 ~ /CSP/   { _[ln,3] = $5 }
$6 ~ /CSP/   { _[ln,3] = $6 }
$7 ~ /CSP/   { _[ln,3] = $7 }
$8 ~ /CSP/   { _[ln,3] = $8 }
$2 ~ /NAM/   { _[ln,4] = $2 }
$3 ~ /NAM/   { _[ln,4] = $3 }
$4 ~ /NAM/   { _[ln,4] = $4 }
$5 ~ /NAM/   { _[ln,4] = $5 }
$6 ~ /NAM/   { _[ln,4] = $6 }
$7 ~ /NAM/   { _[ln,4] = $7 }
$8 ~ /NAM/   { _[ln,4] = $8 }
$2 ~ /OFA/   { _[ln,5] = $2 }
$3 ~ /OFA/   { _[ln,5] = $3 }
$4 ~ /OFA/   { _[ln,5] = $4 }
$5 ~ /OFA/   { _[ln,5] = $5 }
$6 ~ /OFA/   { _[ln,5] = $6 }
$7 ~ /OFA/   { _[ln,5] = $7 }
$8 ~ /OFA/   { _[ln,5] = $8 }
$2 ~ /PWD/   { _[ln,6] = $2 }
$3 ~ /PWD/   { _[ln,6] = $3 }
$4 ~ /PWD/   { _[ln,6] = $4 }
$5 ~ /PWD/   { _[ln,6] = $5 }
$6 ~ /PWD/   { _[ln,6] = $6 }
$7 ~ /PWD/   { _[ln,6] = $7 }
$8 ~ /PWD/   { _[ln,6] = $8 }
$2 ~ /SCHAR/ { _[ln,7] = $2 }
$3 ~ /SCHAR/ { _[ln,7] = $3 }
$4 ~ /SCHAR/ { _[ln,7] = $4 }
$5 ~ /SCHAR/ { _[ln,7] = $5 }
$6 ~ /SCHAR/ { _[ln,7] = $6 }
$7 ~ /SCHAR/ { _[ln,7] = $7 }
$8 ~ /SCHAR/ { _[ln,7] = $8 }
$2 ~ /OICK/  { _[ln,8] = $2 }
$3 ~ /OICK/  { _[ln,8] = $3 }
$4 ~ /OICK/  { _[ln,8] = $4 }
$5 ~ /OICK/  { _[ln,8] = $5 }
$6 ~ /OICK/  { _[ln,8] = $6 }
$7 ~ /OICK/  { _[ln,8] = $7 }
$8 ~ /OICK/  { _[ln,8] = $8 }
{ ln++ }
END {
for ( x = 1; x <= NR; x++ ) {
print ""
for ( y = 1; y <= 8; y++ )
printf _[x,y] "," }
}
' "$INFILE" | sort -n
Example run based on infile posted in post #1:
Code:
./shuffle.sh

6392557081,CAT-9,CSP-9,NAM-1,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557082,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-55,
6392557083,CAT-10,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557084,CAT-10,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557085,CAT-10,CSP-12,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557086,CAT-10,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557087,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-55,
6392557088,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,,OICK-58,
6392557089,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557090,CAT-9,CSP-12,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-55,
6392557091,CAT-9,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557092,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557093,CAT-10,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557094,CAT-10,CSP-12,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557095,CAT-10,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-55,
6392557096,CAT-10,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557097,CAT-9,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557098,CAT-9,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557099,CAT-9,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557100,CAT-10,CSP-7,NAM-3,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557101,CAT-10,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
6392557102,CAT-10,CSP-7,NAM-0,OFA-1,PWD-0000,SCHAR-4,OICK-58,
 
1 members found this post helpful.
Old 10-24-2012, 05:00 AM   #8
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Quote:
Originally Posted by arn2025 View Post
Wim, look carefully at the file,i want to order it in a way that $1, has the numbers, $2 has CAT, $3 has CSP's only
What is $1 ? Column 1? If so, it already has the numbers. Your original file is tab delimited, so if you load that in e.g. a spreadsheet program (using <tab> as the delimiter) you will see the columns clearly. If you want to use a normal editor, use one that allows you to set the tabsize. My initial confusion came as I used gedit to open your file and the tabsize was big enough to have perfectly aligned columns (and therefore I assumed fixed width).

Last edited by Wim Sturkenboom; 10-24-2012 at 05:06 AM.
 
Old 10-24-2012, 06:15 AM   #9
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
thanks druuna, that works well but they are many more columns, do i have to do that for all the 40 columns, in my file that was just a sample of the columns
 
Old 10-24-2012, 07:32 AM   #10
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389Reputation: 2389
Quote:
Originally Posted by arn2025 View Post
thanks druuna, that works well but they are many more columns, do i have to do that for all the 40 columns, in my file that was just a sample of the columns
You do need to extend the given solution to fit your real data.

You can compound my previous solution a bit (still needs expanding for the real amount of columns):
Code:
#!/bin/bash

INFILE="infile"

awk 'BEGIN { ln = 1 }
{ _[ln,1] = $1  }
{
  for ( z = 2 ; z <= NF ; z++ ) { 
    if ( $z ~ /CAT/   ) _[ln,2] = $z # CAT in column 2
    if ( $z ~ /CSP/   ) _[ln,3] = $z # CSP in column 3
    if ( $z ~ /NAM/   ) _[ln,4] = $z
    if ( $z ~ /OFA/   ) _[ln,5] = $z
    if ( $z ~ /PWD/   ) _[ln,6] = $z
    if ( $z ~ /SCHAR/ ) _[ln,7] = $z
    if ( $z ~ /OICK/  ) _[ln,8] = $z
# extend above to fit real data
#    if ( $z ~ /ABC/  ) _[ln,D] = $z # ABC in column D
  } 
}
{ ln++ }
END {
for ( x = 1; x <= NR; x++ ) {
print ""
for ( y = 1; y <= NF; y++ )
printf _[x,y] "," }
}
' "$INFILE" | sort -n
 
1 members found this post helpful.
  


Reply


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
Nokia to shed 7,000 staff as part of reorganisation Jeebizz Linux - News 1 04-30-2011 01:38 PM
LXer: DistroWatch Weekly: New releases from Fedora and Ubuntu, Xandros reorganisation, Xen Demo CD LXer Syndicated Linux News 0 10-23-2006 07:21 AM
Grub - reorganisation CloudBuilder Linux - Newbie 7 05-06-2004 05:53 PM
How to play a media file/ video file/mp3 file recorded in harddisk/cd-rom arindam Linux - Newbie 2 09-05-2003 10:31 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

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

Main Menu
Advertisement
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
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration