Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

urgent<parsing data from a excel file>

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting urgent<parsing data from a excel file>
# 1  
Old 10-31-2009
urgent<parsing data from a excel file>
Hi all,
I wud like to get ur assistance in retrieving lines containing l1.My excel dataset contains around 8000 lines.I converted it into a text tab delimiter file and got the lines containing l1,My output is a list of lines containing l1 saved in a outfile.Some of d lines from my outfile s shown below;

Code:
"NM_001354","chr10","-",5021965,5050207,"Y","LINE,LTR,","L1,MaLR,","+,-,","5039541,5050000,","5039749,5050345,","5'UTR,5'UTR,"
"NM_001494","chr10","-",5847192,5895379,"N","LINE,","L1,","+,","5850340,","5850974,","5'UTR,"
"NM_012311","chr10","-",7837373,7869950,"N","LINE,","L1,","+,","7837122,","7837639,","3'UTR,"
"NM_001033855","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,","Alu,L1,Alu,Alu,L1,","-,-,-,-,-,","14988880,14989317,14989863,14990031,14990305,","14989154,14989863,14990030,14990305,14990395,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,"
"NM_001033855","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,","Alu,L1,Alu,Alu,L1,","-,-,-,-,-,","14988880,14989317,14989863,14990031,14990305,","14989154,14989863,14990030,14990305,14990395,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,"
"NM_001033857","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,SINE,","Alu,L1,Alu,Alu,L1,MIR,","-,-,-,-,-,+,","14988880,14989317,14989863,14990031,14990305,15029509,","14989154,14989863,14990030,14990305,14990395,15029704,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,5'UTR,""NM_001354","chr10","-",5021965,5050207,"Y","LINE,LTR,","L1,MaLR,","+,-,","5039541,5050000,","5039749,5050345,","5'UTR,5'UTR,"
"NM_001494","chr10","-",5847192,5895379,"N","LINE,","L1,","+,","5850340,","5850974,","5'UTR,"
"NM_012311","chr10","-",7837373,7869950,"N","LINE,","L1,","+,","7837122,","7837639,","3'UTR,"
"NM_001033855","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,","Alu,L1,Alu,Alu,L1,","-,-,-,-,-,","14988880,14989317,14989863,14990031,14990305,","14989154,14989863,14990030,14990305,14990395,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,"
"NM_001033855","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,","Alu,L1,Alu,Alu,L1,","-,-,-,-,-,","14988880,14989317,14989863,14990031,14990305,","14989154,14989863,14990030,14990305,14990395,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,"
"NM_001033857","chr10","-",14988878,15036100,"N","SINE,LINE,SINE,SINE,LINE,SINE,","Alu,L1,Alu,Alu,L1,MIR,","-,-,-,-,-,+,","14988880,14989317,14989863,14990031,14990305,15029509,","14989154,14989863,14990030,14990305,14990395,15029704,","3'UTR,3'UTR,3'UTR,3'UTR,3'UTR,5'UTR,"

You may notice some lines containing only "L1," and some lines containing "L1,MaLR," and so on.I would like to retrieve the lines containing only "L1,"into a separate outfile and the other lines into another outfile.I need a regular expression for matching this.

Thank you,

Regards,
Sayee.

---------- Post updated at 10:13 PM ---------- Previous update was at 10:09 PM ----------

Sorry friends,
I need to parse the data using perl!!!
Last edited by Franklin52; 10-31-2009 at 12:40 PM.. Reason: Please use code tags!!
# 2  
Old 10-31-2009
Negative lookahead assertion can help you.

Code:
$ echo 'L1' | perl -ne 'print if /L1(?!MaLR)/'
L1
$ echo 'L1,MaLR' | perl -ne 'print if /L1(?!,MaLR)/'

# 3  
Old 10-31-2009
Or try awk anyway:
Code:
awk -F'","' '$5 == "L1,"' infile > outfile

Code:
$> cat outfile
"NM_001494","chr10","-",5847192,5895379,"N","LINE,","L1,","+,","5850340,","5850974,","5'UTR,"
"NM_012311","chr10","-",7837373,7869950,"N","LINE,","L1,","+,","7837122,","7837639,","3'UTR,"
"NM_001494","chr10","-",5847192,5895379,"N","LINE,","L1,","+,","5850340,","5850974,","5'UTR,"
"NM_012311","chr10","-",7837373,7869950,"N","LINE,","L1,","+,","7837122,","7837639,","3'UTR,"

