Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Retrieving data from 65th col (of each line) ?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Retrieving data from 65th col (of each line) ?
# 1  
Old 07-05-2012
Retrieving data from 65th col (of each line) ?
Hello Friends,
I am in situation where I have to note down few SQL queries from specific hexdump format. Here is an example (the query text starts at 65th character on each line)

----------------------
Code:
0x000007FEB0E701C0 : 7365 6C65 6374 2063 7573 746E 6F2C 2020    select custno,  
0x000007FEB0E701D0 : 696E 765F 7472 6E73 2E74 7261 6E6E 6F2C    inv_trns.tranno,
0x000007FEB0E701E0 : 2069 6E76 5F74 726E 732E 7479 7065 2C20     inv_trns.type, 
0x000007FEB0E701F0 : 696E 765F 7472 6E73 2E72 6566 6572 656E    inv_trns.referen
0x000007FEB0E70200 : 6365 2C20 696E 765F 7472 6E73 2E70 6172    ce, inv_trns.par
0x000007FEB0E70210 : 742C 2020 2063 6173 6520 2069 6E76 5F74    t,   case  inv_t
0x000007FEB0E70220 : 726E 732E 636F 6E64 6974 696F 6E20 2077    rns.condition  w
0x000007FEB0E70230 : 6865 6E20 274E 2720 7468 656E 2027 4E65    hen 'N' then 'Ne
0x000007FEB0E70240 : 7727 2020 2020 7768 656E 2027 5227 2074    w'    when 'R' t
0x000007FEB0E70250 : 6865 6E20 2752 6566 2720 2020 2077 6865    hen 'Ref'    whe
0x000007FEB0E70260 : 6E20 2747 2720 7468 656E 2027 5265 6627    n 'G' then 'Ref'
0x000007FEB0E70270 : 2020 2020 7768 656E 2027 4427 2074 6865        when 'D' the
0x000007FEB0E70280 : 6E20 2744 6566 2720 2020 2077 6865 6E20    n 'Def'    when 
0x000007FEB0E70290 : 2753 2720 7468 656E 2027 5363 7261 7027    'S' then 'Scrap'
0x000007FEB0E702A0 : 2020 2020 7768 656E 2027 5427 2074 6865        when 'T' the
0x000007FEB0E702B0 : 6E20 2754 7261 6465 696E 2720 2020 2065    n 'Tradein'    e
0x000007FEB0E702C0 : 6C73 6520 2069 6E76 5F74 726E 732E 636F    lse  inv_trns.co
0x000007FEB0E702D0 : 6E64 6974 696F 6E20 2065 6E64 2061 7320    ndition  end as 
0x000007FEB0E702E0 : 636F 6E64 6974 696F 6E2C 2020 2028 7365    condition,   (se
0x000007FEB0E702F0 : 6C65 6374 2064 6973 7469 6E63 7420 6465    lect distinct de
0x000007FEB0E70300 : 7363 5F65 6E67 2020 6672 6F6D 2070 726F    sc_eng  from pro
0x000007FEB0E70310 : 645F 6D61 7320 7768 6572 6520 7072 6F64    d_mas where prod
0x000007FEB0E70320 : 7563 7420 3D20 696E 765F 7472 6E73 2E70    uct = inv_trns.p
0x000007FEB0E70330 : 6172 7429 2061 7320 6465 7363 5F65 6E67    art) as desc_eng
0x000007FEB0E70340 : 2C20 2020 2873 656C 6563 7420 6469 7374    ,   (select dist
0x000007FEB0E70350 : 696E 6374 2070 726F 645F 7479 7065 2066    inct prod_type f
0x000007FEB0E70360 : 726F 6D20 7072 6F64 5F6D 6173 2077 6865    rom prod_mas whe
0x000007FEB0E70370 : 7265 2070 726F 6475 6374 203D 2069 6E76    re product = inv
0x000007FEB0E70380 : 5F74 726E 732E 7061 7274 2920 6173 2070    _trns.part) as p
0x000007FEB0E70390 : 726F 645F 7479 7065 2C20 2020 696E 765F    rod_type,   inv_
0x000007FEB0E703A0 : 7472 6E73 2E71 7561 6E74 6974 792C 2063    trns.quantity, c
0x000007FEB0E703B0 : 6861 7228 696E 765F 7472 6E73 2E74 6461    har(inv_trns.tda
0x000007FEB0E703C0 : 7465 2C55 5341 2920 6173 2074 6461 7465    te,USA) as tdate
0x000007FEB0E703D0 : 2C20 2020 6361 7365 2069 6E76 5F74 726E    ,   case inv_trn
0x000007FEB0E703E0 : 732E 7479 7065 2020 7768 656E 2027 4927    s.type  when 'I'
0x000007FEB0E703F0 : 2074 6865 6E20 2749 6E73 7461 6C6C 2720     then 'Install' 
0x000007FEB0E70400 : 2020 2077 6865 6E20 274D 2720 7468 656E       when 'M' then
0x000007FEB0E70410 : 2027 4D61 6327 2020 656E 6420 6173 2074     'Mac'  end as t
0x000007FEB0E70420 : 7970 6564 6573 632C 2020 2063 6173 6520    ypedesc,   case 
0x000007FEB0E70430 : 696E 765F 7472 6E73 2E74 7261 6E73 6163    inv_trns.transac
0x000007FEB0E70440 : 746E 2020 7768 656E 2027 4927 2074 6865    tn  when 'I' the
0x000007FEB0E70450 : 6E20 2749 7373 7565 2720 2020 2077 6865    n 'Issue'    whe
0x000007FEB0E70460 : 6E20 2752 2720 7468 656E 2027 5265 6364    n 'R' then 'Recd
0x000007FEB0E70470 : 2720 2065 6E64 2061 7320 7472 616E 7361    '  end as transa
0x000007FEB0E70480 : 6374 6E2C 2020 2069 6E76 5F74 726E 732E    ctn,   inv_trns.
0x000007FEB0E70490 : 7464 6174 6520 6173 2074 6461 7465 322C    tdate as tdate2,
0x000007FEB0E704A0 : 2020 2020 2873 656C 6563 7420 636F 756E        (select coun
0x000007FEB0E704B0 : 7428 2A29 2066 726F 6D20 7072 6F64 5F70    t(*) from prod_p
0x000007FEB0E704C0 : 6B67 2077 6865 7265 2070 726F 6475 6374    kg where product
0x000007FEB0E704D0 : 203D 2069 6E76 5F74 726E 732E 7061 7274     = inv_trns.part
0x000007FEB0E704E0 : 2920 6173 2070 6B67 636F 756E 7420 2066    ) as pkgcount  f
0x000007FEB0E704F0 : 726F 6D20 765F 696E 765F 7472 6E73 2069    rom v_inv_trns i
0x000007FEB0E70500 : 6E76 5F74 726E 7320 2020 2057 4845 5245    nv_trns    WHERE
0x000007FEB0E70510 : 2069 6E76 5F74 726E 732E 4355 5354 4E4F     inv_trns.CUSTNO
0x000007FEB0E70520 : 203D 2734 3832 3732 3127 2061 6E64 2069     ='482721' and i
0x000007FEB0E70530 : 6E76 5F74 726E 732E 7479 7065 203D 2753    nv_trns.type ='S
0x000007FEB0E70540 : 2720 616E 6420 7265 6665 7265 6E63 6520    ' and reference 
0x000007FEB0E70550 : 3D20 2757 3435 3035 3827 206F 7264 6572    = 'W45058' order
0x000007FEB0E70560 : 2062 7920 7464 6174 6532 2020 2064 6573     by tdate2   des
0x000007FEB0E70570 : 6320 2066 6574 6368 2066 6972 7374 2032    c  fetch first 2
0x000007FEB0E70580 : 3030 3020 726F 7773 206F 6E6C 79           000 rows only

