Can we convert a '|' file into a fixed lenght???


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Can we convert a '|' file into a fixed lenght???
# 1  
Old 05-29-2007
Can we convert a '|' file into a fixed lenght???

Hi All,

I have a pipe separated flat file.But there is often some problem with the records.So is it possible to convert the '|' separated file into a fixed length file by means of some script.

The file has 11 columns which means 10 pipes.Your help is appreciated.

i'm using Sun OS Version 5.10

Thank you,
Kumar
# 2  
Old 05-29-2007
You can do something like that :
Code:
awk '
   BEGIN {
      fields_count = split("5,5,5,5,5,5,5,5,5,5,5", fsize, ",");
      FS  = "|"
      OFS = "";
   }
   function cnv_field(fld   ) {
      if (length($fld) > fsize[fld]) {
         printf("Line %d, field %d is too long (%d > %d)\n", NR, fld, length($fld), fsize[fld]) | "cat >&2";
         status = 1;
      }
      $fld = sprintf("%-5.5s", $fld);
   }
   {
      if (NF != fields_count) { 
         printf("Line %d, fields count is invalid (%d != %d)\n", NR, NF, fields_count) | "cat >&2";
         status = 1;
      }
      for (f=1; f<=NF; f++) cnv_field(f);
      print;
   }
   END {
      exit status;
   }
    '  $1 > $2

The length of each field is specified in the fields_count assignment. In my code all the fields are 5 characters.
In the output, the field separator is set to "" but you can modify it. For example if you want a space modify the OFS assignment :
OFS = " "
Example (assume script file is convert.sh) :
Code:
$ cat input_file
111|22|333|444|555||77|888|9999|000|1
aa|bbbbbb|cc|dd|rr|ff|ggggggg|hh|ii|jjj|hhh
xxx|yyy|zzz
$ convert.sh input_file output_file
Line 2, field 2 is too long (6 > 5)
Line 2, field 7 is too long (7 > 5)
Line 3, fields count is invalid (3 != 11)
$ echo $?
1
$ cat output_file
111  22   333  444  555       77   888  9999 000  1    
aa   bbbbbcc   dd   rr   ff   ggggghh   ii   jjj  hhh  
xxx  yyy  zzz  
$

Jean-Pierre.

Last edited by aigles; 05-30-2007 at 05:26 AM.. Reason: add infos abour output field separator
# 3  
Old 05-30-2007
As the "fields" in your file are separated by a constant char ("|") use cut to separate them, then print the lines via printf (i assume Kornshell here, use 'echo' instead of 'print' if you are using something else):

Code:
cat infile | while read line ; do
     # split each input line to fields and catch these in variables
     field1="$(print - "$line" | cut -d'|' -f1)"
     field2="$(print - "$line" | cut -d'|' -f2)"
     field3="$(print - "$line" | cut -d'|' -f3)"
     .....
     
     # after you are done with the line print it out again
     # i assume here that the first column should be 20 chars wide, the next
     # two 15, and so on. see the second example below.
     printf '%20s %15s %15s [...]\n' "$field1" "field2" "$field3" [...] >> outfile
done

This is using (a fixed number of) fixed-width columns and you have to know the widths in advance. It is possible to create dynamically formatted columns but you will have to read the infile two times:


Code:
maxlength1=0
maxlength2=0
....
cat infile | while read line ; do
     # in the first run we split and get the max width for each column
     field1="$(print - "$line" | cut -d'|' -f1)"
     length1=$(print - "$field1" | wc -c)
     if [ $length1 -gt $maxlength1 ] ; then
          maxlength1=$length1
     fi
     field2="$(print - "$line" | cut -d'|' -f2)"
     length2=$(print - "$field2" | wc -c)
     if [ $length2 -gt $maxlength2 ] ; then
          maxlength2=$length1
     fi
     .....
done

# put together the output template for printf
template='%'"$maxlength1"'s   %"'$maxlength2"'s [.....]\n'
   
cat infile | while read line ; do
     # in the second run we split again and print using the found widths
     field1="$(print - "$line" | cut -d'|' -f1)"
     field2="$(print - "$line" | cut -d'|' -f2)"
     ....
     printf "$template" "$field1" "field2" "$field3" [...] >> outfile
done

I'd suggest you use (dynamical) arrays instead the numbered variables to make the script able to deal with a variable number of fields in the input file as a further enhancement. The column separator could then be provided as a parameter making the script as widely usable as possible.

