Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

extract data in a csv file based on a certain field.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting extract data in a csv file based on a certain field.
# 1  
Old 04-27-2011
extract data in a csv file based on a certain field.
I have a csv file that I need to extract some data from depending on another field after reading info from another text file.

The text file would say have 592560 in it.

The csv file may have some data like so

Code:
Field 1       Field2  Field3   Field4   Field5     Field6

20009756   1        2         1          1           592560

The information will always be in the same position.

If the data in the text file is on line 6 then output the data in line 2 to another text file. There would be many rows and the correct line 2 would need to be copied. There could be many entires in that text file and could reside in the csv file and would need to be added to the output file.
Last edited by pludi; 04-27-2011 at 07:09 PM..
# 2  
Old 04-27-2011
Quote:
If the data in the text file is on line 6 then output the data in line 2 to another text file.
You mean column?

Let me try, although I'm not sure I understood you completely:
Code:
$ cat data
Field 1       Field2  Field3   Field4   Field5     Field6

20009756   1        2         1          1           592560
20007654   2        4         45brt      ewfw        55552
20009756   X        2         1          1           592560
20009756   balh        2         1          1           592560
20007654   8.t        4         45brt      ewfw        55552
$ cat aux
592560
55552
$ awk 'NR==FNR{a[$1]=cnt++; next}{if($6 in a){print $2 > "output."$6;} }' aux data
$ cat output.55552 
2
8.t
$ cat output.592560 
1
X
balh

The above awk command will print the second column of a line whose sixth field matches an entry in aux, and print it into a file.
# 3  
Old 04-27-2011
Code:
$ $ cat data
Field 1       Field2  Field3   Field4   Field5     Field6
20009756   1        2         1          1           592560
20007654   2        4         45brt      ewfw        55552
20009756   X        2         1          1           592560
20009756   balh        2         1          1           592560
20007654   8.t        4         45brt      ewfw        55552


$ awk '{print >$6 ".txt"}' infile

$ cat 592560.txt
20009756   1        2         1          1           592560
20009756   X        2         1          1           592560
20009756   balh        2         1          1           592560

or only print column 2 :
Code:
$awk '{print $2 >$6 ".txt"}' infile

$ cat 592560.txt
1
X
balh

# 4  
Old 04-27-2011
This worked
Code:
awk 'NR==FNR{a[$1]=cnt++; next}{if($6 in a){print $2 > "output.txt";} }' aux data

This put all of the data into one file called output.txt. The other way created a separate file for each entry.

In any case that was a huge help rdcwayx.

There are some other things I need to try to do but this gets me far.

Thanks again!

---------- Post updated at 08:57 PM ---------- Previous update was at 08:32 PM ----------

update
Code:
awk -F't\' 'NR==FNR{a[$1]=cnt++; next}{if($6 in a){print $2 > "output.txt";} }' aux data

That was what worked. Needed the -F'\t' so awk knew it was tab delimited.

Have to pad $2 to account for 5 characters and name the output.txt the name of the data file.

Thanks again.

---------- Post updated at 09:46 PM ---------- Previous update was at 08:57 PM ----------

and this

Code:
awk -F'\t' 'NR==FNR{a[$1]=cnt++; next}{if($12 in a){printf "%05s\n", $2 > "output.txt";} }' aux data

does the padding of the $2 field.
Last edited by Franklin52; 04-28-2011 at 03:45 AM.. Reason: Code tags
# 5  
Old 05-16-2011
Anyone know how to pad the output.txt so that it has 14 characters. Padding that with 0's

Code:
awk -F'\t' 'NR==FNR{a[$1]=cnt++; next}{if($12 in a){printf "%05s\n", $2 > $1".pull";} }' /aiw/aiw1/pullfiles/*.txt *.dpf

Thanks in advance!
# 6  
Old 05-17-2011
I figured out a way to do what I needed in padding 0's.

I do have another question

I need to search in all txt files in a location. But awk only searches the first one and stops.

How do I search *.txt in a location in awk and still have the same results.

Any help would be appreciated.
# 7  
Old 05-17-2011
Quote:
But awk only searches the first one and stops.
Of course it does:
Code:
awk -F'\t' 'NR==FNR{a[$1]=cnt++; next}{if($12 in a){printf "%05s\n", $2 > $1".pull";} }' /aiw/aiw1/pullfiles/*.txt *.dpf

The 'NR==FNR' bit is what tells awk to remember pattern ('a[$1]=cnt++') only from the first file.

You could do:
Code:
awk -F'\t' 'FILENAME~".txt$"{a[$1]=cnt++; next}{if($12 in a){printf "%05s\n", $2 > $1".pull";} }' /aiw/aiw1/pullfiles/*.txt *.dpf

Which instructs awk to apply the first action to *.txt files.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to extract data from csv file

Hi everyone, I have a csv file which has data with different heading and column names as below. Static Data Ingested ,,,,,,,,,,,,Known Explained Rejections Column_1,column_2,Column_3,Column_4,,Column_6,Column_7,,% Column_8,,Column_9 ,Column_10 ,... (14 Replies)
Discussion started by: Vivekit82
14 Replies

2. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 7 columns having values say column 1,column 2.....column 7 as below along with their values. Name, Address,... (7 Replies)
Discussion started by: Vivekit82
7 Replies

3. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file based on certain conditions

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (1 Reply)
Discussion started by: Vivekit82
1 Replies

4. Shell Programming and Scripting

Extract data from XML file and write in CSV file

Hi friend i have input as following XML file <?xml version="1.0"?> <Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.02"> <BkToCstmrDbtCdtNtfctn> <GrpHdr><MsgId>LBP-RDJ-TE000000-130042430010001001</MsgId><CreDtTm>2013-01-04T03:21:30</CreDtTm></GrpHdr>... (3 Replies)
Discussion started by: mohan sharma
3 Replies

5. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

6. Shell Programming and Scripting

Text file to CSV with field data separated by blank lines

Hello, I have some data in a text file where fields are separated by blank lines. There are only 6 fields however some fields have several lines of data as I will explain. Also data in a particular field is not consistently the same size but does end on a blank line. The first field start with... (6 Replies)
Discussion started by: vestport
6 Replies

7. Shell Programming and Scripting

Extract file records based on some field conditions

Hello Friends, I have a file(InputFile.csv) with the following columns(the columns are pipe-delimited): ColA|ColB|ColC|ColD|ColE|ColF Now for this file, I have to get those records which fulfil the following condition: If "ColB" is NOT NULL and "ColD" has values one of the following... (9 Replies)
Discussion started by: mehimadri
9 Replies

8. Shell Programming and Scripting

How to extract data from csv file

Hello everybody, Here is my problem, I don't know anything about shell programming and my boss is actually asking me to develop a shell script in order to get values in a csv file from a specific date. Here is a sample of the csv file : Date;Enchaînement;Titre;Libellé ;calendrier;Heure début;Heure... (11 Replies)
Discussion started by: freyr
11 Replies

9. Shell Programming and Scripting

Extract data from an XML file & write into a CSV file

Hi All, I am having an XML tag like: <detail sim_ser_no_1="898407109001000090" imsi_1="452070001000090"> <security>ADM1=????</security> <security>PIN1=????</security> <security>PIN2=????</security> ... (2 Replies)
Discussion started by: ss_ss
2 Replies

10. Shell Programming and Scripting

Extract data into file with specific field specs

:confused: I have a tab delimited file that I need to extract data from and into a file with specific field specs. Each field has to be a certain amount of characters. So, the name field (from delimited file) might have only 15 characters but needs to be 25 (in new file) so I need to insert spaces... (5 Replies)
Discussion started by: criddel
5 Replies
Login or Register to Ask a Question