Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Sorting Tab file

by raj8 (Sexton)
on Apr 25, 2002 at 05:37 UTC ( #161863=perlquestion: print w/replies, xml ) Need Help??

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
my $row = 0; while (<TABFILE>) { chomp; my @Fld = split ('\t', $_); my $col = 0; foreach my $token (@Fld) { $worksheet->write($row, $col, $token); $col++; } $row++; };

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++; }
      Thanks for your reply. I have been searching CPAN and docs on a way to grab those elements. Once again, thanks -raj
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) {

    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]);


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...

    Matthew Musgrove
    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://161863]
Approved by rob_au
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (2)
As of 2021-11-28 15:08 GMT
Find Nodes?
    Voting Booth?

    No recent polls found