Re: CSV to Excel Converter

I don't want to take away the fun you have in creating utilities like this, but have you taken the time to look at the csv2xls utility that I ship with Text-CSV_XS in the examples folder?


usage: csv2xls -s <sep> -q <quot> -w <width> -d <dtfmt>
               -o <xls> file.csv
       -s <sep>   use <sep>   as seperator char. Auto-detect, default = ','
                  The string "tab" is allowed.
       -e <esc>   use <sep>   as seperator char. Auto-detect, default = ','
                  The string "undef" is allowed.
       -q <quot>  use <quot>  as quotation char. Default = '"'
                  The string "undef" will disable quotation.
       -w <width> use <width> as default minimum column width (4)
       -o <xls>   write output to file named <xls>, defaults
                  to input file name with .csv replaced with .xls
                  if from standard input, defaults to csv2xls.xls
       -F         allow formula's. Otherwise fields starting with
                  an equal sign are forced to string
       -f         force usage of <xls> if already exists (unlink before use)
       -d <dtfmt> use <dtfmt> as date formats.   Default = 'dd-mm-yyyy'
       -D cols    only convert dates in columns <cols>. Default is everywhere.
       -u         CSV is UTF8
       -v <lvl> verbosity (default = 1)

It is a script I already use in real-life for ages now. It is a working script that already deals with big files

Updated on 2013-04-23 to show most recent usage and remove restriction

Enjoy, Have FUN! H.Merijn

Comment on Re: CSV to Excel Converter Select or Download Code

Replies are listed 'Best First'.
Re^2: CSV to Excel Converter by eric256 (Parson) on Sep 21, 2007 at 16:43 UTC
Does that automaticaly wrap to a second worksheet when to many records are added? ___________ Eric Hodges	[reply]
Re^3: CSV to Excel Converter by Tux (Canon) on Sep 21, 2007 at 18:39 UTC
No, it switches to Spreadsheet::WriteExcel::Big when the incoming size is over a certain bound. I bet that adding switching to a new sheet would be an easy to add option. I myself have never wanted that. I want a sheet to be all or nothing. If it doesn't fit, I would have to invent some logic to break up the source, instead of breaking up the destination Enjoy, Have FUN! H.Merijn	[reply]
Re^4: CSV to Excel Converter by runrig (Abbot) on Sep 21, 2007 at 18:50 UTC
It doesn't matter how big the file is. Excel has a row limit (65536), and the OP is hitting that limit. Try: `#!/usr/bin/perl use Spreadsheet::WriteExcel; my $xls = Spreadsheet::WriteExcel->new('tmp.xls') or die "Blah: $^E"; my $ws = $xls->add_worksheet(); my $row = [1..5]; for (0..70000) { $ws->write_row($_, 0, $row); } $xls->close;` [download] No errors, but the spreadsheet will only have 65536 rows (maybe it depends on the version of Excel? Hey, just like we'd never need more than 640K of memory, we'd never need more than 65536 rows, would we? :-). Your script would have been a good starting point for the OP though. BTW, recent versions of S::WE automatically use the "Big" version when needed (so same results if you use 'Big' above).	[reply] [d/l]
Re^3: CSV to Excel Converter by Anonymous Monk on Jan 13, 2009 at 05:07 UTC
how to implement this code after downloading .... i know nothing bout it ...i just want to change an csv to excel properly	[reply]
Re^4: CSV to Excel Converter by holli (Abbot) on Jan 13, 2009 at 05:15 UTC
And what is your definition of properly? holli, /regexed monk/	[reply]
Re^4: CSV to Excel Converter by Anonymous Monk on Jan 29, 2009 at 19:16 UTC
yes, I can't use it either. Looks like it's programmer use only, not for everyday users.	[reply]