Visit Our UNIX and Linux User Community


how to convert Fixed length file to delimited file.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to convert Fixed length file to delimited file.
# 1  
Old 04-02-2008
how to convert Fixed length file to delimited file.

I have below fixed lenth file . I have to convert this to delimitted file.

File1.txt
Code:
E116005/29/19930E001E000
E12201/23/19940E001E003
E10406/4/19940E001E003

I want to convert this to :
Code:
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 sample code ,below iam passing the ending position of each cloumn from other record.
Date is 3rd column,Iam considering date as '10 char' length & ending position to be '15'.So if
i have a date as '5/9/1993' we have to append '0' to date and month so that it will be '05/09/1993'

Code:
sed -e 's/./&,/4' 's/./&,/5' 's/./&,/15' 's/./&,/16' 's/./&,/20' 's/./&,/24'  File1.txt > delimited.csv

I need help .....please help me !!

Last edited by Yogesh Sawant; 04-02-2008 at 04:25 AM.. Reason: added code tags
# 2  
Old 04-02-2008
One way:

Code:
awk -F"/" ' {
s1=substr($1, 1, 4) "," substr($1, 5, 1) "," substr($1, 6)
s2=substr($3, 1, 4) "," substr($3, 5, 1) "," substr($3, 6, 4) "," substr($3, 10)
print s1 "/" $2 "/" s2}
'  File1.txt > delimited.csv

Regards
# 3  
Old 04-02-2008
Thanx Franklin.

why my code is not working ????? Can u please tell me .....

sed -e 's/./&,/4' 's/./&,/5' 's/./&,/15' 's/./&,/16' 's/./&,/20' 's/./&,/24' File1.txt > delimited.csv


where File1 is :

E116005/29/19930E001E000
E122001/23/19940E001E003
E104006/04/19940E001E003
# 4  
Old 04-02-2008
With gawk you can do :
Code:
awk -v FIELDWIDTHS='4 1 10 1 4 4' -v OFS=',' '{ $1=$1 ""; print }' inputfile

Jean-Pierre.
# 5  
Old 04-02-2008
It is not working ......
Can you please check & give exact code . ......

Iam new to unix .....
# 6  
Old 04-02-2008
The code works fine with gawk only :
Code:
$ cat gd.dat
E116005/29/19930E001E000
E122001/23/19940E001E003
E104006/04/19940E001E003
$ gawk -v FIELDWIDTHS='4 1 10 1 4 4' -v OFS=',' '{ $1=$1 ""; print }' gd.dat
E116,0,05/29/1993,0,E001,E000
E122,0,01/23/1994,0,E001,E003
E104,0,06/04/1994,0,E001,E003
$

With standard awk you can do :
Code:
$ cat gd.dat
E116005/29/19930E001E000
E122001/23/19940E001E003
E104006/04/19940E001E003
$ cat gd.sh
awk -v FIELDWIDTHS='4 1 10 1 4 4' -v OFS=',' '
   BEGIN {
      WidthsCount = split(FIELDWIDTHS, Widths);
   }
   {
      fixe = $0;
      pos  = 1;
      for (i=1; i<=WidthsCount; i++) {
         len = Widths[i];
         $i = substr(fixe, pos, len);
         pos += len;
      }
      print;
   }
    ' gd.dat
$ gd.sh
E116,0,05/29/1993,0,E001,E000
E122,0,01/23/1994,0,E001,E003
E104,0,06/04/1994,0,E001,E003
$

Jean-Pierre.
# 7  
Old 04-02-2008
Quote:
Originally Posted by satyam_sat
sed -e 's/./&,/4' 's/./&,/5' 's/./&,/15' 's/./&,/16' 's/./&,/20' 's/./&,/24' File1.txt > delimited.csv


where File1 is :

E116005/29/19930E001E000
E122001/23/19940E001E003
E104006/04/19940E001E003
Try this one.
Code:
sed -e 's/./&,/4' -e 's/./&,/6' -e 's/./&,/17' -e 's/./&,/19' -e 's/./&,/24' File1.txt > delimited.csv


Previous Thread | Next Thread
Test Your Knowledge in Computers #84
Difficulty: Easy
Windows 3.1, Windows 95, Windows 98, and Windows ME were all based on DOS.
True or False?

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

Convert variable length record to fixed length

Hi Team, I have an issue to split the file which is having special chracter(German Char) using awk command. I have a different length records in a file. I am separating the files based on the length using awk command. The command is working fine if the record is not having any... (7 Replies)
Discussion started by: Anthuvan
7 Replies

3. Shell Programming and Scripting

Fixed length to delimited file conversion

Hi All, I need to convert a fixed length file to a delimited file with , (comma). But not all columns, some of the columns in the fixed files are used as fillers and I do not need that in the output file. test_fixed_len.txt I 0515 MR 394 I 0618 MR & MRS 942 I 0618 MR & MRS... (7 Replies)
Discussion started by: member2014
7 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. Shell Programming and Scripting

converting fixed length file to delimited file

hi , i need to convert fixed length file to delimited file using unix where length of each column is variable (2 Replies)
Discussion started by: Nishithinfy
2 Replies

6. 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

7. UNIX for Dummies Questions & Answers

What the command to find out the record length of a fixed length file?

I want to find out the record length of a fixed length file? I forgot the command. Any body know? (9 Replies)
Discussion started by: tranq01
9 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

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

10. Shell Programming and Scripting

Convert delimited to fixed length

Hi, I have to change a tab delimited file to a fixed length file. For text fields I need to left justify and NULL fill to the right and for number fields I need to right justify and zero fill to the left. If there are spaces between words in a text field I need to keep them as spaces. I am using... (14 Replies)
Discussion started by: nelson553011
14 Replies

Featured Tech Videos