in reply to Excel > Data > Text to Columns (Win32::OLE)

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

Replies are listed 'Best First'.
Re^2: Excel > Data > Text to Columns (Win32::OLE)
by Bascule (Initiate) on Apr 29, 2014 at 14:18 UTC
    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]
        This is really helpful - many thanks. I've not yet been able to find any information on TextToColumns. Can you recommend any documentation on Win32::OLE, methods, etc?
[reply]

In Section Seekers of Perl Wisdom