use awk to read randomly located columns in an excel file


 
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-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
# 2  
Old 08-10-2008
Code:
NR==1 {                            # Select first line = header 
   for (i=1; i<=NF; i++)           #    For every field i (NF is last field# ) 
      col_fields[$i] = i;          #       Memorize field# for column name ($i)
}	  

{                                  # Select every line             
   out = "";                       #    Initialize out valriable (used for line diplay) 
   for (i=1; i<=cols_count; i++) { #    For every selected columns
      col_hdr = cols[i];           #       get column name
      out = (i>1 ? out OFS : "") (col_fields[col_hdr] ? $(col_fields[col_hdr]) : "?" );
	                            #       Concatenate column value $(col_fields[col_hdr]
                                   #       to out variable with Output Field Separator 
   }                               #
   print out;                      #    Print columns
}

# 3  
Old 08-10-2008
Thanks Again!!!

last question though.. what does "" and "?" in the code mean?

as far as i can understand it the line of code:
out = (i>1 ? out OFS : "") (col_fields[col_hdr] ? $(col_fields[col_hdr]) : "?" );

means

out = if i>1 is true then out OFS else "" and if col_fields[col_hdr] is true then $(col_fields[col_hdr]) else "?"

am i correct? :-?
# 4  
Old 08-10-2008
Exactly
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