Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Complex data sorting in excel files or text files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Complex data sorting in excel files or text files
# 15  
Old 06-11-2012
Quote:
Originally Posted by ygemici
* what is your O.S ?
* your full input file ? (can you attach it ?)

I am sorry,,,O.S abbreviate for??
I'll try to send you the input file.

Regards
# 16  
Old 06-11-2012
Quote:
Originally Posted by AAWT
I am sorry,,,O.S abbreviate for??
I'll try to send you the input file.

Regards
Hi Abdul, I looked at your file, try this Smilie
Code:
# awk 'NR!=1{y=$1;gsub("[0-9]*","",y);split($1,s,"_");gsub("[^0-9]*","",$1);a[c++]=y FS $1}END{for(j=0;j<c;j++){
y=split(a[j],s,"[_ ]*");for(i=0;i<c;i++){x=split(a[i],ss,"[_ ]*");if(s[y]==ss[x]&&s[1]==v2&&ss[1]==v1){
sub(" ","",a[j]);sub(" ","",a[i]);print a[j],a[i];}}}}' yourfile v1="C" v2="A"
A_JCVI_13208 C_JCVI_13208
A_JCVI_1353 C_JCVI_1353
A_JCVI_9099 C_JCVI_9099
A_JCVI_9938 C_JCVI_9938
A_JCVI_24808 C_JCVI_24808
A_DN191394 C_DN191394
A_JCVI_7849 C_JCVI_7849
A_JCVI_30564 C_JCVI_30564
A_JCVI_2657 C_JCVI_2657
A_AM062153 C_AM062153
A_JCVI_3665 C_JCVI_3665
A_JCVI_36467 C_JCVI_36467
............................
............................

regards
ygemici
This User Gave Thanks to ygemici For This Post:
AAWT
# 17  
Old 06-12-2012
Dear Ygemici,
thanks a lot for your effort,,,,,but there is still some problem
check line 4 and 5, that are not pairs ,,,these are still not giving the required output.
Code:
A_DY000990 C_JCVI_990
A_EX091309 C_EX091309
A_AM388645 C_AM388645
A_AM386941 C_RC_AM386941
A_AB012650 C_JCVI_12650
A_DN191394 C_DN191394



Is it possible to get in this form in a new text file,,,,if possible with all columns of values also.
Code:
A_DY000990 
C_JCVI_990
A_EX091309 
C_EX091309
A_AM388645 
C_AM388645

sorry but i am really not good to make changes by myself in command

thanks a lot

Regards
# 18  
Old 06-13-2012
Quote:
Originally Posted by AAWT
Dear Ygemici,
thanks a lot for your effort,,,,,but there is still some problem
check line 4 and 5, that are not pairs ,,,these are still not giving the required output.
Code:
A_DY000990 C_JCVI_990
A_EX091309 C_EX091309
A_AM388645 C_AM388645
A_AM386941 C_RC_AM386941
A_AB012650 C_JCVI_12650
A_DN191394 C_DN191394



Is it possible to get in this form in a new text file,,,,if possible with all columns of values also.
Code:
A_DY000990 
C_JCVI_990
A_EX091309 
C_EX091309
A_AM388645 
C_AM388645

sorry but i am really not good to make changes by myself in command

thanks a lot

Regards
Hi abdul, your output is different for me..
Because of your input file does not containing some outpus ( in your file that you send to my mail )
look Smilie
Code:
# grep C_RC_AM386941 abdulfile
# grep C_JCVI_12650 abdulfile
# grep C_JCVI_990 abdulfile

Code:
# grep 191394 abdulfile
A_DN191394      0       0       16.6694 23272   38590.3 18954.3 315.157
C_DN191394      0       0       11.1129 18776   34445.7 27008.5 13766.6

if you sent the full data(pls rar/zip the full text and send) to my mail i re-try to this Smilie
# 19  
Old 06-13-2012
Dear Ygemici,

I sent you the whole set.
Please find in your email.
Regards
# 20  
Old 06-13-2012
Quote:
Originally Posted by AAWT
Dear Ygemici,

I sent you the whole set.
Please find in your email.
Regards
ok i got it Smilie
i added a conditon to code..lets try this..(code still working Smilie )
Code:
# awk 'NR!=1{y=$1;gsub("[0-9]*","",y);split($1,s,"_");gsub("[^0-9]*","",$1);a[c++]=y FS $1}END{for(j=0;j<c;j++){
y=split(a[j],s,"[_ ]*");for(i=2;i<y;i++)yy=yy?yy FS s[i]:s[i];for(i=0;i<c;i++){x=split(a[i],ss,"[_ ]*");
for(k=2;k<x;k++)xx=xx?xx FS ss[k]:ss[k];;if(s[y]==ss[x]&&s[1]==v2&&ss[1]==v1&&xx==yy){
sub(" ","",a[j]);sub(" ","",a[i]);print a[j];print a[i];}xx=""}yy=""}}' abdulfull_file v1="C" v2="A"
A_JCVI_13208
C_JCVI_13208
A_JCVI_1353
C_JCVI_1353
....................
....................
....................

I m try to code at the now (for A_ and C_ uniqs)
When the test finishes (your file is 89110 lines so it takes a while), then i write the code..

