http://qs321.pair.com?node_id=161863

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

I have been attempting to open a tab delimitted file and split it and then only take certain fields to populate an Excel Spreadsheet to no avail. I get the spreadsheet populated, but I get every thing in the file. I only want a few items. For example, I would only want fields: Job ID and Status from the file which is shown below. How would one change the below split() to accomplish this. Thanks for the help.. 
Job ID"    "Job PID"  "Type"  "State"    "Status"
1     1        B       Complete      0

[download]
  my $row = 0;
    while (<TABFILE>) {
        chomp;
        my @Fld = split ('\t', $_);
        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
        $col++;
    }
    $row++;
};

[download]

Replies are listed 'Best First'.
Re: Sorting Tab file
by ar0n (Priest) on Apr 25, 2002 at 05:43 UTC
    You needn't change the split, but you could use a array slice in on the loop (only get the first and fifth column): 
    my $row = 0;
while (<TABFILE>) {
  chomp;

  my @fld = split /\t/;
  my $col = 0;

  foreach my $token (@fld[0,4]) {
    $worksheet->write($row, $col, $token);
    $col++;
  }

  $row++;
}

    [download]
[reply]
[d/l]
      Thanks for your reply. I have been searching CPAN and docs on a way to grab those elements. Once again, thanks -raj
[reply]
Re: Sorting Tab file
by Kanji (Parson) on Apr 25, 2002 at 06:18 UTC

    If you really wanted to change split, you could use an array slice like ar0n demonstrated, but applying it directly to split instead of @Fld ...

    my @Fld = ( split /\t/ )[0,4];
my $col = 0;
foreach my $token (@Fld) {

    [download]

    Another way, is to loop over the indicies you want...

    my @fld_no = qw( 0 4 );
my $row    = 0;
 # ...
  foreach my $idx (@fld_no) {
    $worksheet->write($row, $col, $fld[$idx]);

    [download]

        --k.


[reply]
[d/l]
[select]
Re: Sorting Tab file
by Mr. Muskrat (Canon) on Apr 25, 2002 at 14:58 UTC
    Another option (albeit larger and slower) is to fully embrace the Dark Side:
    #!/usr/bin/perl
use strict 'vars';
use warnings;
use Win32::OLE qw(with);
$Win32::OLE::Warn = 3;
my $Excel;
eval {$Excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $Excel) {
  $Excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or
+ die "Cannot start Excel";
}
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
my $Filename = "c:/windows/desktop/some.txt";
my $Tab = $Sheet->QueryTables->Add("TEXT;". $Filename, $Sheet->Range("
+A1"));
$Tab->{Name} = "Job Status";
with($Tab,
  FieldNames => 1,
  RowNumbers => 0,
  FillAdjacentFormulas => 0,
  PreserveFormatting => 1,
  RefreshOnFileOpen => 0,
  RefreshStyle => 1,                  # xlInsertDeleteCells
  SavePassword => 0,
  SaveData => 1,
  AdjustColumnWidth => 0,
  RefreshPeriod => 0,
  TextFilePromptOnRefresh => 0,
  TextFilePlatform => 2,              # xlWindows
  TextFileStartRow => 1,
  TextFileParseType => 1,              # xlDelimited
  TextFileTextQualifier => 1,            # xlTextQualifierDoubleQuote
  TextFileConsecutiveDelimiter => 0,
  TextFileTabDelimiter => 1,                   # Tabs!
  TextFileSemicolonDelimiter => 0,
  TextFileCommaDelimiter => 0,
  TextFileSpaceDelimiter => 0,
  TextFileColumnDataTypes => [1, 9, 9, 9, 1], # Not sure about this li
+ne
);
$Tab->  Refresh->{BackgroundQuery} = 0;
# Do what you normally do in excel here...

    [download]
    Matthew Musgrove
    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?
[reply]
[d/l]

Back to Seekers of Perl Wisdom