Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Modify an XLS file with Awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Modify an XLS file with Awk
# 1  
Old 03-06-2012
Modify an XLS file with Awk
Hello,

I have 2 files. One has a list of serial numbers:

Code:
 
12345_7
2345_9
35454
4759:1
PEP8794

The other is an excel file, with multiple columns, separated by tab:

Code:
 
12345_7 ... ... .. .. .. .. ..  
2345_9 ... ... .. .. .. .. ..  
35454 ... ... .. .. .. .. ..  
4759:1 ... ... .. .. .. .. .. 
YEP0994 ... ... .. .. .. .. .. 
TAHT7878 ... ... .. .. .. .. .. 
AGCRT99 ... ... .. .. .. .. ..

I want the following output, with an extra column added to the excel file, if the serial number is found in file 1, and leave the column "blank" if not found:

Code:
 
FOUND 12345_7 ... ... .. .. .. .. ..  
FOUND 2345_9 ... ... .. .. .. .. ..  
FOUND 35454 ... ... .. .. .. .. ..  
FOUND 4759:1 ... ... .. .. .. .. .. 
 YEP0994 ... ... .. .. .. .. .. 
 TAHT7878 ... ... .. .. .. .. .. 
 AGCRT99 ... ... .. .. .. .. ..

I have the following, but it does not print the desired output:

Code:
 
awk 'NR==FNR{id1=$1;a[id1]=$0;next;}{id2=$1; if($id1==$id2){print "FOUND \t" a[id1];} else {print "\t" a[id1];}}' myFile.xls serialNumbers.txt > result

Any suggestions?
# 2  
Old 03-06-2012
Quote:
Originally Posted by ad23
Any suggestions?
Yes. Reverse the order in which the files are processed. Read the serial numbers first, using them as keys in an array. Then when reading the xls test if $1 is a key in the array. Modify the line according to the result of that test.

Regards,
Alister
This User Gave Thanks to alister For This Post:
ad23
# 3  
Old 03-06-2012
Thanks, but it is still not producing the desired output. It prints "Found" for ALL records in excel....

Code:
 
awk 'NR==FNR{a[id]=$1;next;}{if(a[id]=$1){print "FOUND\t" $0;}else{print "\t" $0;} }' serialNumber.txt myFile.xls > output

# 4  
Old 03-06-2012
You can't read xls files in awk. Try exporting it as csv or tab-separated.

If it's already tab-separated, it's not an xls...
# 5  
Old 03-06-2012
Never mind, this works:
Code:
 
 
awk 'NR==FNR{a[$0];next;}{if($1 in a){print "Found\t" $0;}else {print "\t" $0;} }' serialNumbers myFile.xls > xx

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sed, awk or another bash command to modify string with the content of another file

Hello everybody, I would like modify some strings using sed or another command line with the content file. For example: - {fqdn: "server-01" , ip: "server-01"} - {fqdn: "server-02" , ip: "server-02"} - {fqdn: "server-03" , ip: "server-03"} - {fqdn: "server-04" , ip: "server-04"} My... (4 Replies)
Discussion started by: dco
4 Replies

2. UNIX for Beginners Questions & Answers

Convert Excel File (xls) to tab delimited text file on AIX

Hi i have a problem in my job i try to convert an excel file (xls extention) to text file (tab delimited), but no result with this comand cat xxx.xls > xxx.txt Do you have eny idea? PS: sorry for my english Thanks!! (4 Replies)
Discussion started by: frisso
4 Replies

3. Shell Programming and Scripting

Merging Multiple XLS into Different tabs in xls/ xlsx

HI, I have multiple files per dept in folder for eg : In a folder File1_Dept100.xls File2_Dept100.xls File3_Dept100.xls File1_Dept200.xls File2_Dept200.xls File3_Dept200.xls Output should be : Dept100.xls which has File1, File2, File3 in different tabs Dept200.xls which has... (1 Reply)
Discussion started by: venkyzrocks
1 Replies

4. Shell Programming and Scripting

Converting text files to xls through awk script for specific data format

Dear Friends, I am in urgent need for awk/sed/sh script for converting a specific data format (.txt) to .xls. The input is as follows: >gi|1234|ref| Query = 1 - 65, Target = 1677 - 1733 Score = 8.38, E = 0.6529, P = 0.0001513, GC = 46 fd sdfsdfsdfsdf fsdfdsfdfdfdfdfdf... (6 Replies)
Discussion started by: Amit1
6 Replies

5. Shell Programming and Scripting

Modify text file using awk

I have text file with lines as shown here. Each row has 11 columns separated by tab. In each row, i want to split the 8th column such that the output should look like shown below. Here value in the 9th column is DP value and in the 10th column is MQ value followed by the values after resource.EFF=.... (15 Replies)
Discussion started by: mehar
15 Replies

6. Shell Programming and Scripting

modify awk

awk "BEGIN {if($PERCENT<$WARNING) {print \"OK\" ; exit 0} else if(($PERCENT>=$WARNING) && ($PERCENT<$CRITICAL)) {print \"WARNING\" ; exit 1} else if($PERCENT>=$CRITICAL) {print \"CRITICAL\" ; exit 2} }" how can i... (1 Reply)
Discussion started by: SkySmart
1 Replies

7. Shell Programming and Scripting

Modify the file with awk,sed or perl

Hi All, I need help from any of you.Would be so thankful for your help. I/P DDDD,1045,161,1557,429,1694,800,1911,1113,2460,1457,2917> 1609,3113,1869,3317,2732,3701,3727,4132,5857,5107> 9004,6496 DDDD,1125,157,1558,429,1694,800,1911,1117,2432,1444,2906>... (2 Replies)
Discussion started by: Indra2011
2 Replies

8. Shell Programming and Scripting

awk can't modify the input file ??

Hi * I've just wanted to ask you if it's possible to modify the input file by using awk. Let me explain what I need: I need to change the value $4 from "defaults" to "nodev" in my text file. I've tried to use a string function called "sub" and it works. But I can't figure it out how to... (6 Replies)
Discussion started by: martinp111
6 Replies

9. Shell Programming and Scripting

converting xls file to txt file and xls to csv

I need to convert an excel file into a text file and an excel file into a CSV file.. any code to do that is appreciated thanks (6 Replies)
Discussion started by: bandar007
6 Replies

10. Shell Programming and Scripting

modify file using awk

I have a file, a.asc which is generated from a shell script: -----BEGIN PGP MESSAGE----- Version: PGP 6.5.8 qANQR1DBwE4DR5PN6zVjZTcQA/9z5Eg94cwYdTnC7v+JUegQuJwHripqnyjFrEs/ejzKYCNmngbHHmf8V4K3uFkYyp74aFf+CdymA030RKs6ewOwkmqRW19oIXCgVe8Qmfg+/2KTq8XN =0QSP -----END PGP MESSAGE----- I want... (12 Replies)
Discussion started by: nattynatty
12 Replies
Login or Register to Ask a Question