bakunin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Convert a fixed width file to a delimited file

Hi - this is a generic question .... is there any utility which can convert a fixed width file format to a delimited file (any given character delimited) ? (5 Replies)
Discussion started by: i4ismail
5 Replies

2. Shell Programming and Scripting

Remove new line character and add space to convert into fixed width file

I have a file with different record length. The file as to be converted into fixed length by appending spaces at the end of record. The length should be calculated based on the record with maximum length in the file. If the length is less than the max length, the spaces should be appended... (4 Replies)
Discussion started by: Amrutha24
4 Replies

3. UNIX for Advanced & Expert Users

how to find lenght of fixed width file record?

actually i am trying to find the lenght of fixed width file record reading from teradata db but its not working can u guys help me out? code which i wrote--- colmn_lngth=`cat $RPT_FILE | awk -F~ '{print $1}'` rm $RPT_FILE while read line do result=`echo $line | wc -m` ... (4 Replies)
Discussion started by: Seshendranath
4 Replies

4. UNIX for Dummies Questions & Answers

convert # delimited text file to fixed width

Hello gurus, I have a file containing 5 columns delimited by '#' as shown in the example below: HRP1000-PLVAR#HRP1000-OTYPE#HRP1000-OBJID#HRP1000-BEGDA#HRP1000-ENDDA# 99991231#AU7129#000000000#1 PROCTER & GAMBLE# 99991231#TT4283#1000013883#21111 LAUNDRY# 99991231#TT4283#1000013884#21121 DISH... (3 Replies)
Discussion started by: chumsky
3 Replies

5. UNIX for Dummies Questions & Answers

Convert a tab delimited/variable length file to fixed length file

Hi, all. I need to convert a file tab delimited/variable length file in AIX to a fixed lenght file delimited by spaces. This is the input file: 10200002<tab>US$ COM<tab>16/12/2008<tab>2,3775<tab>2,3783 19300978<tab>EURO<tab>16/12/2008<tab>3,28523<tab>3,28657 And this is the expected... (2 Replies)
Discussion started by: Everton_Silveir
2 Replies

6. Shell Programming and Scripting

Convert Variable file to fixed file.

I have a set of variable date log files /tmp/test/test1_<YYYYMMDD>_A.log /tmp/test/test2_<YYYYMMDD>_B.log /tmp/test/test3_<YYYYMMDD>_C.log /tmp/test/test4_<YYYYMMDD>_D.log /tmp/test/test5_<YYYYMMDD>_E.log which should be converted (should have content of all the above listed files) into... (9 Replies)
Discussion started by: abhi9845
9 Replies

7. UNIX for Dummies Questions & Answers

Convert Variable file to fixed file.

I have a set of variable date log files /tmp/test/test1_<YYYYMMDD>_A.log /tmp/test/test2_<YYYYMMDD>_B.log /tmp/test/test3_<YYYYMMDD>_C.log /tmp/test/test4_<YYYYMMDD>_D.log /tmp/test/test5_<YYYYMMDD>_E.log which should be converted (should have content of all the above listed files) into... (1 Reply)
Discussion started by: abhi9845
1 Replies

8. Shell Programming and Scripting

convert fixed length file to CSV

Newbie Looking for a script to convert my input file to delimited text file. Not familier with AWK or shell programing. Below is sample record in my input file and the expected output format. My OS is HPUX 11.23. Thanks in advance for your assistance. tbtbs input file:... (12 Replies)
Discussion started by: tbtbs
12 Replies

9. Shell Programming and Scripting

how to convert Fixed length file to delimited file.

I have below fixed lenth file . I have to convert this to delimitted file. File1.txtE116005/29/19930E001E000 E12201/23/19940E001E003 E10406/4/19940E001E003 I want to convert this to : E116,0,05/29/1993,0,E001,E000 E122,0,1/23/1994,0,E001,E003 E104,0,6/4/1994,0,E001,E003 I have a... (7 Replies)
Discussion started by: satyam_sat
7 Replies

10. Shell Programming and Scripting

convert XML file into Text file(fixed length)

If someone out there could help me out with this problem. I would really appreciate it. I am trying to convert xml into text file(fixed length) using Unix Borne shell scripts. My xml file: <root> <header_rec recordtype="00"> <record_id>00</record_id> ... (0 Replies)
Discussion started by: ram2s2001
0 Replies
Login or Register to Ask a Question