On 24/06/2011 08:45, Wernher Eksteen wrote:
>
> I've attached a text file containing the original and required
> format and avoid the format being lost by just pasting it in the
> email body.
>
> The original format is separated by a space, but need to replace the
> space with a comma, so it will become a comma delimited csv file
> which I will then import to MS Excel.
>
> I'm not sure how to parse the "Connection" and "Sync'ed-as-of-time"
> columns since the dates there in is in this format "Fri Jun 24
> 06:37" which also separated by space, but the spaces in those
> columns shouldn't be replaced by a comma. The date format in those
> columns should remain the same.
>
> Also, is it possible to convert this directly into a MS Excel
> document using Perl?
Hello again Wernher.
My first guess for this problem was to build an 'unpack' format based on
the row of hyphens beneath the header captions. As it may still be
useful, and well worth studying for similar applications, here is my
alternative solution doing just that.
The program grabs everything in the file that is aligned with the row
of hyphens beneath the captions. It works by finding the first line in
the file that contains only hyphens or spaces, and scanning that to
build an unpack string using the @- and @+ arrays that hold the offsets
of the start and end of the previous successful regex match.
Unlike my previous solution, empty (all-space) fields are handled
correctly: the 'A' unpack format discards trailing spaces, and my only
proviso is that if you ever expect leading spaces in fields they must be
trimmed explicitly.
HTH,
Rob
use strict;
use warnings;
use Fcntl 'SEEK_SET';
my $format;
my $fh = *DATA; # Replace with the appropriate 'open my $fh, '<', ... or die
$!;
# Remember the where the file begins, and then build the unpack pattern from
# the first line continaining only hyphens and underscores
#
my $bof = tell $fh;
while (<DATA>) {
chomp;
if (tr/- //c == 0 and tr/-// > 0) {
while (/-+/g) {
my ($beg, $len) = ($-[0], $+[0] - $-[0]);
$format .= "\@$beg A$len ";
}
last;
}
}
warn qq(Data will be upacked with a format of "$format"\n\n);
# Now rewind to the beginning of the file, ignore anything that contains only
# whitespace, and unpack every record according to the pattern that we just
built
#
seek $fh, $bof, SEEK_SET;
while (<$fh>) {
next unless /\S/;
my @data = unpack $format;
print join ',', @data;
print "\n";
}
__DATA__
CTX Destination
Enabled Connection Sync'ed-as-of-time
--- ---------------------------------------------------------------------
------- ---------------- ------------------
1 pool://ZABRYDD01.localdomain/RDP_NEW_REP
yes Sat Jun 18 08:56 Fri Jun 24 06:37
2 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test101
yes Sat Jun 18 08:57 Fri Jun 24 08:01
4 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test22
yes Sat Jun 18 08:57 Fri Jun 24 09:00
5 pool://ZARDPDD01.localdomain/BRYREP
yes Sat Jun 18 08:57 Fri Jun 24 07:01
8 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test31
yes Sat Jun 18 08:57 Fri Jun 24 09:00
10 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test421
yes Sat Jun 18 08:57 Fri Jun 24 09:00
12 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test5
yes Sat Jun 18 08:57 Fri Jun 24 09:00
13 dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test60
yes Sat Jun 18 08:57 Fri Jun 24 09:00
--- ---------------------------------------------------------------------
------- ---------------- ------------------
**OUTPUT**
Data will be upacked with a format of "@0 A3 @6 A69 @78 A7 @88 A16 @107 A18 "
"CTX","Destination","Enabled","Connection","Sync'ed-as-of-time"
"---","---------------------------------------------------------------------","-------","----------------","------------------"
"1","pool://ZABRYDD01.localdomain/RDP_NEW_REP","","Sat Jun 18 08:56","Fri Jun
24 06:37"
"2","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test101","yes","Sat
Jun 18 08:57","Fri Jun 24 08:01"
"4","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test22","yes","Sat
Jun 18 08:57","Fri Jun 24 09:00"
"5","pool://ZARDPDD01.localdomain/BRYREP","yes","Sat Jun 18 08:57","Fri Jun 24
07:01"
"8","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test31","yes","Sat
Jun 18 08:57","Fri Jun 24 09:00"
"10","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test421","yes","Sat
Jun 18 08:57","Fri Jun 24 09:00"
"12","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test5","yes","Sat
Jun 18 08:57","Fri Jun 24 09:00"
"13","dir://ZARDPDD01.localdomain/backup/ZABRYDD01REP/linux/backup/test60","yes","Sat
Jun 18 08:57","Fri Jun 24 09:00"
"---","---------------------------------------------------------------------","-------","----------------","------------------"
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
