Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract columns from a file if the name dont exist put blank

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract columns from a file if the name dont exist put blank
# 1  
Old 10-04-2010
Extract columns from a file if the name dont exist put blank
Hi,

I am very new to Unix script. Suppose i have a file with column header:
NAME1 NAME2 Address Tel

And I always need to make a file with column header:
ID NAME1 NAME2 EMail Address Tel

For the columns that do not exist in the file, I would still like to make a column with blank.

How do I do this in UNix script?
# 2  
Old 10-04-2010
Assuming you are using TAB as field separator:
Code:
 awk -F"\t" -vOFS="\t" '{$6=$4;$5=$3;$4=(NR==1)?"EMail":"";$3=$2;$2=$1;$1=(NR==1)?"ID":""}1' file

# 3  
Old 10-04-2010
Since you haven't mentioned, I will assume that it's a fixed-format file -

Code:
$
$
$ cat f0
NAME1     NAME2     ADDRESS   TEL
name001   name002   addr001   tel001
name011   name022   addr011   tel011
name111   name222   addr111   tel111
$
$
$ awk 'NR==1{printf("%-10s%-10s%-10s%-10s%-10s%-s\n","ID",$1,$2,"EMAIL",$3,$4)}
       NR>1 {printf("%-10s%-10s%-10s%-10s%-10s%-s\n"," ",$1,$2," ",$3,$4)}
      ' f0
ID        NAME1     NAME2     EMAIL     ADDRESS   TEL
          name001   name002             addr001   tel001
          name011   name022             addr011   tel011
          name111   name222             addr111   tel111
$
$
$

tyler_durden
# 4  
Old 10-04-2010
If the fields are separated by spaces:
Code:
awk 'NR==1{$1="ID" FS $1; $3=$3 FS "EMail"}{$1=FS $1; $3=$3 FS}1' file

# 5  
Old 10-05-2010
This is what I have got so far.
Code:
abc.txt:
NAME1 NAME2 ADDRESS TEL
A B ADD 1234
 
awk -F"\t" '
NR==1{
for(i=1;i<=NF;i++)
if($i~/ID/)f[n]=i
n++
for(i=1;i<=NF;i++)
if($i~/NAME1/)f[n]=i
n++
for(i=1;i<=NF;i++)
if($i~/NAME2/)f[n]=i
n++
for(i=1;i<=NF;i++)
if($i~/EMAIL/)f[n]=i
n++
for(i=1;i<=NF;i++)
if($i~/ADDRESS/)f[n]=i
n++
for(i=1;i<=NF;i++)
if($i~/TEL/)f[n]=i
n++
}
{
if (NR==1)
print "ID NAME1 NAME2 EMAIL ADDRESS TEL"; 
}
{
if (NR > 1) {
for(i=0;i<n;i++)
printf"%s%s",i?" ":"",$f[i];
print""
}
}
' abc.txt

But I keep getting the error "Field is not correct"
I think the error is due to the ID AND EMAIL column not found (by searching for the column name) in abc.txt, hence f[n] evaluates to blank. When the code tries to do printf with $<blank>, it gives this error.

I am almost there, just need a tip how i can handle printf $<blank> so that it prints a blank column rather than errored out.

As i said, i am very new to unix scripting Smilie

---------- Post updated 10-05-10 at 08:03 AM ---------- Previous update was 10-04-10 at 06:24 PM ----------

Hi experts,

Anyone can help me with this please?
Potentially this implementation can help save any manual work that i am doing. That means less Overtime for me and more time with my family.

Thanks
# 6  
Old 10-05-2010
Your script doesn't have to be so convoluted.

Code:
$
$
$ cat abc.txt
NAME1   NAME2   ADDRESS TEL
A       B       ADD     1234
$
$
$ awk 'BEGIN{IFS="\t"; OFS="\t"} NR==1{$1="ID" IFS $1; $3="EMAIL" IFS $3} NR>1{$1=IFS $1; $3=IFS $3}1' abc.txt
ID      NAME1   NAME2   EMAIL   ADDRESS TEL
       A       B               ADD     1234
$
$
$

To check that those are TAB characters and not blank spaces -

Code:
$
$ # -e option displays "$" at end of line; -t option displays TABs as ^I
$ cat -et abc.txt
NAME1^INAME2^IADDRESS^ITEL$
A^IB^IADD^I1234$
$
$
$ awk 'BEGIN{IFS="\t"; OFS="\t"} NR==1{$1="ID" IFS $1; $3="EMAIL" IFS $3} NR>1{$1=IFS $1; $3=IFS $3}1' abc.txt | cat -et
ID^INAME1^INAME2^IEMAIL^IADDRESS^ITEL$
^IA^IB^I^IADD^I1234$
$
$
$