----------------------


Can someone show me how to write a script (any) which can help me to retrieve the query?

- Jenny
Last edited by Scrutinizer; 07-05-2012 at 03:39 AM.. Reason: code tags
# 2  
Old 07-05-2012
Code:
cut -c 65-  inputfile | tr -d '\n' > myfile.sql

# 3  
Old 07-05-2012
for the last line we need to cut the data before 65
Code:
 
awk '{for(i=11;i<=NF;i++){if(i==11){printf("%s ",$i)}else {printf(" %s",$i)}}}' filename.txt

# 4  
Old 07-05-2012
@ jim mcnamara : its not working

Code:
$ cat sql.txt
0x000007FEB0E701C0 : 7365 6C65 6374 2063 7573 746E 6F2C 2020 select custno,
0x000007FEB0E701D0 : 696E 765F 7472 6E73 2E74 7261 6E6E 6F2C inv_trns.tranno,
0x000007FEB0E701E0 : 2069 6E76 5F74 726E 732E 7479 7065 2C20 inv_trns.type,
0x000007FEB0E701F0 : 696E 765F 7472 6E73 2E72 6566 6572 656E inv_trns.referen
0x000007FEB0E70200 : 6365 2C20 696E 765F 7472 6E73 2E70 6172 ce, inv_trns.par
0x000007FEB0E70210 : 742C 2020 2063 6173 6520 2069 6E76 5F74 t, case inv_t
0x000007FEB0E70220 : 726E 732E 636F 6E64 6974 696F 6E20 2077 rns.condition w
0x000007FEB0E70230 : 6865 6E20 274E 2720 7468 656E 2027 4E65 hen 'N' then 'Ne
0x000007FEB0E70240 : 7727 2020 2020 7768 656E 2027 5227 2074 w' when 'R' t
0x000007FEB0E70250 : 6865 6E20 2752 6566 2720 2020 2077 6865 hen 'Ref' whe
0x000007FEB0E70260 : 6E20 2747 2720 7468 656E 2027 5265 6627 n 'G' then 'Ref'
0x000007FEB0E70270 : 2020 2020 7768 656E 2027 4427 2074 6865 when 'D' the
0x000007FEB0E70280 : 6E20 2744 6566 2720 2020 2077 6865 6E20 n 'Def' when
0x000007FEB0E70290 : 2753 2720 7468 656E 2027 5363 7261 7027 'S' then 'Scrap'
0x000007FEB0E702A0 : 2020 2020 7768 656E 2027 5427 2074 6865 when 'T' the
0x000007FEB0E702B0 : 6E20 2754 7261 6465 696E 2720 2020 2065 n 'Tradein' e
0x000007FEB0E702C0 : 6C73 6520 2069 6E76 5F74 726E 732E 636F lse inv_trns.co
0x000007FEB0E702D0 : 6E64 6974 696F 6E20 2065 6E64 2061 7320 ndition end as
0x000007FEB0E702E0 : 636F 6E64 6974 696F 6E2C 2020 2028 7365 condition, (se
0x000007FEB0E702F0 : 6C65 6374 2064 6973 7469 6E63 7420 6465 lect distinct de
0x000007FEB0E70300 : 7363 5F65 6E67 2020 6672 6F6D 2070 726F sc_eng from pro
0x000007FEB0E70310 : 645F 6D61 7320 7768 6572 6520 7072 6F64 d_mas where prod
0x000007FEB0E70320 : 7563 7420 3D20 696E 765F 7472 6E73 2E70 uct = inv_trns.p
0x000007FEB0E70330 : 6172 7429 2061 7320 6465 7363 5F65 6E67 art) as desc_eng
0x000007FEB0E70340 : 2C20 2020 2873 656C 6563 7420 6469 7374 , (select dist
0x000007FEB0E70350 : 696E 6374 2070 726F 645F 7479 7065 2066 inct prod_type f
0x000007FEB0E70360 : 726F 6D20 7072 6F64 5F6D 6173 2077 6865 rom prod_mas whe
0x000007FEB0E70370 : 7265 2070 726F 6475 6374 203D 2069 6E76 re product = inv
0x000007FEB0E70380 : 5F74 726E 732E 7061 7274 2920 6173 2070 _trns.part) as p
0x000007FEB0E70390 : 726F 645F 7479 7065 2C20 2020 696E 765F rod_type, inv_
0x000007FEB0E703A0 : 7472 6E73 2E71 7561 6E74 6974 792C 2063 trns.quantity, c
0x000007FEB0E703B0 : 6861 7228 696E 765F 7472 6E73 2E74 6461 har(inv_trns.tda
0x000007FEB0E703C0 : 7465 2C55 5341 2920 6173 2074 6461 7465 te,USA) as tdate
0x000007FEB0E703D0 : 2C20 2020 6361 7365 2069 6E76 5F74 726E , case inv_trn
0x000007FEB0E703E0 : 732E 7479 7065 2020 7768 656E 2027 4927 s.type when 'I'
0x000007FEB0E703F0 : 2074 6865 6E20 2749 6E73 7461 6C6C 2720 then 'Install'
0x000007FEB0E70400 : 2020 2077 6865 6E20 274D 2720 7468 656E when 'M' then
0x000007FEB0E70410 : 2027 4D61 6327 2020 656E 6420 6173 2074 'Mac' end as t
0x000007FEB0E70420 : 7970 6564 6573 632C 2020 2063 6173 6520 ypedesc, case
0x000007FEB0E70430 : 696E 765F 7472 6E73 2E74 7261 6E73 6163 inv_trns.transac
0x000007FEB0E70440 : 746E 2020 7768 656E 2027 4927 2074 6865 tn when 'I' the
0x000007FEB0E70450 : 6E20 2749 7373 7565 2720 2020 2077 6865 n 'Issue' whe
0x000007FEB0E70460 : 6E20 2752 2720 7468 656E 2027 5265 6364 n 'R' then 'Recd
0x000007FEB0E70470 : 2720 2065 6E64 2061 7320 7472 616E 7361 ' end as transa
0x000007FEB0E70480 : 6374 6E2C 2020 2069 6E76 5F74 726E 732E ctn, inv_trns.
0x000007FEB0E70490 : 7464 6174 6520 6173 2074 6461 7465 322C tdate as tdate2,
0x000007FEB0E704A0 : 2020 2020 2873 656C 6563 7420 636F 756E (select coun
0x000007FEB0E704B0 : 7428 2A29 2066 726F 6D20 7072 6F64 5F70 t(*) from prod_p
0x000007FEB0E704C0 : 6B67 2077 6865 7265 2070 726F 6475 6374 kg where product
0x000007FEB0E704D0 : 203D 2069 6E76 5F74 726E 732E 7061 7274 = inv_trns.part
0x000007FEB0E704E0 : 2920 6173 2070 6B67 636F 756E 7420 2066 ) as pkgcount f
0x000007FEB0E704F0 : 726F 6D20 765F 696E 765F 7472 6E73 2069 rom v_inv_trns i
0x000007FEB0E70500 : 6E76 5F74 726E 7320 2020 2057 4845 5245 nv_trns WHERE
0x000007FEB0E70510 : 2069 6E76 5F74 726E 732E 4355 5354 4E4F inv_trns.CUSTNO
0x000007FEB0E70520 : 203D 2734 3832 3732 3127 2061 6E64 2069 ='482721' and i
0x000007FEB0E70530 : 6E76 5F74 726E 732E 7479 7065 203D 2753 nv_trns.type ='S
0x000007FEB0E70540 : 2720 616E 6420 7265 6665 7265 6E63 6520 ' and reference
0x000007FEB0E70550 : 3D20 2757 3435 3035 3827 206F 7264 6572 = 'W45058' order
0x000007FEB0E70560 : 2062 7920 7464 6174 6532 2020 2064 6573 by tdate2 des
0x000007FEB0E70570 : 6320 2066 6574 6368 2066 6972 7374 2032 c fetch first 2
0x000007FEB0E70580 : 3030 3020 726F 7773 206F 6E6C 79 000 rows only

