Convert comma-delimited records to fixed length records 
Author Message
 Convert comma-delimited records to fixed length records

I have a comma-delimited file where the first field is the first name,
second is the last name, third is the city, fourth is the state, and fifth
is the ZIP code.
The file looks something like this:

John,Doe,Dallas,TX,75051
Jane,Doe,Dallas,TX,75052

Can anyone provide an example, using something like awk or sed, of how to
convert delimited records to fixed length records?
Where the desired output is a fixed-length record with:

First name as 15 characters, blank padded on the right
Last name as 20 characters, blank padded on the right
City as 20 characters, blank padded on the right
State as 2 characters
ZIP code as 5 characters

Something like this:

John            Doe                Dallas                TX75051
Jane            Doe                Dallas                TX75052

Thanks in advance,

David



Wed, 08 Jun 2005 06:45:39 GMT  
 Convert comma-delimited records to fixed length records

Quote:

> The file looks something like this:
> John,Doe,Dallas,TX,75051
> Jane,Doe,Dallas,TX,75052
> Can anyone provide an example, using something like awk or sed, of how to
> convert delimited records to fixed length records?
> First name as 15 characters, blank padded on the right
> Last name as 20 characters, blank padded on the right
> City as 20 characters, blank padded on the right
> State as 2 characters
> ZIP code as 5 characters
> John            Doe                Dallas                TX75051
> Jane            Doe                Dallas                TX75052

Here is an MS-DOS 6.22 batch example demonstrating doing that


  :: Make a demo text file
  echo John,Doe,Dallas,TX,75051> tmp$$$.txt
  echo Jane,Doe,Dallas,TX,75052>>tmp$$$.txt
  ::
  :: Do it
  gawk -F, '{printf"%%-15s%%-20s%%-20s%%-2s%%-5s\n",$1,$2,$3,$4,$5}' tmp$$$.txt
  ::
  :: Clean up
  for %%f in (tmp$$$.*) do if exist %%f del %%f

   All the best, Timo

--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance  ; University of Vaasa

Useful batch files and tricks ftp://garbo.uwasa.fi/pc/link/tsbat.zip



Wed, 08 Jun 2005 10:06:01 GMT  
 Convert comma-delimited records to fixed length records

Quote:

> I have a comma-delimited file where the first field is the first name,
> second is the last name, third is the city, fourth is the state, and fifth
> is the ZIP code.
> The file looks something like this:

> John,Doe,Dallas,TX,75051
> Jane,Doe,Dallas,TX,75052

> Can anyone provide an example, using something like awk or sed, of how to
> convert delimited records to fixed length records?
> Where the desired output is a fixed-length record with:

> First name as 15 characters, blank padded on the right
> Last name as 20 characters, blank padded on the right
> City as 20 characters, blank padded on the right
> State as 2 characters
> ZIP code as 5 characters

> Something like this:

> John            Doe                Dallas                TX75051
> Jane            Doe                Dallas                TX75052

> Thanks in advance,

> David

cat file | while IFS=, read first last city state zip; do
    printf '%-15s%-20s%-20s%2s%5s\n' $first $last $city $state $zip
done

--

Linux solution for data management and processing.



Thu, 09 Jun 2005 01:55:49 GMT  
 Convert comma-delimited records to fixed length records


Quote:

>> I have a comma-delimited file where the first field is the first name,
>> second is the last name, third is the city, fourth is the state, and fifth
>> is the ZIP code.
>> The file looks something like this:

>> John,Doe,Dallas,TX,75051
>> Jane,Doe,Dallas,TX,75052

>> Can anyone provide an example, using something like awk or sed, of how to
>> convert delimited records to fixed length records?
>> Where the desired output is a fixed-length record with:

>> First name as 15 characters, blank padded on the right
>> Last name as 20 characters, blank padded on the right
>> City as 20 characters, blank padded on the right
>> State as 2 characters
>> ZIP code as 5 characters

>> Something like this:

>> John            Doe                Dallas                TX75051
>> Jane            Doe                Dallas                TX75052

>> Thanks in advance,

>> David

>cat file | while IFS=, read first last city state zip; do
>    printf '%-15s%-20s%-20s%2s%5s\n' $first $last $city $state $zip
>done

