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";
}

Login or Register to Ask a Question

Previous Thread | Next Thread

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
Login or Register to Ask a Question

Featured Tech Videos