$ cut -c 65- sql.txt | tr -d '\n' > myfile.sql

$ cat myfile.sql
ect custno,_trns.tranno,_trns.type,_trns.referen inv_trns.parcase inv_t.condition w 'N' then 'Newhen 'R' t 'Ref' wheG' then 'Ref'n 'D' theDef' when then 'Scrap'n 'T' theTradein' e inv_trns.cotion end asdition, (set distinct deeng from proas where prod = inv_trns.p) as desc_engselect distt prod_type f prod_mas wheproduct = invns.part) as p_type, inv_s.quantity, c(inv_trns.tdaUSA) as tdatease inv_trnype when 'I'n 'Install'n 'M' thenc' end as tdesc, case_trns.transacwhen 'I' theIssue' wheR' then 'Recdnd as transa, inv_trns.te as tdate2,lect coun) from prod_pwhere productnv_trns.parts pkgcount f v_inv_trns itrns WHERE_trns.CUSTNO82721' and itrns.type ='Snd referenceW45058' ordertdate2 desetch first 2nly

---------- Post updated at 12:13 AM ---------- Previous update was at 12:10 AM ----------

@ itkamaraj : it has bit issue in SPACES

Code:
$ awk '{for(i=11;i<=NF;i++){if(i==11){printf("%s ",$i)}else {printf(" %s",$i)}}}' sql.txt

