Languages and Scripting
Showing results for 
Search instead for 
Do you mean 

how to reformat flat file using cut command

SOLVED
Go to Solution
Occasional Advisor

how to reformat flat file using cut command

Hi

today I got some new requirement ,I am not able to get the solution for this using
awk or cut command please help me.

Problem: I have given sample input file below,

I/p file
-------------------------------------------
9029774138: "B2", 1;20101009;50791.00|2;20101009;698.77|3;20290309;862655.00|4;20290309;454646.00|
5;20290309;94.00|10;20101005;0.00|11;20101103;0.00|15;20291112;47.00|16;20291203;55.00|
17;20101017;0.00
Required output file
---------------------------
9029774138:"B2":10:1,2,3,4,5,10,11,15,16,17
--------------------------------------------
out put is a colon seperated file first two fields are same as first two fields in input file, third record is the count (number of pipe symbols + 1) fourth record is string seperated by comma which contains each field after the pipe symbol .

Thanks in Anticipation
Gopi
1 ACCEPTED SOLUTIONS
Honored Contributor

Re: how to reformat flat file using cut command

to 'walk' the list you'll have to write a little program in the language of your choice.
C, shell, perl, awk.

Using AWK, the following seems to work:

---------- test.awk --------
{
values = split ($3, a, "|");
line = $1 $2 values ":";
for (i = 1; i < values; i++) {
split (a[i], b, ";");
line = line b[1] ",";
}
split (a[values], last_one, ";");
print line last_one[1];
}
----------------

use as : # awk -f test.awk test.txt

The program splits $3 into an array 'a'

It starts by building the first part of the line using $1, $2 and the returned count for the split.

Then it walks all but the last element from 'a' to split again on ";" into array 'b'.
Element 1 in 'b' has the value.
Glue that to the line, and add a comma.

Add the last one separately to avoid the extra comma.

Enjoy,
Hein
3 REPLIES
Honored Contributor Honored Contributor

Re: how to reformat flat file using cut command

The pipe character is a rather evil separator in shell. Lots of ways to do anything on unix. Call this one the shell IFS example:

# convertStuff() {
-> IFS="#"
->
-> for X in "${@}"
-> do
-> echo $X
-> done
-> }
# convertStuff $( cat file | sed -e "s/\|/#/g" )
1;20101009;50791.00
2;20101009;698.77
3;20290309;862655.00
4;20290309;454646.00
5;20290309;94.00
10;20101005;0.00
11;20101103;0.00
15;20291112;47.00
16;20291203;55.00
17;20101017;0.00

Using that trick a pure sh solution is an easy exercise for the reader...

Bets on time until a Perl solution is posted and who does it? :-)
Honored Contributor

Re: how to reformat flat file using cut command

to 'walk' the list you'll have to write a little program in the language of your choice.
C, shell, perl, awk.

Using AWK, the following seems to work:

---------- test.awk --------
{
values = split ($3, a, "|");
line = $1 $2 values ":";
for (i = 1; i < values; i++) {
split (a[i], b, ";");
line = line b[1] ",";
}
split (a[values], last_one, ";");
print line last_one[1];
}
----------------

use as : # awk -f test.awk test.txt

The program splits $3 into an array 'a'

It starts by building the first part of the line using $1, $2 and the returned count for the split.

Then it walks all but the last element from 'a' to split again on ";" into array 'b'.
Element 1 in 'b' has the value.
Glue that to the line, and add a comma.

Add the last one separately to avoid the extra comma.

Enjoy,
Hein
Highlighted
Occasional Advisor

Re: how to reformat flat file using cut command

ThanQ Hein,

It is very useful. Once again Thanks for your solution.



Cheers,
Gopi