Visit Our UNIX and Linux User Community

convert fixed length file to CSV

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting convert fixed length file to CSV
# 1  
Old 08-06-2008
convert fixed length file to CSV


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.


input file:
4002000W1ABCDABCD7821 123456789071001080600W1VUF 34216002902291LSN 1230-1630 +000000+000400+00000000+00009164+0

4002000W1JKLMABCD6022 123456789071001080600W1VUF 34360001302894UOM +000000+000000+00000000+00014514+0

4002000W1JKLMABCD6022 123456789071001080600W1VUF 34360001302791RFN123456 +000000+008000+00000000+00223280+0

4002000W1JKLMABCD6022 123456789071001080600W1VUFC34360001303067UJN +000000+000000+00000000+00015213+0

Output file should look like this:

4002000 W1ABCD ABCD 7821 123456789 071001 080600 W1VUF 34216002902291 LSN 1230-1630 +000000 +000400 +00000000 +00009164 +000400 +0000916450125 +00000000

4002000 W1JKLM ABCD 6022 123456789 071001 080600 W1VUF 34360001302894 UOM +000000 +000000 +00000000 +00014514 +000000 +0001451413125 +00000000

4002000 W1JKLM ABCD 6022 123456789 071001 080600 W1VUF 34360001302791 RFN 123456 +000000 +008000 +00000000 +00223280 +008000 +0022328050125 +00000000

4002000 W1JKLM ABCD 6022 123456789 071001 080600 W1VUFC 34360001303067 UJN +000000 +000000 +00000000 +00015213 +000000 +0001521313125 +00000000
# 2  
Old 08-06-2008
Question Am I reading your sample right?

It looks like each of the first three records will have slightly different rules.
(1) has 1230-1630 as field
(2) does not have this field
(3) creates this field by splitting off the previous field

rec (4) appears like rec (2)
# 3  
Old 08-06-2008
Yes, that is correct the rules will be different. How do you set the delimiter(,) if there is nothing in the field(ie field 11 of record 2,3 and 4?

1230-1630 is a field. Basically REC1 is the master record format.
# 4  
Old 08-06-2008
Question Maybe let's talk this through for rules before going further

There are three possible record layouts. So, read the record.
if five fields separated by " " space characters, then type A
if four fields and field 3 is 17 characters, then type B
if four fields and field 3 is 23 characters, then type C (similar to type A)

Is this correct logic?
# 5  
Old 08-06-2008
Can you use perl? If so, you can build a mask for each of the three fixed format records, then unpack() them and join() them using your delimiter (looks like a space, supposed to be comma in CSV??)....

$mycsv = join(" ", unpack("mask-goes-here"));
# 6  
Old 08-06-2008
that is correct
# 7  
Old 08-06-2008
Hi Quine

sorry Im not a programmer. No knowledge of Perl.

Previous Thread | Next Thread
Test Your Knowledge in Computers #357
Difficulty: Medium
BusyBox includes an AWK implementation written by Linus Torvalds.
True or False?

10 More Discussions You Might Find Interesting

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

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

3. Shell Programming and Scripting

how to read fixed length flat file....

Hi Gurus, Thanks in advance... I am new to writing shell scripting and help me out reading a flat file with fixed length. I have a fixed length flat file with storename(lenth 6) , emailaddress(lenth 15), location(10). There is NO delimiters in that file. Like the following str00001.txt... (2 Replies)
Discussion started by: willywilly
2 Replies

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

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

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

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

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

Featured Tech Videos