Import Vertical Text records into Excel? 2

[RF101]

I have searched extensively but I have been unable to find a PERL script or module that will do what I want. I may be using the wrong terminology.

I have a lot of text based reports in the following format:

date 03/06/2008
time 08:00
release 19.0
script_ver 3
omc_id 1230
mm_id 12301
mm_type 2-way PUMA R12000 MM
paging_mode UDP
att 18032
moa 8953
mta 9079
cc 14525

The records are a set length and repeat. I am looking for a way to grab this data and transpose it so that it is easier to use in excel or access.

Like this:

Date Time Release Etc...
03/06/2008 08:00 19.0

Any help would be greatly appreciated.

Thanks,

RF101

 

[wardy66]

Hi RF101

You could probably do something with hashes

use strict;
use warnings;

my @rows;       # Contain each "record"
my $row = -1;   # Will be incremented to 0 shortly


LINE:
while (<>) {
  chomp;
  # Get the field name and rest of the line
  my ( $field_name, $field_val ) = unpack 'A20A*', $_;

  # Skip blank lines
  next LINE if $field_name eq '';

  # Strip trailing space from fixed length records
  $field_name =~ s/ \s+ \z //;
  $field_val  =~ s/ \s+ \z //;

  # Is it a new record
  $row++ if $field_name eq 'date';

  $rows[$row]->{$field_name} = $field_val;
}

for my $row_ref (@rows) {
    for my $field qw( date time release ) {
        print $row_ref->{$field}, "\t";
    }
    print "\n";
}

 

[RF101]

"unpack", This is what I needed. Thank you very much for this contribution.

When I run the script above I get the following output:

03/06/2008 08:00 19.0
03/06/2008 08:00 19.0
03/06/2008 08:00 19.0
03/06/2008 08:00 19.0

This is correct. This is the first three fields for each record. Please excuse my ignorance but how would i get the whole record?

Would I just modify unpack 'A20A*', $_; somehow?

In this case there are 140 fields in each record.

Thanks again,

RF101

 

[RF101]

Sorry. i meant the rest of the fields in the record. Why is it only grabbing the first 3?

 

[wardy66]

No worries

It only prints 3 because I didn't bother typing them all in

This bit is the "culprit"

qw( date time release )

. Just add more field names if you like, in whatever order you like.

Enjoy! Thanks for the star

 

[wardy66]

Just a thought - do you want to print all the fields?

If so, you could put a bit of code at the top to keep track of all the fields ever encountered.

You might also want to replace the tab (\t) with a comma (,) in the print statement if you want CSV-like output.

Keep in mind that CSV output should cope with quotes and commas in the output. If you have the possibility of a comma in a field value, you need to escape or quote it.

There are modules on CPAN that can do this for you.

Also, Excel has the ability to use other delimiters (like semicolon) if that helps on your import.

 

[RF101]

Awesome!

I didn't even see that line for some reason. This will make my life so easy. I can't thank you enough.

Notes for people searching with the same issue:

vertical records, Motorola, text reports, transpose, import

RF101

 

[wardy66]

Oops

Just realised my code was a bit wrong.

Change

=~ s/ \s+ \z //

to

=~ s/ \s+ \z //[b]xms[/b]

The x in xms tells Perl to ignore spaces in the regex (the spaces either side of \s+) but it probably worked ok for you in this case but definitely change it.