1) UUOC
2) Horribly inefficient
3) Grossly off-topic

Other than that, a fine post.

I wasn't going to post, but since I took the time to flame your post, I
figure I might as well give an on-topic, AWK solution:

BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
{
for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
print ""

- Show quoted text -

Quote:
}



Thu, 09 Jun 2005 02:33:38 GMT  
 Convert comma-delimited records to fixed length records

Quote:




>>> John,Doe,Dallas,TX,75051
>>> Jane,Doe,Dallas,TX,75052
>>...
>>> John            Doe                Dallas                TX75051
>>> Jane            Doe                Dallas                TX75052

>>cat file | while IFS=, read first last city state zip; do
>>    printf '%-15s%-20s%-20s%2s%5s\n' $first $last $city $state $zip
>>done

> 1) UUOC

    Having input on the left is easier to type, and easier for the
    newbies (why else would they ask) to read.

Quote:
> 2) Horribly inefficient
> 3) Grossly off-topic

    Okey, but Awk solution so obvious and similiar, that I didn't want
    to take the time to type it out.
        awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

Quote:

> Other than that, a fine post.

    Thanks, I think.

Quote:

> I wasn't going to post, but since I took the time to flame your post, I
> figure I might as well give an on-topic, AWK solution:

> BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
> {
> for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
> print ""
> }

--

Linux solution for data management and processing.


Thu, 09 Jun 2005 04:44:33 GMT  
 Convert comma-delimited records to fixed length records


...

Quote:
>> 1) UUOC

>    Having input on the left is easier to type, and easier for the
>    newbies (why else would they ask) to read.

Most of the time, you can replace:

        cat food | ...
with:
        < food ...

(which is ugly [compared to the usual paradigm of doing the redirections at
the end of the line], but does preserve that "input on the left" feel that
some people seem to think important)

However, it seems you can't do this with a while loop:

        < food while read foo

didn't parse in any of the sh-ish shells I tried.  So, you have to do:

        while read foo
        do ...
        done < food

Quote:
>> 2) Horribly inefficient
>> 3) Grossly off-topic

>    Okey, but Awk solution so obvious and similiar, that I didn't want
>    to take the time to type it out.
>    awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

Except that it doesn't work with original (e.g., Solaris) awk.

Quote:
>> Other than that, a fine post.

>    Thanks, I think.

>> I wasn't going to post, but since I took the time to flame your post, I
>> figure I might as well give an on-topic, AWK solution:

>> BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
>> {
>> for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
>> print ""
>> }

And this is so much better.  Don't you agree?


Thu, 09 Jun 2005 06:35:15 GMT  
 Convert comma-delimited records to fixed length records

Quote:



> >       awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

> >> BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
> >> {
> >> for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
> >> print ""
> >> }
> And this is so much better.  Don't you agree?

An enquiring mind wishes to know. What makes an involved five-liner
that much better?

   All the best, Timo

--
Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
Department of Accounting and Business Finance  ; University of Vaasa

Useful batch files and tricks ftp://garbo.uwasa.fi/pc/link/tsbat.zip



Thu, 09 Jun 2005 06:45:35 GMT  
 Convert comma-delimited records to fixed length records

Quote:




> > > awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

> > >> BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
> > >> {
> > >> for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
> > >> print ""
> > >> }

> > And this is so much better.  Don't you agree?

> An enquiring mind wishes to know. What makes an involved five-liner
> that much better?

>    All the best, Timo

> --
> Prof. Timo Salmi ftp & http://garbo.uwasa.fi/ archives 193.166.120.5
> Department of Accounting and Business Finance  ; University of Vaasa

> Useful batch files and tricks ftp://garbo.uwasa.fi/pc/link/tsbat.zip

Timo -

According to Kenny, "[the one-liner] doesn't work with original (e.g.,
Solaris) awk."

I can't test either

    awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

or

    BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
    {
        for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
        print ""
    }

on Solaris but that particular awk is apparently so flakey (Search Google
Groups for "Sun", "Solaris") that I believe anything bad anyone says about
it.  Also, because it is so bad, I see no reason anyone would want to
specifically make sure their code was compatible with that version* (so I
wonder why Kenny brought it up?).