select  custno,inv_trns.tranno, inv_trns.type, inv_trns.referen ce,  inv_trns.part,  case inv_trns.condition  when  'N' then 'New'  when 'R' then  'Ref' when  'G' then 'Ref'when  'D' then  'Def' when'S'  then 'Scrap'when  'T' then  'Tradein' else  inv_trns.condition  end ascondition,  (select  distinct desc_eng  from prod_mas  where product  = inv_trns.part)  as desc_eng,  (select distinct  prod_type from  prod_mas where  product = inv_trns.part)  as prod_type,  inv_trns.quantity,  char(inv_trns.tda te,USA)  as tdate,  case inv_trns.type  when 'I'then  'Install'when  'M' then'Mac'  end as typedesc,  caseinv_trns.transac tn  when 'I' then  'Issue' when  'R' then 'Recd'  end as transactn,  inv_trns.tdate  as tdate2,(select  count(*)  from prod_pkg  where product=  inv_trns.part)  as pkgcount from  v_inv_trns inv_trns  WHEREinv_trns.CUSTNO ='482721'  and inv_trns.type  ='S'  and reference=  'W45058' orderby  tdate2 desc  fetch first 2rows  only

Last edited by Scrutinizer; 07-05-2012 at 03:39 AM.. Reason: code tags
# 5  
Old 07-05-2012
Quote:
Originally Posted by Sunusernewbie
its not working

