Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Text Conversion in Column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Text Conversion in Column
# 1  
Old 09-23-2014
Text Conversion in Column
Hi all;

We have a table:

Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 1 val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

As you see, 6th columns are filled with numbers as, 1, 2, 3, 4, etc... and I want to make a conversion for these values such as:
if 6th column=1, write "RED";
if 6th column=2, write "BLUE";
...

and output should be:
Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 RED val7
val1 val2 val3 val4 val5 YELLOW val7
val1 val2 val3 val4 val5 ORANGE val7
val1 val2 val3 val4 val5 BLUE val7
...

how can I write it with a shell script?
# 2  
Old 09-23-2014
What have you tried?
# 3  
Old 09-23-2014
Using Perl
Code:
$ perl -ne 'chomp;@colour=qw(COL6 orange yellow green blue indigo violet);@r=split;print join (" ",@r[0..4],$colour[$r[5]],$r[6]),"\n";' tmp/tmp.dat
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 orange val7
val1 val2 val3 val4 val5 green val7
val1 val2 val3 val4 val5 indigo val7
val1 val2 val3 val4 val5 yellow val7

Last edited by Skrynesaver; 09-23-2014 at 05:27 AM..
# 4  
Old 09-23-2014
I had a table like:

Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 1 val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

applied this:
Code:
awk 'BEGIN{OFS=FS="\t"}$5==1{$5="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt

and output is:
Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 RED val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

but, how to add another conditions for
Code:
$5==2, 3, 4,

etc...
# 5  
Old 09-23-2014
Code:
awk 'BEGIN{a[1] = "RED"; a[2] = "BLUE"; a[3] = "YELLOW"; a[5] = "ORANGE"} NR>1 {$6 = a[$6]}1' file

# 6  
Old 09-23-2014
thanks SriniShoo but:

Code:
awk 'BEGIN{a[1] = "RED"; a[2] = "BLUE"; a[3] = "YELLOW"; a[5] = "ORANGE"} NR>1 {$6 = a[$6]}1' file
awk: syntax error near line 1
awk: bailing out near line 1

how to add another conditions to:
Code:
awk 'BEGIN{OFS=FS="\t"}$5==1{$5="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt

# 7  
Old 09-23-2014
Quote:
Originally Posted by gc_sw
I had a table like:

Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 1 val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

applied this:
Code:
awk 'BEGIN{OFS=FS="\t"}$5==1{$5="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt

and output is:
Code:
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 RED val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

but, how to add another conditions for
Code:
$5==2, 3, 4,

etc...
Given that there are no tabs in your sample input nor in your sample output, and that if the spaces shown in your input had been tabs there is no 1 in field 5 (it is in field 6); I don't see how you got that output from the input you showed us using the code you showed us???

It looks like you have sample awk code from SriniShoo and perl code from Skrynesaver that will turn your sample input into your sample output ignoring the implication that you want tab separators in your output.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

2. Shell Programming and Scripting

Conversion if 1st column is match (awk '{s+=$1} END

Hi im trying to add numbers, got no problem with it im using awk '{s+=$1} END {print s, "MB"}', but what if the numbers are like mention below. who will i add them 2000 KB 1 MB Answer: 2001 Desired: 2000 KB 1 MB Answer: 3000 (4 Replies)
Discussion started by: invinzin21
4 Replies

3. UNIX for Dummies Questions & Answers

[Solved] Uneven column to row conversion

Hi Unix Forum, I have a relatively easy question i suppose for which, however, until now i could not find a solution. I am working with a program that will give me an output file similar to the following: A 1 2 3 4 B 1 2 3 4 C 1 (9 Replies)
Discussion started by: Leander
9 Replies

4. Solaris

Rows to column conversion in Solaris

i am using the command pkginfo -l | /usr/xpg4/bin/grep -e 'VENDOR' -e 'NAME' -e 'VERSION' >> /tmp/test1.txt in order to get name,vendor and version of the applications installed on Solaris machine.The output stored in test1.txt in as shown below: NAME: Solaris Zones (Usr) VERSION: ... (4 Replies)
Discussion started by: omkar.jadhav
4 Replies

5. Shell Programming and Scripting

Row to Column conversion?

I have a text file with the geneIds separated by space in each line. The number Ids in lines are different. The file is like: abc qwe tyu ghj jkl dfg sdf cvb sdk fgh tyu uio iop tyu rty eru wer rty iop asd sdf dfg fgh zxc I want to format the file like: abc qwe tyu ghj jkl ... (7 Replies)
Discussion started by: sammy777
7 Replies

6. Shell Programming and Scripting

Lines to Column conversion

Hi All, I have a sample file with content: # cat test1.txt mahesh suresh anil # I need to get it like this: m s a a u n h r i e e l s s h h Basically row to column and column to row. I am trying to use this: while read line do echo $line | fold -bw 1 (2 Replies)
Discussion started by: digitalrg
2 Replies

7. Shell Programming and Scripting

text to csv conversion

Thank u every body ......just need a help so that a text file needs to be converted into CSV............. my log file is as follows Host scsi3: usb-storage Vendor: Maxtor Product: OneTouch III Serial Number: 044303E5 Protocol: Transparent SCSI Transport: Bulk ... (4 Replies)
Discussion started by: tangotango
4 Replies

8. UNIX for Dummies Questions & Answers

converting unique identifiers in a column using conversion file

Hello, I often have this problem: I have a file with a column of unique identifiers e.g. file1 below has an id column and data column/columns with p rows: cat data1 dog data2 cow data3 . . . elephant datap-1 horse datap and I have a conversion file,file2, with n<p rows... (4 Replies)
Discussion started by: peanuts48
4 Replies

9. Shell Programming and Scripting

Text to xlsx conversion

Hi Guys, The code snippet below is working fine with xls 2003 but the same is not working in xlsx 2007. Can someone please help. #Convert .txt file format to .xls format awk 'BEGIN{ FS="|" cols=8 print "<HTML><style type=\"text/css\">td{mso-number-format:\"\@\"}</style><BODY><TABLE... (1 Reply)
Discussion started by: icefish
1 Replies

10. Shell Programming and Scripting

column to row conversion with additional pattern

Hi there, I've an input file1 as follows: 1001 1002 1003 1004 1005 I would like to have an output file2 as follows: Numbers are 1001/ 1002/ 1003/ 1004/ 1005/ Any help is appreciated. (2 Replies)
Discussion started by: kbirde
2 Replies
Login or Register to Ask a Question