Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

use awk to read randomly located columns in an excel file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting use awk to read randomly located columns in an excel file
# 1  
Old 08-09-2008
Error use awk to read randomly located columns in an excel file
Hi,

I have an excel file that have a random count of columns/fields and what im trying to do is to only retrieve all the rows under 2 specific field headers.

I can use the usually command for awk which is awk 'print{ $1 $2}' > output.txt, but the location of the 2 specific field headers is not constant so it will not be useful to use the command.

example:(headers needed are name & age)
for today the generated column count and location of needed headers are:

name age sex location company

the next day the column count and location of the needed headers might be

company age location id name address


Is there a command that can be used just to read the headers that i just need and then once the headers are found retrieve all the rows under the header and place them to output.txt?

Thanks,
mdap
SmilieSmilieSmilie
Last edited by mdap; 08-09-2008 at 11:28 AM..
# 2  
Old 08-09-2008
The following script, display selected columns of the input file.
The names of selected columns are specified in the Columns variable.
Code:
Columns="file,size"
awk -v Columns="$columns" '
BEGIN {
   cols_count = split(Columns, cols, ",");
   for (i=1; i<=cols_count; i++)
      col_fields[cols[i]] = 0;
}
NR==1 {
   for (i=1; i<=NF; i++)
      col_fields[$i] = i;
}
{
   out = "";
   for (i=1; i<=cols_count; i++) {
      col_hdr = cols[i];
      out = (i>1 ? out OFS : "") (col_fields[col_hdr] ? $(col_fields[col_hdr]) : "?" );
   }
   print out;
}
' mdat.txt

Sample input file (mdat.txt)
Code:
perms   link owner       group size d1  d2    d3 file
-rw-r--r-- 1 Jean-Pierre Aucun   85 Jul  1  2007 p.sh
-rw-r--r-- 1 Jean-Pierre Aucun  104 Jul  1  2007 p2.sh
-rw-r--r-- 1 Jean-Pierre Aucun  121 Aug  3 19:27 alalush.sh
-rw-r--r-- 1 Jean-Pierre Aucun  180 Oct 10  2007 raghav.sh
-rw-r--r-- 1 Jean-Pierre Aucun  417 Jul  6  2007 struct.sh
-rw-r--r-- 1 Jean-Pierre Aucun  682 Nov  6  2007 single.sh
-rw-r--r-- 1 Jean-Pierre Aucun  703 Aug  9 17:42 mdat.sh
-rw-r--r-- 1 Jean-Pierre Aucun  789 Aug  1 17:43 sm.sh
-rw-r--r-- 1 Jean-Pierre Aucun  888 Aug  3 16:03 mogarb2.sh
-rw-r--r-- 1 Jean-Pierre Aucun  946 Aug  8 18:59 asirohi.sh

Output
Code:
file size
p.sh 85
p2.sh 104
alalush.sh 121
raghav.sh 180
struct.sh 417
single.sh 682
mdat.sh 703
sm.sh 789
mogarb2.sh 888
asirohi.sh 946

Jean-Pierre.
# 3  
Old 08-09-2008
HI aigles,

i tried to copy paste the exact code to mdat.sh and also the input file to mdat.txt and when i executed mdat.sh it only showed blank rows and no data in it...

mdap
# 4  
Old 08-09-2008
Post your OS. Use "uname -a" and post the results. If you are using Solaris/SunOS, switch "awk" to "nawk".
# 5  
Old 08-10-2008
its GNU/Linux... that is why im using awk..
# 6  
Old 08-10-2008
Quote:
Originally Posted by mdap
HI aigles,

i tried to copy paste the exact code to mdat.sh and also the input file to mdat.txt and when i executed mdat.sh it only showed blank rows and no data in it...