cut -c 65- sql.txt | tr -d '\n' > myfile.sql
This should work. It seems that your query does not start at 65th column. Try replacing 65 with 62.
# 6  
Old 07-05-2012
if your query starts at 62 chars in each line than try this
Code:
awk 'BEGIN{c=0}{a[c++]=substr($0,62)}END{for (i=0;i<c;i++){printf a[i]};print}' sql.txt > query.txt

this wont work if query start at different char position in each line

---------- Post updated at 01:36 PM ---------- Previous update was at 01:24 PM ----------

if no. of spaces before query is same in each line than this will work fine for you
Code:
awk 'BEGIN{c=0}{for (i=11;i<NF;i++){printf $i" "};print $NF}' sql.txt | tr -d '\n' > query.txt

Last edited by Franklin52; 07-05-2012 at 08:27 AM.. Reason: Please use code tags for data and code samples, thank you
# 7  
Old 07-05-2012
Assumptions:
1. All lines in the dump are of an identical, fixed width.
2. All whitespace in the dump format consists of spaces (including padding in any last, shortened lines).
3. All dumps have the same endianness.

I did not attempt to simply cut out the ascii on the right since hexdumps don't always represent the ascii faithfully, e.g. newlines and tabs are often transformed into dots.

The following endeavours to convert the byte values directly.
Code:
cut -b 21-60 file | tr ' ' '\n' | fold -w2 | (echo ibase=16; cat) | bc | awk '{printf "%c", $0+0}'

If you continue to have problems with all of the suggestions, provide a hexdump of the hexdump and provide as much information as possible regarding any gotchas in its format/representation.

Regards,
Alister
Last edited by alister; 07-05-2012 at 10:46 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Retrieving sequence data from other file

