------=_NextPart_000_002B_01C3F876.D719D690
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



Hi,

I am dropping this email because I have already tried all possible ways. =


Goal: Migrate Excel to Mysql using perl .=20

Howto: I have save the xls file as CVS ( comma - separate ) format =
like:=20

i.e:
ELTID,Telecom ID,ELTID8,ELTID Greek ATX (Cook),682-60127,Greece-->Cook =
Island,GR,27-Apr-98

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++
perl program:

#Initialization of Libraries needed it.
use DBI;
use DBD::mysql;

#Initialization of database

my $table=3D"dnis";
my $db=3D"stats";
my $user=3D"user";
my $pass=3D" password";
my $sth2;
my $i=3D0;
my $sqlrecords;
my $export_sql;
my $source=3D"/home/canario/eltid.txt";



#$LOGFILE=3D"ELTID.csv ";


# Tell the script that we will use Mysql database

my $drh=3DDBI->install_driver('mysql');

# Establish connectin with the database

my $dbh=3D$drh->connect($db,$user,$pass);

# A simple check to see if we connect6

if (!$dbh) {

print "cannot connect: $DBI::errstr<br>";
die;
}

open(LOG,$source) or die("Could not open log file.");

foreach $line (<LOG>){
#while (<LOG>) {
my ($ipcode, $ip_name, $prod_id, $prod_name, $actual_num, $rate_desc, =
$country, $updated)=3Dsplit(/,/,$line);

print "column: $i\n";
print "ipcode:$ipcode\n";
print "ip_name:$ip_name\n";
print "prod_id:$prod_id\n";
print "prod_name:$prod_name\n";
print "actual_num:$actual_num\n";
print "rate_desc:$rate_desc\n";
print "country:$country\n";
print "updated:$updated\n";

$i++;

#if ipcode is not null then -> Export the data to the Mysql database

#if ($ipcode !=3D"") {
$sqlrecords =3D "insert into =
$table(ipcode,ip_name,prod_id,prod_name,actual_num ,rate_desc,country,upda=
ted) values =
('$ipcode','$ip_name','$prod_id','$prod_name','$ac tual_num','$rate_desc',=
'$country','$updated')";

print "$sqlrecords\n";
# }

#$sth2=3D$dbh->prepare($sqlrecords);
$sth2=3D$dbh->do($sqlrecords);
#$export_sql=3D$sth2->execute();


}

close(LOG);

$sth2->finish;
$dbh->disconnect;

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++=
++++++++

Description of program:=20
It read from cvs file row by row and it goes insert the data read from =
file into mysql table. Just a simple loop with single query.

I have checked the log file from Mysql and I can see 700 insert queries =
, but , when I check the table there is ONLY A SINGLE ROW ENTRY - REST =
OF 699 ROW OF DATA HAS NOT BEEN INSERTED ....WHY???



Please HELP!!

Thanks in advance.

Cheers,

Vic
------=_NextPart_000_002B_01C3F876.D719D690--

--Alt-Boundary-3354.1914693
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

you don't need a script for this.

use

load data local infile <file> into table <table>

see

http://www.mysql.com/documentation/m...ial.html#Loadi
ng_tables

if you still want to use your script, then maybe

while ($line = <LOG>)

is better than

foreach $line (<LOG>)


Uli


On 21 Feb 2004 at 12:32, Victor Alamo wrote:



--Alt-Boundary-3354.1914693--