Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

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

Comment on *log to *.xls
Download Code
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/WriteExcel.pm did not return a true value at test.pl line 6. BEGIN failed--compilation aborted at test.pl line 6."" in folder C:/Test where I run the scripts, I have: ActivePerl-5.16.1.1601-x86_64-linux-glibc-2.3.5-296175 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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1009532]
Approved by Corion
Front-paged by 2teez
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (11)
As of 2015-07-29 00:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (260 votes), past polls