Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

*log to *.xls

by frhling (Initiate)
on Dec 19, 2012 at 09:11 UTC ( #1009532=perlquestion: print w/replies, xml ) Need Help??
frhling has asked for the wisdom of the Perl Monks concerning the following question:

I want to convert the log files to xls. by bash script I converted it to csv, but I need to make some changes in some of them. please dont ask why xls and not csv. I need xls and this is to be a automated process. I am new to linux, bash and NULL in Perl. That would be nice if someone help me to convert to xls :-) this is my code in bash scripting:
#!/bin/bash NAME=evaluation_`date +%Y%m%d` find /dir -name "*.log" -mtime 0 -exec cat "{}" \; >> $NAME.log grep -e word1 -e word2 $NAME.log | grep -v word3| awk '{n=split($11,P, +"/");print $1 " " $2 ";" $3 ";" $9 ";" $11 ";" P[n]}' > $NAME.csv sed -i '1iDate;Time;From;To;User' $NAME.csv rm $NAME.log echo "done."

Replies are listed 'Best First'.
Re: *log to *.xls
by marto (Bishop) on Dec 19, 2012 at 09:32 UTC
Re: *log to *.xls
by 2teez (Priest) on Dec 19, 2012 at 10:00 UTC

    "..I want to convert the log files to xls. by bash script I converted it to csv.."

    Since you have a csv file already, you will need Text::CSV_XS or Text::CSV module to read in your csv file and Spreadsheet::WriteExcel to write into an excel file as mentioned by marto.
    Something like so:

    #!/usr/bin/perl use strict; use warnings; use utf8; use Spreadsheet::WriteExcel; use Text::CSV_XS; my $csv = Text::CSV_XS->new( { binary => 1, allow_loose_quotes => 1, } + ) or die "can't open CSV file" . Text::CSV_XS->error_diag(); my $wrkbook = Spreadsheet::WriteExcel->new('new_file.xls'); my $wrksheet = $wrkbook->add_worksheet('new_file'); open my $fh, "<:encoding(utf8)", "Data.csv" or die "can't open file: $ +!"; while ( my $row = $csv->getline($fh) ) { $wrksheet->write_row( $., 0, [ @{$row} ] ); ## write to excel file } $csv->eof or $csv->error->diag(); close $fh or die "can't close file:$!";

    If you tell me, I'll forget.
    If you show me, I'll remember.
    if you involve me, I'll understand.
    --- Author unknown to me

      Or use csv2xls out of Text::CSV_XS' examples folder:

      $ csv2xls --help 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 befor +e use) -d <dtfmt> use <dtfmt> as date formats. Default = 'dd-mm-yyyy +' -D cols only convert dates in columns <cols>. Default is eve +rywhere. -u CSV is UTF8 -v [<lvl>] verbosity (default = 1)

      Enjoy, Have FUN! H.Merijn
      Thanks. I have tried to run this, but I have an Error: "" Spreadsheet/ did not return a true value at line 6. BEGIN failed--compilation aborted at line 6."" in folder C:/Test where I run the scripts, I have: ActivePerl- Spreadsheet-ParseExcel-0.2602 Spreadsheet-WriteExcel-2.38 I dont know what else I should do so that I get rid out of this error. I would appreciated if you help me out.
Re: *log to *.xls
by tobyink (Abbot) on Dec 19, 2012 at 12:33 UTC

    Up until Excel 2007, Excel had a limit of 65,536 rows (and 256 columns) for a spreadsheet. Excel 2007 raised this limit to 1,048,576 rows (and 16,384 columns).

    The column limit is unlikely to be problematic, but log files can easily contain millions of lines.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
      "Excel 2007 raised this limit to 1,048,576 rows (and 16,384 columns)"

      Yes, shure. But who likes to have a spreadsheet with 1,048,576 rows ;-)

      I'm just wondering about how i would do something like this.

      Even splitting such a large log by timestamp into different worksheets is no fun.

      But what else? Perhaps putting the whole stuff into a database and write some stored procedures that do the dirty mangle job(s) a.s.o...?

      Best regards, Karl

      «The Crux of the Biscuit is the Apostrophe»

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1009532]
Approved by Corion
Front-paged by 2teez
Not_a_Number adds chocolate chip cookies to the platter on the sideboard.

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (5)
As of 2017-04-26 16:00 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (484 votes). Check out past polls.