[Perl]read text file with delimiter and insert into mysql table in perl 5.10

Question

I have text file(employeedata.txt) with values like this :

ABC#VVV#JHY#ABC#VVV#JHY#ABC#VVV#JHY#ABC#VVV
BBN#NJU#NULL#ABC#VVV#JHY#ABC#VVV#JHY#ABC#OLJ
ABC#BYR#MPL#ABC#VVV#JHY#ABC#TGB#JHY#ABC#NULL
NMP#JDC#NULL#ABC#VVV#JHY#ABC#XCD#JHY#ABC#NULL
UJK#SAB#NULL#ABC#VVV#JHY#ABC#NBG#JHY#ABC#MPL

my text file contains 5,000 lines and I have a Table called Employee with values like this:

id|EmployeLastName|EmployeFirstName|EmployeeAddress

In my file text, in each line, i have EmployeLastName in the first position, EmployeFirstName in the fourth position, EmployeeAddress in the last position

Example :

EmployeLastName#VVV#JHY#EmployeFirstName#VVV#JHY#ABC#VVV#JHY#ABC#EmployeeAddress

Now I want to read text file line by line and insert into table Employee by using perl 5.10.

I am a novice in perl. How can do it?

First you need perl and mysql see https://www.perltutorial.org/perl-dbi/ and then you need the mysql script https://stackoverflow.com/questions/13579810/how-to-import-data-from-text-file-to-mysql-database — nbk, Nov 21 '19 at 00:18

Polar Bear · Answer 1 · 2019-11-21T01:15:30.423

Well you need to do some reading on DBI driver to get a grip on the code provided bellow -- it is best invested time to work with DB.

NOTE: in this piece of code I read data from internal block __DATA__

use strict;
use warnings;

use Data::Dumper;
use DBI;

my $debug = 0;

my @fields = qw(id last first address); # Field names in Database
my(%record,$rv);

my $hostname = 'db_server_1';           # Database server name
my $database = 'db_employees';          # Database name
my $table    = 'db_table';              # Table name
my $port     = '3306';                  # Database port [default]

# Define DSN
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
# Connect to Database
my $dbh = DBI->connect($dsn, $user, $password, {RaiseError => 1});
# Define query
my $stq = qq(INSERT INTO $table (id,last,first,address) VALUES(?,?,?,?););
# Prepare query
my $sth = $dbh->prepare($stq);

$dbh->begin_work();                     # Ok, we will do insert in one transaction

my $skip = <DATA>;                      # We skip header in data block

while( <DATA> ) {
    @record{@fields} = split /#/;       # Fill the hash with record data

    print Dumper(\%row) if $debug;      # Look at hash in debug mode

    $rv = $sth->execute(@record{@fields});  # Execute query with data

    print $DBI::errstr if $rv < 0;      # If error lets see ERROR message 
}

$dbh->commit();                         # Commit the transaction
$dbh->disconnect();                     # Disconnect from DataBase


__DATA__
id#EmployeLastName#EmployeFirstName#EmployeeAddress
1#Alexander#Makedonsky#267 Mozarella st., Pizza, Italy
2#Vladimir#Lenin#12 Glinka st., Moscow, Italy
3#Donald#Trump#765 Tower ave., Florida, USA
4#Angela#Merkel#789 Schulstrafe st., Berlin, Germany

You can read data from a file with following code

use strict;
use warnings;

my $debug = 1;

my @fields = qw(id last first address); # Field names in Database
my(%record);

my $filename = shift
    or die "Provide filename on command line";

open DATA, "< $filename"
     or die "Could not open $filename";

while( <DATA> ) {
   @record{@fields} = split /#/;

   print Dumper(\%record) if $debug;
}

close DATA;

As you very fresh in Perl programming then you probably should start from Learning Perl, then move on Programming Perl, when you get in trouble visit Perl Cookbook and if you decided to dive into database programming Programming the Perl DBI

NOTE: if your table has fields with specification **uniq** you will get errors on duplicate record insertion. You need to do some reading on DB table creation https://dev.mysql.com/doc/refman/5.5/en/create-table.html (please keep in mind that there might be difference between versions of MySQL -- look into documentation for your version to avoid confusion) — Polar Bear, Nov 21 '19 at 01:04

score 1 · Answer 2 · answered Nov 21 '19 at 00:56

1

Well it is nice to know Perl and DBI driver programming. But in your particular case you could load data from a file directly by utilizing MySQL commands.

Loading Data into a Table

Sometimes it is much much easier than it looks at first sight.

answered Nov 21 '19 at 00:56

Polar Bear

6,762
1
5
12

[Perl]read text file with delimiter and insert into mysql table in perl 5.10

2 Answers2