All this to parse and insert a flat file. If you look through it, you can
probably get it down to less than 10 lines. This is perl. No BEGIN block
is needed, use does the same thing in or outside of it. Other stuff is also
extra, and where is 'use DBI', it says use database driver, actually it's
use DBI and then define the db driver in connect. Sorry I didn't mean to
come at this code like this, but I just don't think that long of a coded is
needed.
Ilya Sterin
-----Original Message-----
From: James Maes
To: David Jacobowitz; [EMAIL PROTECTED]
Sent: 3/24/01 8:09 PM
Subject: Re: Need to help with flat file
If there was magic, there would be no need for programmer.
see code below, note this was typed in an email client and never ran.
Debuging is left to the local warlock.
=== begin magic code ===
#!/usr/bin/perl
BEGIN
{
use XXX.pm ## replace XXX with the DBI Driver you
are using ##
};
#######################
## define our namespace ##
#######################
package magicLoader;
my($fileToLoad,$fileData,$dbh,$sql,$st1);
$fileToLoad = "XXX"; ## replace XXX with the name of the file
##
##############################################
## load file into memory, if it is a large file, dont do this ##
## iterate over it sax style ##
##############################################
open(FH,$fileToLoad) || ##INSERT ERROR CODE##
while($line = <FH>)
{
###########################
## build a hash of the file data ##
###########################
if($line ne "end-of-user-record")
{
push(@{$fileData->{$cnt}},$line);
}
else
{
$cnt++;
}
}
close(FH);
#####################
## insert data into table ##
#####################
$dbh = XXX ## replace XXX with the code to init a DBI
connection
$sql = "INSERT into myTable (Col0,Col1,Col2,Col3,Col4,Col5,Col6) VALUES
(?,?,?,?,?,?,?);";
################################
## create the sql statement and run it ##
################################
foreach $row(keys %{$fileData})
{
my($cnt=0);
$st1 = $dbh->prepare($sql) || ##INSERT ERROR CODE##
#####################################
## bind the params to the statement handle ##
#####################################
foreach $item(@{$fileData->{$row}})
{
$st1->bind_param($cnt++,$item) || ##INSERT ERROR CODE##
}
$st1->execute() || ##INSERT ERROR CODE##
}
$dbh->disconnect();
=== end magic code ===
On Saturday 24 March 2001 20:45, David Jacobowitz wrote:
> Hi,
>
> I have stored our web visitors input in a flat file. My form has 7
fields.
> Each field values are stored in new line and at the end of the record
I
> simply printed end-of-user-record.
>
> I have constructed a table in MySQL and would like to upload these
data in
> my database. But there are many records, I need to convert each
records
> into rows. Can any one tell me if there is any magic perl code that
will
> convert my flat file data records into rows.
>
> Greatly appreciate any comments or suggestions.
>
> David
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
--
------------------------------------
|| James Maes
|| Senior Programmer
|| [EMAIL PROTECTED]
|| The Sporting News
|| www.sportingnews.com
|| fantasy.sportingnews.com
------------------------------------