---------- Post updated at 11:17 PM ---------- Previous update was at 02:12 PM ----------

and the others (uniqs)

2-) only A_ specific records
Code:
# awk 'NR!=1{y=$1;gsub("[0-9]*","",y);split($1,s,"_");gsub("[^0-9]*","",$1);a[c++]=y FS $1}END{for(j=0;j<c;j++){
y=split(a[j],s,"[_ ]*");if(s[1]==v2){ll=a[j];for(i=2;i<y;i++)yy=yy?yy FS s[i]:s[i];for(i=0;i<c;i++){x=split(a[i],ss,"[_ ]*");
for(k=2;k<x;k++)xx=xx?xx FS ss[k]:ss[k];;if(s[y]==ss[x]&&xx==yy){if(s[1]==v2&&ss[1]==v1){ac_uniq++;};
if(s[1]==v2&&ss[1]==v2){aa_uniq++}}xx="";};if(ac_uniq==0){gsub(FS,"",ll);b[ll];};yy="";;aa_uniq=ac_uniq=0}};for(uniqa in b)print uniqa}' 
abdulfile v1="C" v2="A" >uniq_A

3-) only C_ specific records
Code:
# awk 'NR!=1{y=$1;gsub("[0-9]*","",y);split($1,s,"_");gsub("[^0-9]*","",$1);a[c++]=y FS $1}END{for(j=0;j<c;j++){
y=split(a[j],s,"[_ ]*");if(s[1]==v1){ll=a[j];for(i=2;i<y;i++)yy=yy?yy FS s[i]:s[i];for(i=0;i<c;i++){x=split(a[i],ss,"[_ ]*");
for(k=2;k<x;k++)xx=xx?xx FS ss[k]:ss[k];;if(s[y]==ss[x]&&xx==yy){if(s[1]==v1&&ss[1]==v2){ac_uniq++;};
if(s[1]==v1&&ss[1]==v1){cc_uniq++}}xx="";};if(ac_uniq==0){gsub(FS,"",ll);b[ll];};yy="";ac_uniq=cc_uniq=0}};for(uniqc in b)print uniqc}' 
abdulfile v1="C" v2="A" >uniq_C


regards
ygemici
This User Gave Thanks to ygemici For This Post:
AAWT
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge and Sort tabular data from different text files

I have 42 text files; each containing up to 34 lines with following structure; file1 H-01 23 H-03 5 H-05 9 H-02 14 . . file2 H-01 17 H-02 43 H-04 7 H-05 8 H-03 7 . . file3 (6 Replies)
Discussion started by: Syeda Sumayya
6 Replies

2. UNIX for Dummies Questions & Answers

Unexplained text in data files

Has anyone ever encountered text from other files suddenly appearing in another data file that is not being used. There does not seem to be any reason for it, any thoughts would be useful. Thanks (14 Replies)
Discussion started by: SRoberts
14 Replies

3. Shell Programming and Scripting

Sorting indented text files

Hello, I'm trying to find a solution or a proper tool for the following job: I need to sort a text document with indented sections, so all levels of indentation are sorted independently for each section. Particularly, I need this for Cisco routers' running config files to compare them with... (2 Replies)
Discussion started by: kobel
2 Replies

4. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

5. Shell Programming and Scripting

How to write text file data to excel using UNIX shell script?

Hi All, I have the requirement in unix shell script. I want to write the "ls -ltr" command out put to excel file as below. Input :text file data : drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Nov 29 18:40 drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x... (10 Replies)
Discussion started by: Balasankar
10 Replies

6. Shell Programming and Scripting

Need help on inserting data from text file to excel using shell script

Hi, Please help me on this. I want to insert data from text file to excel using shell script nawk -v r=4 -v c=4 -v val=$a -F, 'BEGIN{OFS=","}; NR != r; NR == r {$c = val; print}' "file.csv" I used above one to insert $a value in 4th row, 4th column in an excel file.csv and it... (3 Replies)
Discussion started by: suman.frnz
3 Replies

7. Shell Programming and Scripting

Attaching two text files in two different sheet in same excel

Hi, My requirement is to get attach two different text file contents to two different sheets in same excelsheet. Also, is there any way we can name the tabs as desired ? Kindly assist. (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

8. Shell Programming and Scripting

sorting from several files for a specific data

Please assist: I have several files and all of the files have the same data format like following: All I need to get item next to "name" field and the "address" field from each file which has only 8 characters in "name" field. so the output should be: ams00ark(spcae)10.1.1.12... (3 Replies)
Discussion started by: amir07
3 Replies

9. Shell Programming and Scripting

PERL: Split Excel Workbook to Indiv Excel files

Hi, I am trying to find a way to read an excel work book with multiple worksheets. And write each worksheet into a new excel file using perl. My environment is Unix. For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create... (2 Replies)
Discussion started by: sandeep78
2 Replies

10. UNIX for Dummies Questions & Answers

sorting files with find command before sending to text file

i need help with my script.... i am suppose to grab files within a certain date range now i have done that already using the touch and find command (found them in other threads) touch -d "$date_start" ./tmp1 touch -d "$date_end" ./tmp2 find "$data_location" -maxdepth 1 -newer ./tmp1 !... (6 Replies)
Discussion started by: deking
6 Replies
Login or Register to Ask a Question