Visit Our UNIX and Linux User Community


how to convert this file using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to convert this file using awk
# 1  
Old 09-01-2009
how to convert this file using awk

Hi all,
I have a file with this format..

Alphabet,0
1,0,
ID,1,
a,0,
b,0,
c,0,
d,0,
e,0,
f,0,
:
:
:
z,2;
2,0,-->another record
ID,2,
a,0,
b,0,
c,0,
d,0,
e,0,
f,0,
:
:
z,0;
4,0,-->another new record
2,0,
ID,2,
a,0,
b,0,
c,0,
d,0,
e,0,
f,0,
:
:
:
:
:
z,0;
3,0,-->another record
ID,4,
a,0,
b,0,
c,0,
d,0,
e,0,
f,0,
:
:
:
z,3;
:
:
:
The output that I need is:
ID,a,b,c,d,e,f,.....,z
1,0,0,0,0,0,0,.....,2
2,0,0,0,0,0,0,.....,0
4,0,0,0,0,0,0,.....,0
3,0,0,0,0,0,0,.....,3

I have try the code like this
awk '
BEGIN { FS=","}
{if (NR>2 && NR<26)
{printf $2};}
{if (NR>26)
{printf ( "%s,", $2)}}
{if (NR>52)
{printf ( "%s,", $2)}}
{if (NR>78)
{printf ( "%s,", $2)}}' inputfile

output is:
ID,a,b,c,d,e,f,.....,z,1,0,0,0,0,0,0,.....,2,2,0,0,0,0,0,0,.....,0,4,0,0,0,0,0,0,.....,0,3,0,0,0,0,0 ,0,.....,3-->all become 1 line Smilie
Which is not what I want. Besides,the number of new record is not consistent. Just have the info that if the $2 end with ";" then, next line is the new records.

ANyone can help?

Thanks
# 2  
Old 09-01-2009
try..
Code:
awk '
BEGIN { FS=","} 
{if (NR>2 && NR<26)
{printf $2};}
{if (NR>26)
{printf ( "%s,", $2)}}
{if (NR>52)
{printf ( "%s,", $2)}}
{if (NR>78)
{printf ( "%s,", $2 "\n")}}' inputfile

# 3  
Old 09-01-2009
Hi ryandegreat,

Thanks for your help.

But for this one {if (NR>78)
{printf ( "%s,", $2 "\n")}}

the output become like this
3,
0,
0,
0,
0,
0,
:
:
:
3

Is there any other way that after the awk see the ";", it will know that next line is the new record and print the new record?

Thanks.
# 4  
Old 09-02-2009
Here's another approach in awk:

Code:
$ 
$ cat -n inputfile
     1    1,0,
     2    ID,1,
     3    a,0,
     4    b,0,
     5    c,0,
    ...
    ...
    26    x,0,
    27    y,0,
    28    z,2;
    29    2,0,
    30    ID,2,
    31    a,0,
    ...
    ...
    55    y,0,
    56    z,0;
    57    4,0,
    58    ID,3, 
    59    a,0,
    ...
    ...
    83    y,0,
    84    z,0;
    85    3,0,
    86    ID,4,
    87    a,0,
   ...
   ...
   111    y,0,
   112    z,3;
$ 
$ 
$ ##
$ awk -F",|;" '{ if (/^[0-9],/){
>                  if (NR>1) {
>                    if (length(s1)>0) {
>                      print substr(s1,2); s1=""
>                    }
>                    print s2
>                  }
>                  s2=$1
>                } else if($1 != "ID") {
>                  s2=s2","$2
>                }
>                if (NR > 1 && NR <= 28) {s1=s1","$1}
>              } END {print s2}' inputfile
ID,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2
2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3
$ 
$

tyler_durden
# 5  
Old 09-02-2009
Code:
local $/=";\n";
open FH,"<a.txt";
while(<FH>){
   my @tmp=split("\n",$_);
   print join ",", map {my @t=split(",",$_); $t[1]=~s/[;\n]//; $t[1];} @tmp[1..$#tmp];
   print "\n";
}


Previous Thread | Next Thread
Test Your Knowledge in Computers #510
Difficulty: Easy
Variable names cannot be the same as any reserved word as the language would not be able to distinguish between them.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to convert 2 columns into matrix -awk?

How can i convert two columns in to o and 1 matrix. thnks Input a c1 b c2 c c1 d c3 e c4 output c1 c2 c3 c4 a 1 0 0 0 b 0 1 0 0 c 1 0 0 0 d 0 0 ... (5 Replies)
Discussion started by: quincyjones
5 Replies

2. Shell Programming and Scripting

awk convert date format

Could you tell me how to convert the following dates? If I have m/d/yyyy, I want to have 0m/0d/yyyy. I want my dates to always be 8 digits. In other words, I want a 0 inserted whenever the month or day is a single digit. My issue is first I need to use FS="," to get field $4 for the... (7 Replies)
Discussion started by: wbrunc
7 Replies

3. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

4. UNIX Desktop Questions & Answers

Convert time with AWK

Hi All, I need your help with the following: My input shows the time in this format 1311547776493 I need to convert it (as part of my AWK script) into this format: 18-08-2011 09:35:11.072 Thanks a lot! Royi (4 Replies)
Discussion started by: royip
4 Replies

5. Programming

awk script to convert a text file into csv format

hi...... thanks for allowing me to start a discussion i am collecting usb usage details of all users and convert it into csv files so that i can export it into some database.. the input text file is as follows:- USB History Dump by nabiy (c)2008 (1) --- Kingston DataTraveler 130 USB... (2 Replies)
Discussion started by: certteam
2 Replies

6. Shell Programming and Scripting

AWK Script to convert input file(s) to output file

Hi All, I am hoping someone can help me with some scripting I need to complete using AWK. I'm trying to process multiple fixed files to generate one concatenated fixed file in a standard format. The Input file is:- aaaa bbbbb ccccc 1 xxxx aaa bbb aaaa bbbbb ccccc 2 abcd aaa CCC... (9 Replies)
Discussion started by: jason_v_brown
9 Replies

7. Shell Programming and Scripting

Convert date with awk

Hi all, I'm fighting with awk because I didn't find any way with other commands :( I need to convert a date from format A to format B : format A : "YYYY MM DD" format B : "DD-MON-YYYY" Exemple : "2010 3 25" => "25-MAR-2010" My main difficulty is to convert the month with something... (4 Replies)
Discussion started by: madmat
4 Replies

8. Shell Programming and Scripting

Using awk to convert DD-MMM-YY to YYYYMMDD

Hi i need to convert a date in the format DD-Mon-YY to YYYYDDMM Ex : 01-JUL-00 to 20000701 Can anybdy help me with this?? Thanks in advance Shenaz (5 Replies)
Discussion started by: shanu_85
5 Replies

9. Shell Programming and Scripting

Awk to convert a flat file to CSV file

Hi , I have a file with contents as below: Contract Cancellation Report UARCNCL LOS CODE DATE REAS TYPE AMOUNT AMOUNT LETTER BY ========= ======= ==== ==== ==== ========= ==== ==== 8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N .00 .00 CCAN 8678696 8091709 1S NONE DDEB 30-JUN-2008... (14 Replies)
Discussion started by: rkumudha
14 Replies

10. Shell Programming and Scripting

awk to perl convert

Dear Collegue Do anybody help me to convert this AWK script to Perl script { for (i = 2; i <= length ($0); i++) { x = substr($0, i , 1) if (c > 0) { b = b x if (x == "(") c++ ... (2 Replies)
Discussion started by: jaganadh
2 Replies

Featured Tech Videos