Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

comment on

( [id://3333] : superdoc . print w/replies, xml ) Need Help??
What's the best way to delete a column if you don't have keywords you are looking for? I have a spreadsheet I parse very often it has 0-35 columns and I want to automate this process so it only writes 0-9 columns. (They aren't in order) I tried messing with $col_num, I learned that's not what I need. I need to also sort by date, but thats not my main concern right now. I've adapted some of my excel sub routines that use Spreadsheet::ParseXLSX and Excel::Writer::XLSX and I've searched many forms related to this issue and they've had different format/problems and approaches to those problems especially since alot of them used WIN::OLE a module I'm not very familiar with. I just didnt want to write something completely from scratch with something i'm not used to if the modules I am familiar with will work. Please guide me wise monks.. I have a couple subroutine I could post too. The main one is one ive used on a few different excel projects, I've managed to narrow it down to 60 lines functionally, I realize that may not be ideal as far as SCCE.
use Spreadsheet::ParseXLSX; use Excel::Writer::XLSX; my $in_file = "Input.xlsx"; my $out_file = "Output.xlsx"; # Read in all rows from existing file my $rows = read_excel($in_file); my $count = 0; # Write the updated rows to new file my $col_num = 2; write_excel($out_file, $rows, $col_num); ++$count; sub read_excel { my ( $file, $sheet ) = @_; $sheet ||= 0; my $parser = Spreadsheet::ParseXLSX->new(); my $workbook = $parser->parse($file); if ( not defined $workbook ) { die $parser->error; } my $worksheet = $workbook->worksheet($sheet); my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); my @rows; for my $row ( $row_min .. $row_max ) { my @cells; for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); if (not $cell) { push(@cells,''); next; } my $value = $cell->value(); push(@cells,$value); } push(@rows,\@cells); } return \@rows; } sub write_excel { my ( $file, $rows, $col_max ) = @_; my $workbook = Excel::Writer::XLSX->new( $file ); if ( not defined $workbook ) { die "Could not open file: $!"; } my $worksheet = $workbook->add_worksheet(); my $row_num = 0; for my $row ( @$rows ) { for my $col (0 .. $col_max) { $worksheet->write( $row_num, $col, $row->[$col] ); } $row_num++; } $workbook->close(); return; }
Input:
program_name enrolled_date_cst Prospect Name Salesperson Client Name Street Address City State Zip Code program_status Termination Date Current Program Length Total Debt Enrolled Verified Debt Amount Unverified Debt Amount Original Debt Without Conditional Debt payment_frequency Original scheduled first draft date First Deposit Date Change Flag First Draft Date First Draft Status Second Draft Date Second Draft Status Third Draft Date Third Draft Status Fourth Draft Date Fourth Draft Status Most Recent Draft Failure Reason Bank Account Status Received in Program Loan Flag OBC Completed Within 2 Hrs Flag Service Entity Name Client Deposit Flag Client Deposit Month
0 BRP-255762 2023-03-21 00Q5d00002ECrhKEAT Tammy Smith Larry Buss Charlestown IN 47111 Enrolled 56 46734 46734 0 46734 Monthly 2023-03-31 N 2023-03-31 Completed 2023-04-15 Completed 2023-05-14 Scheduled 2023-06-14 Scheduled VERIFIED FALSE Y Beyond Finance Yes 2023-03-31
1 BRP-281722 2023-04-25 00Q5d00002FBoa6EAD Dan Tucker Linda Smith Morehead KY 40351 Enrolled 55 42724 42724 0 29439 Monthly 2023-04-27 N 2023-04-27 Completed 2023-05-26 Scheduled 2023-06-26 Scheduled 2023-07-26 Scheduled AUTO_VERIFIED FALSE Y Beyond Finance Yes 2023-04-30
output:
program_name enrolled_date_cst Salesperson Client Name Total Debt Enrolled Verified Debt Amount Unverified Debt Amount First Draft Date First Draft status
26 BRP-238848 2023-02-24 Tami Smith MORRIS CLEGG 18461 10315 8146 2023-02-28 Returned
44 BRP-240923 2023-02-28 Tami Smith Paul Brannon 22162 22162 0 2023-03-07 Completed

In reply to Deleting a excel column by MoodyDreams999

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.