Solved

regex & mysql & perl

Posted on 2013-12-28
2
297 Views
Last Modified: 2014-01-07
I have some files as given below and it is generated by an other software i need to record them into mysql with

date cabinet port upload download time

columns


K16.Result.28-12-2013-11-08-58.txt

cabinet : K16
time : 28-12-2013 11:08:58

from the file
2 1048941172 7134284

port  : 2
upload : 1048941172
download  : 7134284

[root@sflow snmpactivity]# ls -l
total 20
-rw-r--r-- 1 root root 1044 Dec 28 11:09 K16.Result.28-12-2013-11-08-58.txt
-rw-r--r-- 1 root root  744 Dec 28 11:09 K17.Result.28-12-2013-11-08-58.txt
-rw-r--r-- 1 root root 1247 Dec 28 11:09 ROUTER.Result.28-12-2013-11-08-58.txt
-rw-r--r-- 1 root root 2475 Dec 28 11:09 T01.OUT.28-12-2013-11-08-58.txt
-rw-r--r-- 1 root root  925 Dec 28 11:09 T01.Result.28-12-2013-11-08-58.txt

Open in new window



[root@sflow scripts]# cat snmpactivity/K16.Result.28-12-2013-11-08-58.txt
1 0
2 1048941172 7134284
3 3529411188 3185307262
4 1048687588 6735180
5 2840247361 4175184636
6 2657702532 271368251
7 2961612083 1091749773
8 3878772277 2619036428
9 1044640118 188432
10 0
11 3883362145 2744237398
12 4137364222 4011646502
13 3204266302 1070142078
14 0
15 3214335177 3502388384
16 0
17 2325405345 1347644254
18 249701586 2938848862
19 1617958588 2135678611
20 1042992729 2851911
21 3632181468 1141920086
22 2092103702 2803266339
23 1544137143 3535215114
24 1614928891 959086658
25 1049028038 7423758
26 511661802 2644622558
27 1044508473 12530
28 1044518637 19130
29 0
30 1044695107 327690
31 1282656802 3355194564
32 1715099776 1404745494
33 3872591119 4290906417
34 0
35 2183833671 972684281
36 4254282367 2838704160
37 1044668906 3194863
38 1049459257 7695900
39 1048022231 6003614
40 1048261461 5846452
41 3124176458 889028303
42 1852391274 3095560750
43 732955858 2190609680
44 1086479583 67235631
45 1051889807 514447884
46 0
47 0
48 2829606450 2505355220
1000 0
1001 0
1002 0
1003 0
1004 0
1005 0
1006 0
1007 0
100000 0

Open in new window

0
Comment
Question by:3XLcom
2 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 39744075
This should do it.  You were a little unclear in defining date and time - your desired columns list both but your breakdown of the filename only lists a single datetime called time.
use strict;
use warnings;
use DBI;
# you'll need to define the variables used in the connection string (or replace with literals
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$hostname;port=$port", $user, $passwd);
foreach my $fil (@ARGV) {
    unless ($fil =~ m{^([^.]+\.(\d+-\d+-\d+)-(\d+-\d+-\d+)\.txt$}) {
        warn "file name does not conform - skipping $fil\n";
        next;
    }
    my ($cab, $dt, $tm) = ($1, $2, $3);
    $tm =~ s{-}{:}g;
    open IN, $fil or die "could not open $fil: $!";
    while (<IN>) {
        chomp;
        my ($port, $up, $dn) = split;
        $dn = 0 unless defined($dn);
        $dbh->do("insert into table values (?, ?, ?, ?, ?, ?)", $cab, $dt, $port, $up, $dn, $tm);
    }
    close IN;
}

Open in new window

0
 

Author Closing Comment

by:3XLcom
ID: 39762532
sorry thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now