How to output selective delimited data to a new file?

Login or Register to Ask a Question and Join Our Community

How to output selective delimited data to a new file?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to output selective delimited data to a new file?

04-14-2013

Registered User

7, 0

Join Date: Apr 2013

Last Activity: 14 April 2014, 4:32 AM EDT

Posts: 7

Thanks Given: 3

Thanked 0 Times in 0 Posts

How to output selective delimited data to a new file?

I have the following file which at times can be quite large so can be difficult to read, I need a script that just extracts particular delimited data and outputs to alternate file

Code:

$cat fix1.log

FIX4.4|0=12|55=LIT.L|48=123456|32=5|52=20111107-10:52:22.128|38=100|10=200|
 FIX4.4|0=12|55=LIT.L|48=123456|32=2|52=20111107-10:52:23.133|38=100|10=200|
FIX4.4|0=12|55=LIT.L|48=123456|32=5|52=20111107-10:53:22.128|38=100|10=200|
FIX4.4|0=12|55=LIT.L|48=123456|32=11|52=20111107-10:52:22.145|38=100|10=200|
FIX4.4|0=12|55=LIT.L|48=123456|32=55|52=20111107-10:52:24.128|38=100|10=200|

So for example if I was only interested in the data within delimiters 0= *, 32=* & 52=* I would want the output to look like this :

Code:

|0=12  |32=5   |52=20111107-10:52:22.128
|0=12  |32=2   |52=20111107-10:52:23.133
|0=12  |32=11 |52=20111107-10:52:22.145
|0=12  |32=55 |52=20111107-10:52:24.128
|0=12  |32=5   |52=20111107-10:53:22.128

Then if perhaps I wanted to manipulate this output further and just calculate the sum values of column '32= ' added only

Assistance appreciated !!

Last edited by Buddyluv; 04-14-2013 at 06:51 PM.. Reason: Initial Request was unclear

Buddyluv

View Public Profile for Buddyluv

Find all posts by Buddyluv

04-14-2013

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

Not clear. Your written request and your output are not consistent.
Where's you second and third input lines? They fulfill the requirements (0=, 32=, 52=).
Where do your output lines 2 - 5 come from? They're not in the input file.
Should only fixed columns (here: 2 - 5 - 6) be considered, or would $6 containing "32=" also be considered fulfilled?

RudiC

View Public Profile for RudiC

Find all posts by RudiC

04-14-2013

Registered User

7, 0

Join Date: Apr 2013

Last Activity: 14 April 2014, 4:32 AM EDT

Posts: 7

Thanks Given: 3

Thanked 0 Times in 0 Posts

Ok I've tried to clarify matching input ---> output however I should also mention that although the input will always consist these data fields they are not fixed so could appear anywhere on the line.

Buddyluv

View Public Profile for Buddyluv

Find all posts by Buddyluv

04-14-2013

Moderator

12,296, 3,792

Join Date: Nov 2008

Last Activity: 1 January 2021, 1:47 AM EST

Location: Amsterdam

Posts: 12,296

Thanks Given: 679

Thanked 3,792 Times in 3,282 Posts

Try:

Code:

awk -F\| '{for(i=1; i<=NF; i++) if($i~/^(0|32|52)=/) printf "|%s ",$i; print x}' file

or

Code:

grep -Eo '\|(0|32|52)=[^|]*' file | paste - - -

Last edited by Scrutinizer; 04-14-2013 at 07:25 PM..

Scrutinizer

View Public Profile for Scrutinizer

Find all posts by Scrutinizer

04-14-2013

Registered User

858, 184

Join Date: Mar 2013

Last Activity: 12 May 2013, 11:33 PM EDT

Posts: 858

Thanks Given: 18

Thanked 184 Times in 179 Posts

Here is one way to do it:

Code:

$ sed -n "s/.*\(|0=[^|]*\).*\(|32=[^|]*\).*\(|52=[^|]*\).*/\1\2\3/p" fix1.log
|0=12|32=5|52=20111107-10:52:22.128
|0=12|32=2|52=20111107-10:52:23.133
|0=12|32=5|52=20111107-10:53:22.128
|0=12|32=11|52=20111107-10:52:22.145
|0=12|32=55|52=20111107-10:52:24.128

hanson44

View Public Profile for hanson44

Find all posts by hanson44

04-14-2013

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

Scrutinizer's scripts assume that every input line will contain all three desired fields. (That wasn't true in the original posting, but is true in the updated sample input.)

hanson44's script assumes that the three desired fields will always appear in the same order. (That is also true in the sample input, but is not part of the specified behavior.)

None of their scripts provide a sum of the values in the "32=value" fields. And, none of the scripts provided (nor my script below) try to match the seemingly random number of spaces at the ends of the first and second fields.

The following awk script will only process lines that contain all three requested fields but doesn't care about the order in which they appear. It does not add any spaces to the input. It does add a final line to the output giving the sum of the values in the "32=" columns.

