Professional Web Applications Themes

import from text file to mysql - PERL Beginners

how to import text file to mysql using PERL ?. This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal....

  1. #1

    Default import from text file to mysql

    how to import text file to mysql using PERL ?.

    This e-mail and any attachments may contain confidential and
    privileged information. If you are not the intended recipient,
    please notify the sender immediately by return e-mail, delete this
    e-mail and destroy any copies. Any dissemination or use of this
    information by a person other than the intended recipient is
    unauthorized and may be illegal.
    Roime Guest

  2. #2

    Default Re: import from text file to mysql

    On Fri, 24 Sep 2004, Roime bin Puniran wrote:
     

    What is in the text file?

    We can't begin to help you without knowing what the file contains.

    Ignoring Perl for a minute, have you considered using the MySQL command
    line tool `mysqlimport` ? It has lots of options and may be easier to
    use than Perl, at least in this case. Maybe. Doentation for it can be
    found at <http://dev.mysql.com/doc/mysql/en/mysqlimport.html>, among
    other places.


    --
    Chris Devers
    Chris Guest

  3. #3

    Default RE: import from text file to mysql

    i have several packet that captured from monitoring process...Then we save it in txt format...And now, i need to import it into mysql, by that way i can make a report esily...How i can import it using PERL ?...

    Thanks


    -----Original Message-----
    From: Chris Devers [mailto:com]
    Sent: Fri 9/24/2004 10:39 AM
    To: Roime bin Puniran
    Cc: Perl Beginners List
    Subject: Re: import from text file to mysql
    On Fri, 24 Sep 2004, Roime bin Puniran wrote:
     

    What is in the text file?

    We can't begin to help you without knowing what the file contains.

    Ignoring Perl for a minute, have you considered using the MySQL command
    line tool `mysqlimport` ? It has lots of options and may be easier to
    use than Perl, at least in this case. Maybe. Doentation for it can be
    found at <http://dev.mysql.com/doc/mysql/en/mysqlimport.html>, among
    other places.


    --
    Chris Devers

    --
    To unsubscribe, e-mail: org
    For additional commands, e-mail: org
    <http://learn.perl.org/> <http://learn.perl.org/first-response>







    This e-mail and any attachments may contain confidential and
    privileged information. If you are not the intended recipient,
    please notify the sender immediately by return e-mail, delete this
    e-mail and destroy any copies. Any dissemination or use of this
    information by a person other than the intended recipient is
    unauthorized and may be illegal.
    Roime Guest

  4. #4

    Default RE: import from text file to mysql

    * Please don't top-quote.

    * Perl is a language, perl is a program, PERL isn't a word.


    On Fri, 24 Sep 2004, Roime bin Puniran wrote:
     


    I still have no idea what your file looks like. I have a vague idea what
    data the file contains, but that's really not the important thing. Is
    the "text" a comma-separated format? Is it XML? Some kind of freeform
    log file like the Unix syslog or Apache's log? tcpdump output?

    You have to give a small but representative sample of what the data in
    the file looks like.

    Also, you have to show what you have tried so far. Have you written code
    to open the file, make sense of the data, and insert it in the database?
    Or were you just hoping that we would do all of that for you? You need
    to at least make an attempt to solve the problem -- it's okay if you get
    stuck, but there has to be some code to start with.



    --
    Chris Devers
    Chris Guest

  5. #5

    Default RE: import from text file to mysql

    I am sorry that not showing u a detail at least, about my text file...Here is te sample of my text file...

    ================================================== =========================

    "1.200.200.202.157 2.200.200.250.7 3.0 4.255 5.8 6.1109 7.0 8.0 9.1095648052.771 10.1095648052.979 11.1417 12.445 13.27 14.6 15.0 16.0 17.0 18.0 19. 20.0 21.0 22.0 23.119000 24.0 25.0 26.0 27.32"
    "1.200.200.202.157 2.200.200.250.7 3.0 4.255 5.2 6.92 7.0 8.0 9.1095648052.770 10.1095648052.771 11.1413 12.445 13.17 14.6 15.0 16.0 17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.0"
    "1.200.200.245.239 2.200.200.255.255 3.0 4.255 5.6 6.468 7.0 8.0 9.1095648052.402 10.1095648052.847 11.137 12.137 13.0 14.17 15.0 16.0 17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"
    "1.200.200.101.1 2.200.200.255.255 3.0 4.255 5.1 6.243 7.0 8.0 9.1095648061.059 10.1095648061.059 11.138 12.138 13.0 14.17 15.0 16.0 17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"
    "1.200.200.100.97 2.200.200.255.255 3.0 4.255 5.3 6.234 7.0 8.0 9.1095648053.622 10.1095648055.122 11.137 12.137 13.0 14.17 15.0 16.0 17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"

    ================================================== ================

    But there is no problem about the data i think..And sorry because i am not customize it first...:)....The big matter is how can i import a text file into mysql...I just wrote some code ...Here is my code...

    ================================================== ======

    #!/usr/bin/perl

    use DBI;
    use IO::Socket;
    ###########################
    #import.pl
    #Roime Bin Puniran
    #Telekom RnD
    ###########################

    $local = IO::Socket::INET->new(
    Proto => 'tcp',
    LocalAddr => '*******************',
    Reuse => 1
    ) or die "$!";
    $local->listen();
    $local->autoflush(1);

    print "......Testing.......\n";
    #my $addr;

    while($addr=$local->accept())
    {
    print"Connect From : ", $addr->peerhost();
    #Display Message
    print "Port : ", $addr->peerport(), "\n";

    my $dbh=DBI->connect('dbi:mysql:test', 'root','')||
    die "Error opening database: $DBI::errstr\n";

    print "I think it's connect !.........";

    my $sth=$dbh->prepare(" LOAD DATA LOCAL INFILE "/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");

    $sth->execute();
    $set->finish();

    $dbh->disconnect ||
    die "Failed to connect..........";
    $dbh->disconnect ||
    die "Error opening database: $DBI::errstr\n";

    my $result;
    while(<$addr>)
    {
    last if m/^end/gi;
    print "Received : $_";
    print $addr $_;
    $result += $_;
    }

    chomp;
    if(m/^end/gi)
    {
    my $send = "result";
    print $addr "$send\n";
    print "Result: $send\n";
    }
    print "Closed connection\n";

    close $addr;
    print "At your service....Waiting Please !!......\n";
    }


    ================================================== ============
    i just used this below method to import it into mysql, instead of mysqlimport...

    my $sth=$dbh->prepare(" LOAD DATA LOCAL INFILE "/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");

    But i receive this message when i compiled it..............

    "String found where operator expected at import.pl line 34, near "TERMINATED BY ','""
    (Might be a runaway multi-line "" string starting on line 33)
    (Missing semicolon on previous line?)
    syntax error at import.pl line 34, near "TERMINATED BY ','""
    Execution of import.pl aborted due to compilation errors. "

    Do you have any suggestion ?...




    This e-mail and any attachments may contain confidential and
    privileged information. If you are not the intended recipient,
    please notify the sender immediately by return e-mail, delete this
    e-mail and destroy any copies. Any dissemination or use of this
    information by a person other than the intended recipient is
    unauthorized and may be illegal.
    Roime Guest

  6. #6

    Default RE: import from text file to mysql

     
    "/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");

    here you had forgot escape the double quotes,should be escaped quotes:
    \"/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt\"

    my $sth=$dbh->prepare(" LOAD DATA LOCAL INFILE
    \"/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt\" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");



    -----Original Message-----
    From: Roime bin Puniran [mailto:net.my]
    Sent: Friday, September 24, 2004 8:57 AM
    To: Perl Beginners List
    Subject: RE: import from text file to mysql


    I am sorry that not showing u a detail at least, about my text file...Here
    is te sample of my text file...

    ================================================== =========================

    "1.200.200.202.157 2.200.200.250.7 3.0 4.255 5.8 6.1109 7.0 8.0
    9.1095648052.771 10.1095648052.979 11.1417 12.445 13.27 14.6 15.0 16.0
    17.0 18.0 19. 20.0 21.0 22.0 23.119000 24.0 25.0 26.0 27.32"
    "1.200.200.202.157 2.200.200.250.7 3.0 4.255 5.2 6.92 7.0 8.0
    9.1095648052.770 10.1095648052.771 11.1413 12.445 13.17 14.6 15.0 16.0
    17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.0"
    "1.200.200.245.239 2.200.200.255.255 3.0 4.255 5.6 6.468 7.0 8.0
    9.1095648052.402 10.1095648052.847 11.137 12.137 13.0 14.17 15.0 16.0
    17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"
    "1.200.200.101.1 2.200.200.255.255 3.0 4.255 5.1 6.243 7.0 8.0
    9.1095648061.059 10.1095648061.059 11.138 12.138 13.0 14.17 15.0 16.0
    17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"
    "1.200.200.100.97 2.200.200.255.255 3.0 4.255 5.3 6.234 7.0 8.0
    9.1095648053.622 10.1095648055.122 11.137 12.137 13.0 14.17 15.0 16.0
    17.0 18.0 19. 20.0 21.0 22.0 23.0 24.0 25.0 26.0 27.32"

    ================================================== ================

    But there is no problem about the data i think..And sorry because i am not
    customize it first...:)....The big matter is how can i import a text file
    into mysql...I just wrote some code ...Here is my code...

    ================================================== ======

    #!/usr/bin/perl

    use DBI;
    use IO::Socket;
    ###########################
    #import.pl
    #Roime Bin Puniran
    #Telekom RnD
    ###########################

    $local = IO::Socket::INET->new(
    Proto => 'tcp',
    LocalAddr => '*******************',
    Reuse => 1
    ) or die "$!";
    $local->listen();
    $local->autoflush(1);

    print "......Testing.......\n";
    #my $addr;

    while($addr=$local->accept())
    {
    print"Connect From : ", $addr->peerhost();
    #Display Message
    print "Port : ", $addr->peerport(), "\n";

    my $dbh=DBI->connect('dbi:mysql:test', 'root','')||
    die "Error opening database: $DBI::errstr\n";

    print "I think it's connect !.........";

    my $sth=$dbh->prepare(" LOAD DATA LOCAL INFILE
    "/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");

    $sth->execute();
    $set->finish();

    $dbh->disconnect ||
    die "Failed to connect..........";
    $dbh->disconnect ||
    die "Error opening database: $DBI::errstr\n";

    my $result;
    while(<$addr>)
    {
    last if m/^end/gi;
    print "Received : $_";
    print $addr $_;
    $result += $_;
    }

    chomp;
    if(m/^end/gi)
    {
    my $send = "result";
    print $addr "$send\n";
    print "Result: $send\n";
    }
    print "Closed connection\n";

    close $addr;
    print "At your service....Waiting Please !!......\n";
    }


    ================================================== ============
    i just used this below method to import it into mysql, instead of
    mysqlimport...

    my $sth=$dbh->prepare(" LOAD DATA LOCAL INFILE
    "/home/roime/MYSQL_PERL/IN_NETWORK/packet.txt" INTO TABLE flow FIELDS
    TERMINATED BY ',' ");

    But i receive this message when i compiled it..............

    "String found where operator expected at import.pl line 34, near "TERMINATED
    BY ','""
    (Might be a runaway multi-line "" string starting on line 33)
    (Missing semicolon on previous line?)
    syntax error at import.pl line 34, near "TERMINATED BY ','""
    Execution of import.pl aborted due to compilation errors. "

    Do you have any suggestion ?...




    This e-mail and any attachments may contain confidential and
    privileged information. If you are not the intended recipient,
    please notify the sender immediately by return e-mail, delete this
    e-mail and destroy any copies. Any dissemination or use of this
    information by a person other than the intended recipient is
    unauthorized and may be illegal.

    Raymond Guest

Similar Threads

  1. Replies: 0
    Last Post: June 16th, 11:22 PM
  2. import Text file in MySQL table
    By Bob Bedford in forum PHP Programming
    Replies: 8
    Last Post: December 10th, 05:31 PM
  3. Import MYSQL table information from .sql file
    By Brad Brevet in forum PHP Development
    Replies: 16
    Last Post: October 14th, 01:03 PM
  4. Import .sql file into MySQL DB ?
    By Nikolai Chuvakhin in forum PHP Programming
    Replies: 0
    Last Post: August 13th, 06:03 PM
  5. Import text file
    By PSH in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139