Bascule has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks *bows*,

Noob alert and apologies in advance for anything you deem requires an apology.

Just cutting my teeth on Perl and Excel - I've done some basic Perl in the past - now looking to learn new stuff.

I'm messing around with a script to run a "df -Pk" command on linux boxes using plink from my desktop (my $output = `plink -pw <pwd> -ssh -l <usr> $server df -Pk`;) and getting the output into excel. So far so good - the following code pastes the output of the df command into the first column of my spreadsheet:

my $CLIP = Win32::Clipboard();
$CLIP->Set($output);
print "Clipboard contains: ", $CLIP->Get(), "\n";
my $paste_range = $worksheet->Range('A1');
$paste_range->PasteSpecial();

[download]

Each line is in its own row but all in a single column. Is there a way I can specify a delimiter whilst pasting (so that the file systems and values in each line are in separate cells) or is there a way of replicating the Data > Text to Columns process once I've pasted the data into the sheet?

Any advice gratefully received.

Replies are listed 'Best First'.
Re: Excel > Data > Text to Columns (Win32::OLE)
by davies (Monsignor) on Apr 29, 2014 at 13:23 UTC

    I'm not absolutely clear what it is you want. There is a "Text to Columns" command in Excel, which may be what you are talking about. Anything that is available from the face of the spreadsheet is available from Win32::OLE. The classic advice is to record an Excel macro and translate that to Perl, but be warned that recorded macros are usually very bad VBA, and transliterating that without understanding it will lead to bad Perl. Is the following what you want?

    use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;

for my $nSht (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

my $sht = $wb->Sheets(1);
$sht->Cells(1, 1)->{Value}="a b";
$sht->Cells(2, 1)->{Value}="a c";
$sht->Cells(3, 1)->{Value}="ab c";

$sht->Range("A1:A3")->TextToColumns({
    Destination => $sht->Range("A1"),
    Space       => 1,
});

    [download]

    Regards,

    John Davies

[reply]
[d/l]
      John, Thanks a lot for the reply. That's exactly the kind of thing I was after. I currently have a sheet with a single column (A1) where each cell in that column contains a line of the output: 
      Filesystem         1024-blocks      Used Available Capacity Mounted on
/abc/defghi/dskvg00-root   1015704    546516    416760      57% /
/abc/defghi/dskvg00-var   2539312   1054620   1353636      44% /var
/abc/defghi/dskvg00-tmp   1552232     41392   1432208       3% /tmp
/abc/defghi/dskvg00-home    126931      5693    114685       5% /home
/abc/defghi/dskvg00-usr   2539312   1240960   1167280      52% /usr
/abc/defghi/dskvg00-usrlocal    126931      7146    113232       6% /u
+sr/local

      [download]
      I'm looking to split this up into multiple columns using the spaces as delimiters. I'll have a play with your example. Thanks again.
[reply]
[d/l]
        use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;

for my $nSht (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

my $sht = $wb->Sheets(1);
$sht->Cells(1, 1)->{Value}="Filesystem         1024-blocks      Used A
+vailable Capacity Mounted on";
$sht->Cells(2, 1)->{Value}="/abc/defghi/dskvg00-root   1015704    5465
+16    416760      57% /";
$sht->Cells(3, 1)->{Value}="/abc/defghi/dskvg00-var   2539312   105462
+0   1353636      44% /var";
$sht->Cells(4, 1)->{Value}="/abc/defghi/dskvg00-tmp   1552232     4139
+2   1432208       3% /tmp";
$sht->Cells(5, 1)->{Value}="/abc/defghi/dskvg00-home    126931      56
+93    114685       5% /home";
$sht->Cells(6, 1)->{Value}="/abc/defghi/dskvg00-usr   2539312   124096
+0   1167280      52% /usr";
$sht->Cells(7, 1)->{Value}="/abc/defghi/dskvg00-usrlocal    126931    
+  7146    113232       6% /usr/local";

$sht->Range("A1:A" . $sht->UsedRange->Rows->{Count})->TextToColumns({
    Destination          => $sht->Range("A1"),
    Space                => 1,
    ConsecutiveDelimiter => 1,
});

$sht->UsedRange->Columns->AutoFit;

        [download]

        Obviously this isn't the way to import the data, but you seem to have solved that already. There are three main changes:

        Line 22 calculates the number of rows automatically. There are more complicated ways. Avoid them.

        The ConsecutiveDelimiter line treats all spaces as a single delimiter.

        The AutoFit line is purely cosmetic.

        I can't work out quickly how to stop "Mounted on" being split into 2 columns. If there's anything that can be done about that at generation time, it might help. Or you might insert some code to remove & replace the space. Or live with it. Or something else.

        Regards,

        John Davies

[reply]
[d/l]
[select]
Re: Excel > Data > Text to Columns (Win32::OLE)
by poj (Abbot) on Apr 29, 2014 at 14:02 UTC

    See if this works

    $output =~ s/ +/\t/g;
my $CLIP = Win32::Clipboard();
$CLIP->Set($output);

    [download]
    poj
[reply]
[d/l]
      Haha! Yes, that did the trick. Thanks. Now I have a couple of options..
[reply]

        Actually, just adding the one line meant that my paste worked fine as it was.

        $output =~ s/ +/\t/g;
[reply]
[d/l]

      If this does what I think it does, converting spaces to tabs, all this will change is the delimiter needed in the code I posted. It's possible to tell each of the delimiters whether they should be on or off, and mix & match your own to suit.

      Regards,

      John Davies

[reply]

Back to Seekers of Perl Wisdom