LinuxQuestions.org - Import Database Function Script Using Perl??

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Import Database Function Script Using Perl?? (https://www.linuxquestions.org/questions/programming-9/import-database-function-script-using-perl-275020/)

Import Database Function Script Using Perl??

Hello,

I need to build a perl script that will read a user provided text file delimited by <tab> and 'drop' info from 'category' mysql table,then rebuild the same tables based on the imported data from the user text file.
See the sample user text file :cat.txt

-------------------------------------------------------------------------
TYPE<tab>catid<tab>catname<tab>catdescription<tab>CataccesCost
__________________________________________________________________________
CATEGORY<tab>1<tab>Computers<tab>Computer related vacancies<tab>30
CATEGORY<tab>2<tab>marketing<tab>Clerical and assistance vacancies<tab>30
CATEGORY<tab>3<tab>human resource<tab>hr related vacancies<tab>30
___________________________________________________________________________
Any help greatly appreciated.

DominiQue

Any perl guru willing to help??

Code:

#!/usr/bin/perl 



$db = "category";



$sql = "INSERT INTO $db (catid,catname,catdescription,CataccesCost) ";

$sql .= "VALUES('%s','%s','%s','%s')\n";



while(<>) {

    if(/CATEGORY/) {

        @values = split /\t/;

        printf($sql, $values[1], $values[2], $values[3], $values[4]);

    }

}

save it as convert_sql.pl, then (say myfile is the file with separate tab field) :

cat myfile | convert_sql.pl > myfile.sql
Then use any mysql client to connect to mysql server and use myfile.sql as
source script.

mysql> source myfile.sql

Thanks for the great help Cedrik at least i have something thats up and running even though it needs some litlle tweaks which i believe i can be able to handle.

Thanks again !