Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: Excel > Data > Text to Columns (Win32::OLE)

by davies (Vicar)
on Apr 29, 2014 at 13:23 UTC ( #1084320=note: print w/ replies, xml ) Need Help??


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, });

Regards,

John Davies


Comment on Re: Excel > Data > Text to Columns (Win32::OLE)
Download Code
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
    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.
      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;

      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

        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?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1084320]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (15)
As of 2014-11-24 18:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (144 votes), past polls