Hello experts :cool:, I am new to programming and will need your help.. I have 2 very large files with the following format: FILE1: >MLP1019 PL4 >MLP7456 PL3 >MLP9268 PL9 >MLP6245 PL1 FILE2: >MLP1019 STNAPLQTSNTWVSYQPSMMMSLQ >MLP7456 PPYWYWNSAVMIFYVQPLSLLAVLLA >MLP9268... (2 Replies)
Discussion started by: narachaid
2 Replies

2. Shell Programming and Scripting

Modifying col values based on another col

Hi, Please help with this. I have several excel files (with and .xlsx format) with 10-15 columns each. They all have the same type of data but the columns are not ordered in the same way. Here is a 3 column example. What I want to do add the alphabet from column 2 to column 3, provided... (9 Replies)
Discussion started by: newbie83
9 Replies

3. Shell Programming and Scripting

Printing from col x to end of line, except last col

Hello, I have some tab delimited data and I need to move the last col. I could hard code it, awk '{ print $1,$NF,$2,$3,$4,etc }' infile > outfile but it would be nice to know the syntax to print a range cols. I know in cut you can do, cut -f 1,4-8,11- to print fields 1,... (8 Replies)
Discussion started by: LMHmedchem
8 Replies

4. UNIX for Dummies Questions & Answers

retrieving data between two strings

I have input file like AAA AAA CCC CCC CCC EEE EEE EEE EEE FFF FFF GGG GGG i was trying to retrieve data between two strings using sed. sed -n /CCC/,/FFF/p input_file Am getting output like CCC CCC CCC (22 Replies)
Discussion started by: NareshN
22 Replies

5. Shell Programming and Scripting

Merging data from col 3 to col2 with awk or sed

Dear Friends, I have a file in which lists State and Phone numbers. Does anybody have a solution in which to take the data from col3 and place it on col2? AK 2988421640 9077467107 AK 2998266711 2069239034 AK 2983804242 2069239034 AK 2960407849 AK ... (3 Replies)
Discussion started by: liketheshell
3 Replies

6. Linux

Retrieving Data from VHD File (Virtual Machine Harddrive)

Hello, I had Gentoo installed on a Microsoft Windows Hyper-V virtual machine. The system shutdown properly but the RAID array on the drive it was on failed. We had a backup that was poorly configured and as such we didn't back up all of the data we needed. Therefore, after getting the RAID... (0 Replies)
Discussion started by: ckoeber
0 Replies

7. UNIX for Advanced & Expert Users

Print line based on highest value of col (B) and repetion of values in col (A)

Hello everyone, I am writing a script to process data from the ATP world tour. I have a file which contains: t=540 y=2011 r=1 p=N409 t=540 y=2011 r=2 p=N409 t=540 y=2011 r=3 p=N409 t=540 y=2011 r=4 p=N409 t=520 y=2011 r=1 p=N409 t=520 y=2011 r=2 p=N409 t=520 y=2011 r=3 p=N409 The... (4 Replies)
Discussion started by: imahmoud
4 Replies

8. UNIX and Linux Applications

Retrieving data from a database and store to a file

Hi I'm using and Oracle 10g Database. I want to write a script to retrieve data from the database and store it toa file. I'm using simple sql statements such as Select * from celltable I don't know how to specify the database name etc. I have this but it doesn't work ... (1 Reply)
Discussion started by: ladyAnne
1 Replies

9. Shell Programming and Scripting

Using loop reading a file,retrieving data from data base.

Hi All, I am having trouble through, I am reading the input from tab delimited file containing several records, e.g. line1 field1 field2 field3 so on.. line2 field1 field2 field3 so on.. .. .. on the basis of certain fields for each record in input file, I have to retrieve... (1 Reply)
Discussion started by: Sonu4lov
1 Replies

10. UNIX for Dummies Questions & Answers

Retrieving data

Friends, I have a data with 3 columns: 30 41 1 39 19 4 14 25 3 .... .... ..... I want to retrieve any data in the first column that is greater 15. What is the best way to do this? Thanks! (2 Replies)
Discussion started by: bobo
2 Replies
Login or Register to Ask a Question