This script takes a csv and converts it to excel. It automatically creates new sheets when you reach 50_000 rows. Excels limit is higher than that around 65k but this way they break into nice units. I often have csv files with 100k+ rows and this is a handy way to get them into excel.
#!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; use Text::CSV::Simple; my $infile = shift; usage() unless defined $infile && -f $infile; my $parser = Text::CSV::Simple->new; my @data = $parser->read_file($infile); my $headers = shift @data; my $outfile = shift || $infile . ".xls"; my $subject = shift || 'worksheet'; sub usage { print "csv2xls infile [outfile] [subject]\n"; exit; } my $workbook = Spreadsheet::WriteExcel->new($outfile); my $bold = $workbook->add_format(); $bold->set_bold(1); import_data($workbook, $subject, $headers, \@data); # Add a worksheet sub import_data { my $workbook = shift; my $base_name = shift; my $colums = shift; my $data = shift; my $limit = shift || 50_000; my $start_row = shift || 1; my $worksheet = $workbook->add_worksheet($base_name); $worksheet->add_write_handler(qr[\w], \&store_string_widths); my $w = 1; $worksheet->write('A' . $start_row, $colums, ,$bold); my $i = $start_row; my $qty = 0; for my $row (@$data) { $qty++; if ($i > $limit) { $i = $start_row; $w++; $worksheet = $workbook->add_worksheet("$base_name - $w"); $worksheet->write('A1', $colums,$bold); } $worksheet->write($i++, 0, $row); } autofit_columns($worksheet); warn "Convereted $qty rows."; return $worksheet; } ###################################################################### +######### ###################################################################### +######### # # Functions used for Autofit. # ###################################################################### +######### # # Adjust the column widths to fit the longest string in the column. # sub autofit_columns { my $worksheet = shift; my $col = 0; for my $width (@{$worksheet->{__col_widths}}) { $worksheet->set_column($col, $col, $width) if $width; $col++; } } ###################################################################### +######### # # The following function is a callback that was added via add_write_ha +ndler() # above. It modifies the write() function so that it stores the maximu +m # unwrapped width of a string in a column. # sub store_string_widths { my $worksheet = shift; my $col = $_[1]; my $token = $_[2]; # Ignore some tokens that we aren't interested in. return if not defined $token; # Ignore undefs. return if $token eq ''; # Ignore blank cells. return if ref $token eq 'ARRAY'; # Ignore array refs. return if $token =~ /^=/; # Ignore formula # Ignore numbers #return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+ +))?$/; # Ignore various internal and external hyperlinks. In a real scena +rio # you may wish to track the length of the optional strings used wi +th # urls. return if $token =~ m{^[fh]tt?ps?://}; return if $token =~ m{^mailto:}; return if $token =~ m{^(?:in|ex)ternal:}; # We store the string width as data in the Worksheet object. We us +e # a double underscore key name to avoid conflicts with future name +s. # my $old_width = $worksheet->{__col_widths}->[$col]; my $string_width = string_width($token); if (not defined $old_width or $string_width > $old_width) { # You may wish to set a minimum column width as follows. #return undef if $string_width < 10; $worksheet->{__col_widths}->[$col] = $string_width; } # Return control to write(); return undef; } ###################################################################### +######### # # Very simple conversion between string length and string width for Ar +ial 10. # See below for a more sophisticated method. # sub string_width { return length $_[0]; }
___________
Eric Hodges
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: CSV to Excel Converter
by graff (Chancellor) on Aug 28, 2007 at 01:22 UTC | |
by eric256 (Parson) on Aug 28, 2007 at 14:29 UTC | |
by raj8 (Sexton) on Jun 09, 2010 at 03:39 UTC | |
by Anonymous Monk on Aug 19, 2008 at 11:15 UTC | |
Re: CSV to Excel Converter
by Tux (Canon) on Sep 21, 2007 at 08:24 UTC | |
by eric256 (Parson) on Sep 21, 2007 at 16:43 UTC | |
by Tux (Canon) on Sep 21, 2007 at 18:39 UTC | |
by runrig (Abbot) on Sep 21, 2007 at 18:50 UTC | |
by Anonymous Monk on Jan 13, 2009 at 05:07 UTC | |
by holli (Abbot) on Jan 13, 2009 at 05:15 UTC | |
by Anonymous Monk on Jan 29, 2009 at 19:16 UTC | |
Re: CSV to Excel Converter
by Aim9b (Monk) on Sep 20, 2007 at 15:48 UTC | |
by poolboi (Acolyte) on Feb 14, 2008 at 02:18 UTC | |
by Aim9b (Monk) on Feb 15, 2008 at 21:20 UTC | |
Re: CSV to Excel Converter
by narendra (Initiate) on Oct 06, 2011 at 07:54 UTC | |
Re: CSV to Excel Converter
by bofsthus (Initiate) on Apr 22, 2013 at 22:52 UTC | |
by Anonymous Monk on Apr 23, 2013 at 01:59 UTC | |
by bofsthus (Initiate) on Apr 23, 2013 at 13:23 UTC | |
Re: CSV to Excel Converter
by Anonymous Monk on Jul 17, 2013 at 08:29 UTC | |
by Anonymous Monk on Jul 17, 2013 at 08:42 UTC |
Back to
Cool Uses for Perl