tyler_durden
# 7  
Old 10-05-2010
Hi Tyler,
Appreciate your prompt reply.
What if I don't know beforehand the columns that were missing? Will your code still work?

I was using for loop in my earlier posting to perform a column header name matching to try to find out 1) the missing column ( as this is not fixed)
2) the position of the column if it matches

I thought this is the way to go? U think u can help me?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extract data from a log file and put it in a file

Hi, I would like to seek your help for a script that will extract data from log file and put it in a file. Sample log file 2018-10-23 12:33:21 AI ERROR -- tpid: SAMPLE_TH account: 123456789 aiSessionNumber: 660640464 mapName: xxx to yyy errorDesc: Translation Error:ErrorNumber : 993 ... (2 Replies)
Discussion started by: neverwinter112
2 Replies

2. Shell Programming and Scripting

Script to find blank records in a file except for few columns

I have a file with the following format: X|High|2|GIC|DM||XHM|||6 Months X|Moderate|2|GIC|DM||XHM|||6 Months X|High|2|GCM|DM||XSF|||6 Months X|Med|2|GCM|DM||XSF|||6 Here there are ten columns but I need to print rows having blank records in any of the rows (except for 6th,8th and 9th... (10 Replies)
Discussion started by: chatwithsaurav
10 Replies

3. Shell Programming and Scripting

Remove blank columns from a tab delimited text file

Hello, I have some tab delimited files that may contain blank columns. I would like to delete the blank columns if they exist. There is no clear pattern for when a blank occurs. I was thinking of using sed to replace instances of double tab with blank, sed 's/\t\t//g' All of the examples... (2 Replies)
Discussion started by: LMHmedchem
2 Replies

4. Shell Programming and Scripting

Inserting blank columns in already present CSV file

Hi, i have a csv file which have headers and values of it like below : headers --> CI Ref SerialNumber LastScanDate values --> VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 i want to have a above csv in below format : headers --> CI Name CI Description CI Ref... (6 Replies)
Discussion started by: omkar.jadhav
6 Replies

5. Shell Programming and Scripting

How to sort a text file if certain columns are blank?

Dear all, I am trying to sort a text file based on column 3, 10, 11 and 12. But certain column are blank for some lines. Column 3 has to be in ascending order after sorting. Part of my input file is as follows: CN727990 1 A01 4703 5083 73.28 - A_scaffold000011 4365605 4365985 73.28 +... (10 Replies)
Discussion started by: huiyee1
10 Replies

6. UNIX for Dummies Questions & Answers

Rsync copy files if dont exist

I have a setup where I have two drives. TV TVbackup For what ever reason, I have a lot of content on my TVbackup drive which isn't on my TV drive. I want to copy all the files across which are on TVbackup but are not currently on TV. If there is a file with the same name but a... (2 Replies)
Discussion started by: Spadez
2 Replies

7. Shell Programming and Scripting

ls | grep (i dont know what to put here)

Dear users, I googled for a while, but i have got a lot of different answers regarding a simple unix command. lets say there are a lot of files in a directory. How can i list the files in a directory whose file types is "text"? Thank you in advance (4 Replies)
Discussion started by: kevincobain2000
4 Replies

8. Shell Programming and Scripting

Removing blank columns from a file

Hi, I have a sample file as shown below -- id parent name dba -----------------------------------... (7 Replies)
Discussion started by: sumirmehta
7 Replies

9. Shell Programming and Scripting

How to extract visually blank lines form the file

Hi, Could some one help me to get rid of visually blank lines from a file using shell or awk or sed (on Solaris machine)? When I use grep grep -v ^$ inputfile >outputfile it removes some blank lines.. but it seems some tab plus space balnk lines remains. thaen I used "grep -v '^]*$' ... (1 Reply)
Discussion started by: hadsuresh
1 Replies

10. UNIX for Advanced & Expert Users

Tab Completion showing files that Dont Exist

Has anyone seen this before? if i ls a particular directory (an email Maildir) i get a long list of files, if i 'du -h' that same directory, i get messages like: du: `./1053894791.17207_0.srweb,S=6199': No such file or directory strange. so using tab will complete this filename too, giving... (3 Replies)
Discussion started by: dittonamed
3 Replies
Login or Register to Ask a Question