*Except for antique programming in an environment that doesn't have a better
awk (Solaris ships with another working awk, but it's not in the path), or
the ability to install Gnu Awk.

    - Dan



Fri, 10 Jun 2005 08:10:31 GMT  
 Convert comma-delimited records to fixed length records


Quote:






>> > > awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'

>> > >> BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
>> > >> {
>> > >> for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
>> > >> print ""
>> > >> }

>> > And this is so much better.  Don't you agree?

>> An enquiring mind wishes to know. What makes an involved five-liner
>> that much better?

>>    All the best, Timo

Thank you professor Timo : >

Quote:
> According to Kenny, "[the one-liner] doesn't work with original (e.g.,
> Solaris) awk."

No because it doesnt support -F. Big deal. Just do something
like this:
awk '{FS=","} {printf "%-15s%-20s%-20s%2s%5s\n",$1,$2,$3,$4,$5}'

Still a one liner and more efficient than the 5 liner if anyone
bothered to check it out.

- Show quoted text -

Quote:
> I can't test either
>     awk -F ',' '{printf "%-15s%-20s%-20s%2s%5s\n", $1, $2, $3, $4, $5}'
> or
>     BEGIN   { nf = split("15 20 20 2 5",widths);FS = "," }
>     {
>         for (i=1; i<=nf; i++) printf("%-*s",widths[i],$i)
>         print ""
>     }
> on Solaris but that particular awk is apparently so flakey (Search Google
> Groups for "Sun", "Solaris") that I believe anything bad anyone says about
> it.  Also, because it is so bad, I see no reason anyone would want to
> specifically make sure their code was compatible with that version* (so I
> wonder why Kenny brought it up?).
> *Except for antique programming in an environment that doesn't have a better
> awk (Solaris ships with another working awk, but it's not in the path), or
> the ability to install Gnu Awk.

Actually they ship /bin/nawk and /usr/xpg4/bin/awk and oawk (which doesnt
support -F either : > )
The companion CD has the package SFWgawk
All freely downloadable in iso image format...
If you cannot install packages or build your own thats a different
matter. For sure that can happen but thats not a Solaris restriction.


Sat, 11 Jun 2005 00:12:16 GMT  
 Convert comma-delimited records to fixed length records

Quote:

>The file looks something like this:

>John,Doe,Dallas,TX,75051
>Jane,Doe,Dallas,TX,75052

>Can anyone provide an example, using something like awk or sed, of how to
>convert delimited records to fixed length records?

>Something like this:

>John            Doe                Dallas                TX75051
>Jane            Doe                Dallas                TX75052

You've seen the awk solutions, now for a sed script ...

$ cat sed.scr

# first, add more than enough spaces in front of every comma
s/,/                         ,/g
# the line below sets width of second-from-right field
s/\(.* ,..\) *,/\1/
s/\(.* ,...................\) *,/\1/
s/\(.* ,...................\) *,/\1/
# the line below sets width of left-most field
s/\(...............\) *,/\1/

$ sed -f sed.scr data

John           Doe                Dallas             TX75051
Jane           Doe                Dallas             TX75052

I've avoided the \{ extension so this will work with all versions of sed.

Note: any spaces in your comma-separated data will mess with my script,
so if any data can contain spaces replace each space in the sed script
with say, an underscore if no underscores occur in the data, and append
a command to as a final step resubstitute those underscores with spaces:
s/_/ /g

--
John Savage            (for email, replace "ks" with "k" and delete "n")



Wed, 15 Jun 2005 04:09:15 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. matching records in a comma delimited file

2. Reading Comma delimited, Quoted String records

3. Trouble loading comma delimited records

4. problem ascii file with fixed record length

5. Variable vs. fixed-length records performance

6. Need Help converting a Clarion DB to comma delimited

7. Getting true length of a variable length record - IBM Mainframe

8. Finding Variable-Length Record Length

9. getting fields NOT comma delimited with commas inside

10. Problem when formating a variable delimited file to fixed length

11. How to convert tab delimited file to space delimited file

12. length field and record

 

 
Powered by phpBB® Forum Software