Import Database Function Script Using Perl??

domquem · 01-07-2005, 09:42 AM

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

domquem · 01-11-2005, 07:48 AM

Any perl guru willing to help??

Cedrik · 01-11-2005, 08:42 AM

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

domquem · 01-13-2005, 09:05 AM

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 !