Last edited by Scrutinizer; 10-31-2009 at 03:29 PM..
# 4  
Old 10-31-2009
@thegeek---->is it possible to use this coz am concentrating only on L1 and i hu gotta large dataset which contains a no of Mal1 lik elements.hope u get me!!!i need a pattern match which shud exactly match the lines with "L1," read in an outfile and d remaining lines read in another outfile.I will show u my script which check i used to get the output shown above;

Code:
#!/usr/bin/perl -w

use strict;
use warnings;

# declaration
my $file = "dataset1.txt";
my $pattern = "L1";

#opens the file using file handle
open(IN,$file) or die "Cannot open file \"$file\n";

print "sayee";
#reading the file into array
while(my @lines = <IN>)
{
#going through each line and checking for pattern
foreach my $lines(@lines)
   {
  while($lines =~/$pattern/g)
     {
 
     #printing to out file
     open OUT, ">>outsai.txt" or die ;
     print OUT $lines;
   
      }
   }
}

Last edited by Franklin52; 10-31-2009 at 12:41 PM.. Reason: Please use code tags!!
# 5  
Old 10-31-2009
Code:
$ echo 'L1' | perl -ne 'print if /L1(?!MaLR)/'
L1
$ echo 'L1,MaLR' | perl -ne 'print if /L1(?!,MaLR)/'

Sorry, am not able to get what you exactly mean. But the thing is clear that when you want only the data without L1,MaLR and only MaLR use the above regex ?!

What is the confusion ?

If you have doubt still, try with sample data and tell us what your problem.
# 6  
Old 10-31-2009
Not clear what you want.
Always post the input and the output also.

Code:
 
sed -n '/"L1,"/p' myfile            # L1 only. Simple straight
sed -n '/"L1,[[:alpha:]]/p' myfile  # L1 with MaLR. Regexp
sed -n '/"L1,[[:alnum:]]/p' myfile  # L1 with MaLR99. Regexp.
sed -n '/"L1,[^"]/p' myfile           # L1 with any thing except ". Regexp.

If you want, you can directly write to two diff. files in one shot with sed.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Formatting data to put it in the excel file

Hello, I have a file with the below contents : Policy Name: Backup_bkp Policy Type: Catalog_bkp Active: yes Effective date: 08/07/2013 02:02:12 Mult. Data Streams: no Client Encrypt: no Checkpoint: no Policy Priority: ... (11 Replies)
Discussion started by: rahul2662
11 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. Shell Programming and Scripting

Data formatting in CSV file to EXCEL

Hello friends I want to convert an csv file on unix (which is generated by a ETL application) to a formatted excel sheet like .I have roughly like 28 columns 1)All numbers need to be stored as numbers with leading zeros-like format as text for this column to preserve leading zeroes e.g... (6 Replies)
Discussion started by: etldev
6 Replies

4. UNIX for Advanced & Expert Users

put data in excel file using shell script

Hi. I wish to add data in a specific excel file on daily basis.However the currect dat's data should always come on top i.e for example should always occupy cell A7,B7,C7 .. and the data of day before which was earlier on 7th row of each coloumn should move to 8th row..data on 8th row should... (1 Reply)
Discussion started by: kanus
1 Replies

5. Shell Programming and Scripting

store the table data in excel file

Hello - I have a below table and i want to extract the data into excel sheet and send to different location. Here is the table structure... SQL> desc t_i1_exportdocs Name Null? Type ----------------------------------------- --------... (11 Replies)
Discussion started by: govindts
11 Replies

6. Shell Programming and Scripting

Copying data from excel file

Hii friends, I am a newbie to unix/shell scripting and got stuck in implementing a functionality.Dear experts,kindly spare some time to bring me out of dark pit :confused:.. My requirement is somewhat wierd,let me explain what i have and what i need to do... 1) there are several excel... (1 Reply)
Discussion started by: 5ahen
1 Replies

7. Shell Programming and Scripting

how to copy data to to excel file

Hi, Can any one tell me how to copy data using shell script to a excel file from text file to other columns of excel file,leaving first column unaffected i.e it should not overwrite data in first column. Say my text file data is: 15-dec-2008 15-dec-2009 16-dec-2008 16-dec-2009 ... (7 Replies)
Discussion started by: tucs_123
7 Replies

8. Shell Programming and Scripting

Help on email data file as excel from unix!!

Hi, I need to email a data in excel sheet from unix using shell scripting.I could able to generate the data file with tab delimiter with extension .xls could able to email it. The problem is when a coulmn with 16 digit number is exported, it is showing in scientific format. Any help in... (1 Reply)
Discussion started by: sparan_peddu
1 Replies
Login or Register to Ask a Question