Results 1 to 15 of 15
  1. #1
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    ~Hola

    I am in need of a perl snippet.

    I have a short perl file that grabs a datafeed and drops it on my server.(thanks C4!)

    I need a chunk o' code that will read the txt file and insert it into the mysql db.

    If someone can sketch out the framework for me, I can flesh it out. I've got a O'Reilly perl book, and I've done some reading on using DBI & using DBIx::Recordset, but me brain is spinning!! If someone would hold my hand and give me the framework, I'd be eternally grateful!

    I'm looking for:

    read the txt file (tab delimited, first row is field names)
    open connection
    do while !EOF
    INSERT
    next
    close connection

    Thanks from a Perl rookie!

    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  2. #2
    Full Member c4's Avatar
    Join Date
    January 18th, 2005
    Posts
    488
    I don't know why I keep helping my concurrence, I must be nuts

    Use this to create the table:
    create table listings (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    number CHAR(15) NOT NULL,
    url CHAR(255) NOT NULL,
    image CHAR(255) NOT NULL,
    price FLOAT(8) NOT NULL,
    discount FLOAT(8),
    category CHAR(80) NOT NULL,
    title CHAR(255) NOT NULL,
    description TEXT NOT NULL,
    metal CHAR(30),
    stone CHAR(30)
    );

    Copy this and save it as a perl file ("datafeed.cgi"):

    #!/usr/bin/perl
    use CGI::Carp qw(fatalsToBrowser);

    $database_name = "database_name";
    $database_user = "username";
    $database_pass = "password";
    $file = "datafeed.txt";
    $url = "http://affiliates.mondera.com/affiliates/access.asp?file=path/datafeed.txt";
    $delimiter="\t"; # \t = (TAB)

    use DBI;
    $i=0;
    $num=0;
    $j=0;

    use LWP::Simple qw(getstore);
    $execute = getstore($url, $file);
    $dbh = DBI->connect( "dbi:mysql:$database_name", "$database_user", "$database_pass", {RaiseError=>1,AutoCommit=>1} ) or die("Can't access database");

    open(TEMP, $file) or die("Can't open datafeed file");
    while ( <TEMP> ) {

    # To save server resources we'll stop the script for 2 seconds every 50 lines
    if($j==50){sleep(2); $j=0; $num++;}

    chomp;
    # Escape the quotes
    $_ =~ s/"/\\"/g;
    ($number,$url,$image,$price,$discount,$cat,$short,$long,$metal,$stone) = split(/$delimiter/,$_);

    # insert everything into the database
    $sth = $dbh->do(qq{INSERT INTO `listings` (`id`,`number`,`url`,`image`,`price`,`discount`,`category`,`title`,`description`,`metal`,`stone`)
    VALUES ('',"$number","$url","$image","$price","$discount","$cat","$short","$long","$metal","$stone")}) or db_err("Unable to execute query", $dbh->errstr);

    $i++;
    $j++;
    }
    close(TEMP);

    print "Content-type: text/html\n\n<p>$i fields inserted, slept $num times.</p>\n";
    exit;



    Change the $url, $database_name, $database_user and $database_pass to your info.

    If you have problems with inserting price and discount, try changing SQL:

    price CHAR(10) NOT NULL,
    discount CHAR(10),

    Now, this script will insert everything in the database, but only the first time. When you update database you will have to either change the SQL statement to UPDATE or empty your database and re-fill it with this script.

    This is as far as I can help you with this one. few more posts like this and scripts4yoursite.com will be out of work



    YOURsoft affiliate software
    Join YOURsoft "affiliate program for affiliate software"!

  3. #3
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    Holy Cow!

    Way cool..

    MUCH more help than I was expecting..

    Thank you very much!

    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  4. #4
    ABW Ambassador FFoc's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,015
    Wow.. That's alot of work..

    My download and import looks something like this (two files -- first one run from cron every x days):
    df-update script:
    <pre class="ip-ubbcode-code-pre">
    #!/bin/sh
    cd /path/to/web/data/storage
    wget -q -O http://merchant.com/path/to/datafeed.txt
    mysql -u webuser -ppass &lt; populate_db
    </pre>

    populate_db (assumes tab delimited, no unique field):
    <pre class="ip-ubbcode-code-pre">
    USE df_site_db;
    DELETE FROM df_site_db.df_table;
    LOAD DATA INFILE '/path/to/web/data/storage/datafeed.txt'
    INTO TABLE df_site_db.df_table
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n';
    </pre>

    If you are using a feed with a guaranteed unique field (sku or whatnot) you can ditch the DELETE statement and use the REPLACE tag on the LOAD statement..

    --
    "The greatest good you can do for another is not just to share your riches, but to reveal to him his own." – Benjamin Disraeli
    --
    Ford Fox-body Owners Club -- http://www.ford-fox.org

  5. #5
    Full Member c4's Avatar
    Join Date
    January 18th, 2005
    Posts
    488
    You're welcome

    FFoc:
    Well, he did say Perl script Maybe a little more code (true I could shrink it but then it wouldn't be as easy to read) but will do a quicker job then any PHP script ... plus is more easy to edit if the database doesn't just contain the fields that datafeed does.



    YOURsoft affiliate software
    Join YOURsoft "affiliate program for affiliate software"!

  6. #6
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    Remember the Perl mantra: "There's more than one way to do it.".... and this way worked great.

    C4 - you were right, I had to change price & discount to char... no problem as I'm just displaying those.. or not, havent decided yet!

    Thanks again for your help, now I have to go figure out pagination!

    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  7. #7
    Full Member c4's Avatar
    Join Date
    January 18th, 2005
    Posts
    488
    "There's more then one way to do it" ... very true for Perl, but can be a pain in the a** when you have to read larger scripts which you wrote long ago...

    Yea, I thought price and discount will be a problem since Mondera uses a "," in the prices.

    Good luck with your page!

    Oh, BTW, don't be so thrilled about me helping you, when your site becomes successful and profitable I'll sue you for % of profits



    YOURsoft affiliate software
    Join YOURsoft "affiliate program for affiliate software"!

  8. #8
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    Ha! I'm far enough behind you that you wont have time to sue me... You'll be spending so much time counting your gold you will have forgotten all about me!

    Thanks again for your help!

    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  9. #9
    ABW Ambassador FFoc's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,015
    Who said anything about PHP? That thar is one of them old-fangled "shell scripts"...

    AND, there's nothing to edit in the script when the new feed has different fields than the old..

    You can do numeric out of the ","-type fields nooooo problem...

    Just change the line "FIELDS TERMINATED BY" to these:
    <pre class="ip-ubbcode-code-pre">
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"'
    ESCAPED BY '\\'</pre>
    --
    "The greatest good you can do for another is not just to share your riches, but to reveal to him his own." – Benjamin Disraeli
    --
    Ford Fox-body Owners Club -- http://www.ford-fox.org

  10. #10
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Guys, cool stuff.

    Here are some rainy-day scenarios to consider...

    What happens if a product is removed from the feed? If you just insert/replace the new data and rely on primary keys to sort things out, you end up with a bunch of discontinued products on your site.

    FFoc handles this with a dump and reload strategy:

    DELETE FROM df_site_db.df_table;
    before loading the new data

    But then what if the feed is broken and none of the rows load? Or what if there's no feed at all or the server's unreachable?

    You've tossed the data, have no new data, and will end up with an empty site. And google's crawling...

    And even if things all work perfectly, your site is empty for however long it takes to load the new data (which for thousands of products on a text indexed table can take many minutes).

    Check the files to make sure they exist.
    Check the data to make sure it's well formed.
    Make sure a large enough set of products loaded such that you're comfortable things are ok.
    THEN remove the old data.

    You can do that with an "updated" column in the database that you touch when you load new data and later delete anything that wasn't updated.

    --scott

    --
    scott@befree.com

  11. #11
    ABW Ambassador FFoc's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,015
    My download script saves a copy of the last feed and tests to make sure wget got something, etc.. Also sends me email when it gets an error.. I wrote the above by hand instead of copying mine in verbatim, and it was also late, lol..

    My point was simply that it can be handled in a very short, quick manner, rather than requiring a long, complicated script that takes more than a single page to print out..

    --
    "The greatest good you can do for another is not just to share your riches, but to reveal to him his own." – Benjamin Disraeli
    --
    Ford Fox-body Owners Club -- http://www.ford-fox.org

  12. #12
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>I wrote the above by hand instead of copying mine in verbatim, and it was also late, lol..<HR></BLOCKQUOTE>
    Then I'm even more impressed.

    Obviously you know what you're doing. Too many others drop these things into cron jobs without error checking and end up with blank sites for days on end because of an upstream glitch.

    Murphy's law definitely applies here.

    --
    scott@befree.com

  13. #13
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    DEFENSIVE_CODING := TRUE



    Thanks for everyone's help... I appreciate it greatly!



    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  14. #14
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    &gt;&gt; DEFENSIVE_CODING := TRUE

    := colon equals? Is that Pascal? Smalltalk? You're dating yourself.

    --
    scott@befree.com

  15. #15
    Member
    Join Date
    January 18th, 2005
    Posts
    101
    heh... no comment..



    Mel
    Goal: $4500 per month by Christmas, 2003
    Sunset Beach Trading Company - Financially Free in 2003

  16. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Perl Help...
    By eggerda in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: July 22nd, 2003, 05:40 AM
  2. ASP Source Code "Translation" to Perl
    By bevmoam in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: April 2nd, 2003, 09:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •