Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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
Re: *log to *.xls
by marto (Chancellor) 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 browsing the Monastery: (4)
As of 2014-09-23 04:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls