Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Fixed length to delimited file conversion

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fixed length to delimited file conversion
# 1  
Old 12-08-2014
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
Code:
I   0515 MR       394 
I   0618 MR & MRS 942 
I   0618 MR & MRS 944 
I   0205 MR & MRS 614 
I   0122 MR       940

The file is defined as
Code:
transaction type - 1 char
filler - 3 char
date - 4 char
filler - 1 char
name header - 8 char
filler 1
transaction number - 3 char

I need the input file converted into a delimited file like
Code:
I,0515,MR,394 
I,0618,MR & MRS,942 
I,0618,MR & MRS,944 
I,0205,MR & MRS,614 
I,0122,MR,940

Which is:
Code:
transaction type,date,name header,transaction number

with no fillers.

I tried:
Code:
sed -e 's/./&,/1' -e 's/./&,/9' -e 's/./&,/19' test_fixed_len.txt

This is adding , in the positions I want. I also need to remove the extra spaces in a column if there are any.

Can anyone help me if there is a better way?
# 2  
Old 12-08-2014
How about using awk instead?
Code:
awk  '{print substr($0,1,1), substr($0,5,4), substr($0,10,8), substr($0,19)}' OFS=, test_fixed_len.txt

This User Gave Thanks to Yoda For This Post:
member2014
# 3  
Old 12-08-2014
Thanks Yoda. This is giving me the csv file with the columns I need.

Code:
I,0515,MR      ,394
I,0618,MR & MRS,942
I,0618,MR & MRS,944
I,0205,MR & MRS,614
I,0122,MR      ,940

Is there a way I can trim the spaces from the fields to get something like this instead?
Code:
I,0515,MR,394
I,0618,MR & MRS,942
I,0618,MR & MRS,944
I,0205,MR & MRS,614
I,0122,MR,940

# 4  
Old 12-08-2014
Try
Code:
sed 's/./,/4;s/./,/9;s/./,/18;s/ *,/,/g' file
I,0515,MR,394 
I,0618,MR & MRS,942 
I,0618,MR & MRS,944 
I,0205,MR & MRS,614 
I,0122,MR,940

This User Gave Thanks to RudiC For This Post:
member2014
# 5  
Old 12-08-2014
Thanks Rudi. One problem with this is, some of the columns are adjoining without fillers in between. For such cases this is not working.
# 6  
Old 12-08-2014
GNU awk:
Code:
awk '{sub(/ *$/,x,$5); print $1,$3,$5,$7}' FIELDWIDTHS="1 3 4 1 8 1 3" OFS=, file

Code:
I,0515,MR,394
I,0618,MR & MRS,942
I,0618,MR & MRS,944
I,0205,MR & MRS,614
I,0122,MR,940

These 2 Users Gave Thanks to Scrutinizer For This Post:
member2014 migurus
# 7  
Old 12-08-2014
Quote:
Originally Posted by member2014
Thanks Rudi. One problem with this is, some of the columns are adjoining without fillers in between. For such cases this is not working.
OK. Why didn't you specify that in the first place?

Try
Code:
sed 's/./&,/18;s/./&,/9;s/./&,/4;s/ *,/,/g' file

This User Gave Thanks to RudiC For This Post:
member2014
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Length of a fixed width file

I have a fixed width file of length 53. when is try to get the lengh of the record of that file i get 2 different answers. awk '{print length;exit}' <File_name> The above code gives me length 50. wc -L <File_name> The above code gives me length 53. Please clarify on... (2 Replies)
Discussion started by: Amrutha24
2 Replies

2. Shell Programming and Scripting

Remove characters from fixed length file

Hello I've question on the requirement I am working on. We are getting a fixed length file with "33" characters long. We are processing that file loading into DB. Now some times we are getting a file with "35" characters long. In this case I have to remove two characters (in 22,23... (14 Replies)
Discussion started by: manasvi24
14 Replies

3. Shell Programming and Scripting

Fixed length flat file extraction

Hii ,I am new to Unix ,i have a flat file which is (fixed length) sitting in unix,Which is holding the data for a table.I want to extract one column(length7-10) on the basis of another column(length13-15) and want only one single row Example: Below is the sample of flat file. 1111 AAAA 100 ... (4 Replies)
Discussion started by: laxmi1166
4 Replies

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

5. Shell Programming and Scripting

Need a sort solution for fixed length file

I have a 1250 byte record that I need to sort in column 10-19 and in column 301. I have tried the sort command, but it looks like it needs delimiters to work. The record can have spaces in a lot of its 1250 columns, but 10-19, and 301 are guaranteed. These columns are numeric too. A sample... (1 Reply)
Discussion started by: mb1201
1 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

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

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

10. Shell Programming and Scripting

creating a fixed length output from a variable length input

Is there a command that sets a variable length? I have a input of a variable length field but my output for that field needs to be set to 32 char. Is there such a command? I am on a sun box running ksh Thanks (2 Replies)
Discussion started by: r1500
2 Replies
Login or Register to Ask a Question