Results 1 to 8 of 8
  1. #1
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Datafeed File Management
    Oh where should I start. I get about 25 datafeed files daily from Performics merchants. I also get 2 zipped files that contain a total of 45 or so individual datafeed files from CJ merchants. In addition, I have 25 or so datafeed files from SAS merchants.

    I currently place most of the CJ merchants datafeed files into a single table, which now houses about 900K records. All of the SAS datafeed files were put into individual tables. Finallly, all of the Performics datafeed files were put into a single table, which has about 400K records.

    I need a better system for sure. I am currently working on something new for my site that will require me to query a specific product from some of the combined merchants. About 15 of the 100 merchants I have datafeeds for carry this type of product.

    Trying to run this query on each of the tables would be an efficiency disaster. So, any ideas on the best solution? I was thinking that I could potentially create a new table that contains the datafeeds from just the 15 merchants that offer this item. However, the number of records from these 15 merchants would be rather large still. Maybe I need to read each line of the 15 datafeeds in question and only add lines to the table based on a regex of this product name?

  2. #2
    web dev with whiskers tn-morgen's Avatar
    Join Date
    February 15th, 2007
    Location
    USA
    Posts
    177
    That would be my way of handling things. Files getting too large means they take too long to work/sort and sift and such. There is also the possibility that the file will get corrupted.

    I suggest taking related products from each feed and putting them in a specific DB, and name it with the overall category type. That way, it's easier to remember.

  3. #3
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    A single table is usually the best way to handle multiple datafeeds, especially if you need to be able to access stuff from multiple merchants with a single query.

    You probably want a key structure like "network ID / merchant ID / product ID" or just "my merchant ID / product ID" if you have a unique merchant ID on your end.

    Basically, you just have to create a table structure that covers all of the fields (that you want, anyway) from all of the networks, then figure out how to translate network fields into your "consolidated" table.

    When updating it takes some special coding to handle dropped products. Probably the easiest way to handle it is by having an update status field. At the beginning of an update, set all of the update statuses for that merchant/network to "pending". As you process each datafeed line, change the status for that product to "updated". After you're done, select the remaining "pending" ones and delete them.

    Keep table locking and system load considerations in mind when building the update routines. The last thing you want is for your updates to bring your server to it's knees or have pages timeout or take a long time to load.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  4. #4
    Grandma broke her coccyx! Uncle Rico's Avatar
    Join Date
    May 8th, 2007
    Location
    North Carolina
    Posts
    2,238
    Ok, I got it. I now have a process that reads each datafeed file, does a strstr on the phrase I am looking for and writes lines that match to a new file. When done, I then do a LOAD DATA LOCAL INFILE of the new file into a new table. Works great. The lage loads very quick now given there are under 10K records in the table.

  5. #5
    ABW Veteran Mr. Sal's Avatar
    Join Date
    January 18th, 2005
    Posts
    6,795
    Question
    Quote Originally Posted by SeymourButts
    Oh where should I start. I get about 25 datafeed files daily from Performics merchants. I also get 2 zipped files that contain a total of 45 or so individual datafeed files from CJ merchants. In addition, I have 25 or so datafeed files from SAS merchants.

    I currently place most of the CJ merchants datafeed files into a single table, which now houses about 900K records. All of the SAS datafeed files were put into individual tables. Finallly, all of the Performics datafeed files were put into a single table, which has about 400K records.

    I need a better system for sure. I am currently working on something new for my site that will require me to query a specific product from some of the combined merchants. About 15 of the 100 merchants I have datafeeds for carry this type of product.
    Quote Originally Posted by MichaelColey
    A single table is usually the best way to handle multiple datafeeds, especially if you need to be able to access stuff from multiple merchants with a single query.

    You probably want a key structure like "network ID / merchant ID / product ID" or just "my merchant ID / product ID" if you have a unique merchant ID on your end.

    Basically, you just have to create a table structure that covers all of the fields (that you want, anyway) from all of the networks, then figure out how to translate network fields into your "consolidated" table.
    This thread might be over two years old, but now I am working on something similar to what Seymour asked above...

    I have a different database for each network I get datafeeds, and I have individual tables for each merchant on those databases...

    What I would like to know is, what is the best way to add all of my merchants datafeed from just one network, into a single table...

    For example, on SAS I can get my merchants feed into one table with a code like this one:

    // path to remote file
    $remote_file = '/1234/1234.txt';
    $local_file = "../feeds/1234.txt";
    // open some file to write to
    $handle = fopen($local_file, 'w');
    if (ftp_fget($conn_id, $handle, $remote_file, FTP_ASCII, 0)) {
    echo "successfully written to $local_file";
    } else {
    echo "There was a problem while downloading $remote_file to $local_file\n";
    }
    ftp_close($conn_id);
    fclose($handle);
    $Feed = "$local_file";
    $temptable = "merchantname_temp";
    $maintable = "merchantname";

    // feed data

    $feed = fopen($Feed, 'r');
    $rowNum = 0;
    $recCount = 0;
    while($data = fgetcsv($feed, 3000, "|")){
    if($rowNum > 0){ //etc, code...
    But what I would like to know is how to add more SAS merchants feed to that same table, using the same script...

    I know it may have something to do with looping thru the other datafeeds, but I still getting stuck with my code, so...

    Do anyone here would like to share the piece of code I need, in order to add a few SAS datafeeds, to just one table?

    TIA!

  6. #6
    Full Member iolaire's Avatar
    Join Date
    October 3rd, 2006
    Location
    Arlington, VA
    Posts
    229
    Quote Originally Posted by Mr. Sal
    But what I would like to know is how to add more SAS merchants feed to that same table, using the same script...
    Sorry no code sample, but I recommend loading all tables into a temp table then inserting it into your master table.

    My process is
    1. Load vendor file into temp table whose layout is the same for all programs, so you have some work converting the various layouts, sometimes this is a two table process
    2. Do all your updates relating to is in stock etc... on the temp table
    3. Delete those vendor's old records from master table
    4. Insert vendor's records into master table.

  7. #7
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    Create an array of merchants, then loop through the array. This way you only have to add the merchant number,

    $merchants = array(12345,12346);

    then your file will be at

    /$merchants[0]/$merchants[0].txt - Itís not written correctly but you should get the point.

    I would be careful with using that because not all merchants us the feed the same way. I have found it best to give each merchant their own script.

    As for the database, I have a single database with multiple tables.

    network
    merchant
    product
    join tables
    extend as needed. Donít get too table happy though as your queries can get long & complicated.

    read up on database normalization.

  8. #8
    ABW Veteran Mr. Sal's Avatar
    Join Date
    January 18th, 2005
    Posts
    6,795
    iolaire and Wayder,

    Thank you for the ideas.

    I'm working on it!

  9. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. How often datafeed file get updated?
    By smallfish in forum BettyMills
    Replies: 0
    Last Post: November 23rd, 2009, 07:35 PM
  2. Replies: 4
    Last Post: June 23rd, 2009, 07:39 PM
  3. zip file datafeed file transfer into database
    By rahulcbr in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: August 28th, 2007, 04:59 AM
  4. How do I use a .aspx datafeed file?
    By rAd in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: June 20th, 2005, 12:22 PM
  5. Problems with slow file management using Windows Explorer
    By Baertracks in forum WebMerge (Fourthworld.com)
    Replies: 3
    Last Post: December 1st, 2004, 08:06 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
  •