mdap
Sorry, typo error
Code:
Columns="file,size"
awk -v Columns="$Columns" '
BEGIN {

Jean-Pierre.
# 7  
Old 08-10-2008
Thanks much aigles!!! its now working fine..

Would you mind to explain the meaning of the block of code below, if it is ok with? (",)..

NR==1 {
for (i=1; i<=NF; i++)
col_fields[$i] = i; }

{
out = "";
for (i=1; i<=cols_count; i++) {
col_hdr = cols[i];
out = (i>1 ? out OFS : "") (col_fields[col_hdr] ? $(col_fields[col_hdr]) : "?" );
}
print out;
}

THANK YOU Master!... Smilie Smilie Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need specific columns in a log file as excel.

Hi All... I am in need of few columns from a log file.. in .xls file... below is what i have tried. my log file has 16 colums with " ; " as delimiter, but i need randomn columns 1 2 3 4 5 6 10 11 16 in an excel. I tried to awk the columns with delimiter ; and it worked, below is the log... (5 Replies)
Discussion started by: nanz143
5 Replies

2. UNIX for Dummies Questions & Answers

Solaris server read and write randomly slow

Hi! It's been a week now that connections to my server are slow. I can transfer one file of 6 GB at 50 MB/s but with a folder of the same size with multiple files I go down to 4 MB/s. Also, browsing a folder with multiple files takes a lot more time for the files to show. The only thing I've... (9 Replies)
Discussion started by: Boogie
9 Replies

3. Shell Programming and Scripting

Randomly inserting extra columns into csv file

Hi Tech Guru, I have a test file as below , which needs some more fields to be populated randomly : dks3243;12;20130823;1420;25m;0;syt dks3243;rocy;10 dks3243;kiop;18 sde21p4;77;20151210;8479;7py;9;vfr sde21p4;temp;67 sfq6i01;12;20120123;3412;4rd;7;jui sfq6i01;uymk;90 sfq6i01;kiop;51 ... (8 Replies)
Discussion started by: Lokesha
8 Replies

4. UNIX for Dummies Questions & Answers

How to delete columns with numbers in an excel file?

Dear all, I have one file (see below) with more then 100 columns and 2500 rows, and need only column which has GType in label with Alphabets, please help me to remove these columns with numbers. input file is n.201.GType n-201.Theta n-201.R n_1.GType n_1.Theta n_1.R... (6 Replies)
Discussion started by: AAWT
6 Replies

5. UNIX for Dummies Questions & Answers

To compare first two columns in an excel file

Hi All, i have a excel sheet with two columns as below. column1 column2 100 100 200 300 300 400 400 400 500 600 i need to compare the values these two columns and the output should be printed in the third column...if these values are equal the output should be green and if these... (2 Replies)
Discussion started by: arunmanas
2 Replies

6. Shell Programming and Scripting

How to sort columns in excel(csv) file

i want sort columns with headers based on another file headers file1 eg: i'm having an empty file with only coumn names like lastname firstname title expirydate stlcno status etc... another file with same column names and some other as well but in different order... file2 eg:firstname... (2 Replies)
Discussion started by: Man83Nagesh
2 Replies

7. Shell Programming and Scripting

how to convert fields from a text file to excel columns

i have this file which has the following contents: ,-0.3000 ,-0.3000 ,-0.3000 ,-0.9000 ,-0.9000 ,-0.9000 i would like to get this: -0.3-0.9-0.3-0.9-0.3-0.9 so far i am trying: awk '{for(i=1; i<=NF; i++) {printf("%f\n",$i)}}' test1 > test2 any help... (4 Replies)
Discussion started by: npatwardhan
4 Replies

8. Shell Programming and Scripting

printing two lines in awk as two columns in excel

hi guys, i would like to print two lines from a file as two adjacent columns using excel using awk.. i have this so far: awk '{for(i=1; i<=NF; i++) {printf("%s\n",$i)}}' "$count".ttt > "$count".csv #this to print the first line from the .ttt file as rows of the first column in the .csv... (9 Replies)
Discussion started by: npatwardhan
9 Replies

9. Shell Programming and Scripting

Read line from file randomly

I have data file with customer.dat, and this contains the customer names >cat customer.dat FirstName1 LastName1 FistName2 LastName1 FistName3 MiddleName3 LastName3 This file can contain areoun 100 customer names. Regards, (1 Reply)
Discussion started by: McLan
1 Replies

10. Programming

how can i read an excel file using C?

hi.. i have the next question: i need to read an excel file (xls) using a C program. Is that possible? how can i do that? please, any idea thanks (6 Replies)
Discussion started by: DebianJ
6 Replies
Login or Register to Ask a Question