Add a delimiter in a fixed width flatfile

[MAYIANAN]

Hi all,

I have a fixed with flatfile where i need to add a delimiter for each column.

e.g file

D313971283783JOHN
D313977382918MARTIN

I need the file to look like

D31,3971283783,JOHN
D31,3977382918,MARTIN

[Wim Rombauts · Accepted solution]

I think something like this is the easiest :$

sed 's/\(...\)\(..........\)\(.*\)/\1,\2,\3/' inputfile > outputfile

[MAYIANAN]

Thanks Wim,

But the record length may vary from file to file, where i will get the record length from a config file. I need to read the record length and frame the sed dynamically and execute it. Is there any other easiest way??

[Wim Rombauts]

I guess you can build the "from" part of sed with a script.

When you start by defining a variable consisting of dots, longer than the longest column, you can add a "column" to sed with something like SEDFROM=$SEDROM'\('$(echo $DOTS | cut -c 1-$COLUMNLENGTH)'\)'
The to part can be build by
SEDTO=$SEDTO',\'$COLUMNNUMBER'

And then your sed command can look like :
sed s/$SEDFROM/$SEDTO/ inputfile > outputfile.

I have never used this type of sed however, so the little details to make it work possibly need some research.

If you know in advance which 10 or 15 column formats you will have, you can as well work with case :

case COLUMNFORMAT in
1) ;;
2) ;;
...
esac

[Dennis Handly]

>But the record length may vary from file to file, where I will get the record length from a config file.

What about the field widths?

>Is there any other easiest way?

You could use awk to read your config file then use that to format your files.

It would help to provide your config file.

[Raj D.]

MAYIANAN,

Here you go , customized to the above Wim's code:

Note: $c having different record length: And it works fine.


a=D313971283783JOHN
b=D313977382918MARTIN
c=D31397738291934234MRTEST



# echo $a |sed 's/\(...\)\([\0-9]*\)\([\A-Z]*\)/\1,\2,\3/'
D31,3971283783,JOHN


# echo $b |sed 's/\(...\)\([\0-9]*\)\([\A-Z]*\)/\1,\2,\3/'
D31,3977382918,MARTIN


# echo $c |sed 's/\(...\)\([\0-9]*\)\([\A-Z]*\)/\1,\2,\3/'
D31,397738291934234,MRTEST



Cheers,
Raj.

[Raj D.]

MAYIANAN,

Based on the above, here you go.. to convert the flat file to a delimited file:


# cat file1
D313971283783JOHN
D313977382918MARTIN
Z31397738291934234MRTEST
Y234242342223423424234MRTESTA



# sed 's/\(...\)\([\0-9]*\)\([\A-Z]*\)/\1,\2,\3/' file1
D31,3971283783,JOHN
D31,3977382918,MARTIN
Z31,397738291934234,MRTEST
Y23,4242342223423424234,MRTESTA
#


Enjoy, Have fun!,
Raj.

[James R. Ferguson]

Hi:

Fixed width format lends itself to a solution like this (using your data);

# perl -ne 'print join ",",unpack("a3,a10,a*",$_)' file
D31,3971283783,JOHN
D31,3977382918,MARTIN

Regards!

...JRF...

[Raj D.]

The perl solution one from JRF , having same issue for different record length as mentioned earlier MAYIANAN looking for solutions with different record lengths,


$ cat file1
D313971283783JOHN
D212122123231332342SARAH
Z234223434534535453GIBSON
Y342342342342342343MARTIN
$


$ perl -ne 'print join ",",unpack("a3,a10,a*",$_)' file1
D31,3971283783,JOHN
D21,2122123231,332342SARAH
Z23,4223434534,535453GIBSON
Y34,2342342342,342343MARTIN

,,$


So far I can see sed working ok in this scenario.


Rgds,
Raj.

[James R. Ferguson]

Hi (again):

> Raj: The perl solution one from JRF , having same issue for different record length as mentioned earlier MAYIANAN looking for solutions with different record lengths,

There is *NO* issue with different record lengths if we assume that Mayianan wants delimiters imposed after the first 3-characters; after the next 10-characters; and then whatever length constitutes the *remainder* of the record follows.

The problem is that the format of the file hasn't been defined. Fixed-length records have fields with defined widths. (think COBOL). Another alternative is a length-string format where the first byte is the length of the string that follows, but that doesn't appear to be the case here.

Regards!

...JRF...

[Raj D.]

I assume , Mayianan tried to capture, first 3 filed, and then remainings before name stats,and then the name with "," .

> The problem is that the format of the file hasn't been defined.

James , that makes sense, agree...



Rgds,
Raj.