Use the xlscat tool that comes with Spreadsheet::Read (which uses Text::CSV_XS to parse the CSV) with the -L option

perlform

Once you have that part working, if you really need fixed-width text output, you'll want to loop over the rows and keep track of the longest string length in each column. Then, print each row using printf or pack.

I'm more familiar with printf, so I think it's easier; the full documentation is actually found under sprintf - it let's you control column width, left- vs. right-justification within the column, number of digits to the right of the decimal for floating-point numbers, and so on. Once you get the hang of it, you can do anything.

But I have to ask: what purpose will be served by having fixed-witdth text output? If it's for human readability, that's a good-enough reason, but if it's just to allow some other program to do something with data from Excel, there's probably a better way...

Welcome jazzlover,

The answers you received so far are all excellent.

So this is my suggestion on making your life easier, if you are going to stay at the new job for some years to come.

Before Perl, I used fixed width files and/or csv files, so naturally I did the same with Perl. But once I learned and re-learned the power of the Perl hash, I now keep all data files as saved hashes. (Note: I started a simple sample, but it became too complex.) This is a skeleton using your data for an example (untested).

  use strict;
  use warnings;

  our %people = (); # I use 'our' for global and 'my' for temporary da
+ta
  our $ksep = "\r"; # you define your key separator
  our $dsep = "\t"; # you define your data separator
                    # I use chr(30) & chr(254) respectfully!
                    #   Use something that will not be part
                    #   of the raw data or use 'pack' for the
                    #   length of the data elements.
 
  my $key  = 'pat';
  my $data = "$key$ksep";
    $data .= "Name=$key$dsep";   # You can use "\t" instead of '='
    $data .= "Address=50 car road$dsep";
    $data .= "Postcode=aa1 1ab$dsep";
    . . .
    $data .= "END=Finish"; # I like an end ( helps with 'split' )

  $people{$key} = $data    # key is also part of data

  # This can now to saved in a flat file or database or both.
  # You need a double 'split' to get the key and data
  #    Then each data field needs to be split on '=' or "\t"
[download]

The format of the data can be converted (subroutine?) to whatever format is needed. It's easy to add more data fields if needed.

Recently, I had to add 3 new fields for a report. No problem!

Each day I have audit reports that must be in Soap, XML, PDF, MIME, docx, etc. formats, but all the raw data is saved in key/value database as saved Perl hashes. Perl is G R E A T !

Regards...Ed

Update: I missed the Excel/Access requirement, but I can't test that, so I don't even know if this will work or not. It's meant for CSV./Update

Here's a quick attempt that isn't really efficient (it has to read the file twice, first to get the max widths, then to write it out), but it does do what you want (from what I can tell). It puts your first requirement (the padded entries) into a file called out.1, and the output for your second requirement in out.2.

use warnings;
use strict;

use Text::CSV;

my $csv = Text::CSV->new;

my $file = 'in.csv';
my $out1 = 'out.1';
my $out2 = 'out.2';

open my $fh, '<', $file
  or die "can't open the damned csv file!: $!";

my %widths;

while (my $row = $csv->getline($fh)){
    for (0..$#$row){
        my $length = length $row->[$_];
        if (! $widths{$_} || $length > $widths{$_}){
            $widths{$_} = $length;
        }
    }
}

seek $fh, 0, 0;

open my $wfh_1, '>', $out1
  or die "can't open the bloody output file $out1!: $!";

open my $wfh_2, '>', $out2
  or die "can't open the dirty output file $out2!: $!";

my @header;
my $got_header;

while (my $row = $csv->getline($fh)){

    @header = @$row if ! $got_header;
    $got_header = 1;

    for (0..$#$row){
        if (@header){
            print $wfh_2 "$header[$_] : $row->[$_]\n";
        }

        my $elem_len = $widths{$_} - length($row->[$_]);
        $row->[$_] .= ' ' x $elem_len;
    }

    print $wfh_1 join(' ', @$row);
    print $wfh_1 "\n";
}

close $fh;
close $out1;
close $out2;
[download]

-stevieb

A requirement to format fields to certain widths almost certainly has external limits, meaning the field width must always be 20, for instance, even if input is 10 or 22.

Update: except in this case, as the OP explicitly states :) Well, I did say "almost certainly..."

Dum Spiro Spero

#!/usr/bin/perl

# http://perlmonks.org/?node_id=1146524

use strict;
use warnings;

chomp( my @data = <DATA> );
my @widths;

for( @data )
  {
  my @fields = split /,/;
  $widths[$_] |= $fields[$_] for 0..$#fields;
  }

my $format = join(' ', map '%-' . length() . 's', @widths) . "\n";

printf $format, split /,/ for @data;

print "\n";

my @keys = split /,/, shift @data;
my $i = 0;
for( @data )
  {
  print "$keys[$i++ % @keys]: $_\n" for split /,/;
  }

__DATA__
name,address,postcode,mobile,
pat,50 car road,aa1 1ab,99999
patr,100 car av,bb1 1bb,88888
[download]