Code:

awk -F '[|=]' '
/[|]|0=/ && /[|]32=/ && /[|]52=/ {
        for(i = 2; i <= NF; i  += 2) {
                if($i == 0) z = $(i + 1)
                else if($i == 32) {
                        t = $(i + 1)
                        ts += t
                } else if($i == 52) f = $(i + 1)
        }
        printf("|0=%s|32=%s|52=%s\n", z, t, f);
}
END {   printf("||sum=%d|\n", ts)}' fix1.log

As always, if you're using a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of awk. With the current sample input, the output produced is:

Code:

|0=12|32=5|52=20111107-10:52:22.128
|0=12|32=2|52=20111107-10:52:23.133
|0=12|32=5|52=20111107-10:53:22.128
|0=12|32=11|52=20111107-10:52:22.145
|0=12|32=55|52=20111107-10:52:24.128
||sum=78|

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

04-15-2013

Registered User

1,040, 213

Join Date: Dec 2010

Last Activity: 20 September 2014, 2:08 AM EDT

Posts: 1,040

Thanks Given: 62

Thanked 213 Times in 203 Posts

Simple, but works for data given.

Code:

awk -F\| '{print $2,$5,$6}{split($5,s,"=");sum+=s[2]} END {print "sum(" s[1] ")=" sum}' OFS="|" fix1.log
0=12|32=5|52=20111107-10:52:22.128
0=12|32=2|52=20111107-10:52:23.133
0=12|32=5|52=20111107-10:53:22.128
0=12|32=11|52=20111107-10:52:22.145
0=12|32=55|52=20111107-10:52:24.128
sum(32)=78

Jotne

View Public Profile for Jotne

Find all posts by Jotne

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET...

2. Shell Programming and Scripting

Output file with <Tab> or <Space> Delimited

Input file: xyz,pqrs.lmno,NA,NA,NA,NA,NA,NA,NA abcd,pqrs.xyz,NA,NA,NA,NA,NA,NA,NA Expected Output: xyz pqrs.lmno NA NA NA NA NA NA NA abcd pqrs.xyz NA NA NA NA NA NA NA Command Tried so far: awk -F"," 'BEGIN{OFS=" ";} {print}' $File_Path/File_Name.csv Issue:...

3. Shell Programming and Scripting

How to read data from tab delimited file after a specific position?

Hi Experts, I have a tab deliminated file as below myfile.txt Local Group Memberships *Administrators *Guests I need data in below format starting from 4th position. myfile1.txt Administrators Guests the above one is just an example and there could...

4. Shell Programming and Scripting

Append data to first column delimited file

Hi, I have a data like Input: 12||34|56|78 Output: XYZ|12||34|56|78 I tried like this , but it puts it on another line awk -F "|" ' BEGIN {"XYZ"} {print $0} 'file Any quick suggessitons in sed/awk ? am using HP-UX

5. Shell Programming and Scripting

capturing selective data from a vcd file

Hi, This is a vcd file.A vcd file may have 'n' modules. 1) I need to capture the data in bold,i.e. the module names (shown in bold) 2) Also i need to capture the data inside each individual module,say for tst_bench_top ,i need to capture data from line 4 to line 20 ... I just want one...

6. Shell Programming and Scripting

Moving a column across a delimited data file

Hi, I am trying to move a column from one position to another position in a delimited file. The positions are dynamic in nature and are available by environmental variables. Also the file can have n number of columns. Example: Initial Column Position=1 Final Column Position=3 Delimiter='|' ...

7. Shell Programming and Scripting

using awk to substitute data in a column delimited text file

using awk to substitute data in a column delimited text file hello i would like to use awk to do the following calculation from the following snippet. input file C;2390 ;CV BOUILLOTTE 2L 2FACES NERVUREES ;1.00 ;3552612239004;13417 ;25 ;50 ; 12;50000 ; ; ...

8. Shell Programming and Scripting

Remote command output to file comma delimited

All, I am trying to run a command that will get the serial number, model, and hostname of a server and then output the 3 fields comma delimited. Then a new line to separate the 3 fields for each host. So, I run a for loop that reads in the list of hosts in a file and then runs these sudo...

9. Shell Programming and Scripting

Get data from delimited file

Hi, I am quite new to shell scripting in Linux. Please help me in solving the given problem. I have file called Data.txt File "Data.txt" looks like India|9990000|City England|888800|London Nigeria|9887766|uganda I need to get the data after first pipe till second pipe into file...

10. Shell Programming and Scripting

Check whether a given file is in ASCII format and data is tab-delimited

Hi All, Please help me out with a script which checks whether a given file say abc.txt is in ASCII format and data is tab-delimited. If the condition doesn't satisfy then it should generate error code "100" for file not in ASCII format and "105" if it is not in tab-delimited format. If the...

Login or Register to Ask a Question