Results 1 to 17 of 17
  1. #1
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Hi,
    I'm a little confused how I would update a database using a datafeed text file or the text file imported into a new access database and using that to update the live database.

    What I want to do is use an update SQL statement over-ride all previous records. There is about 5,000 records and I will need to do this update frequently like once a week as some products might not be stocked anymore or the prices change (from the merchant) etc.

    My Database Table has the same fields as the Datafeed file but has a few more as well. I only have to update one table in my database ("products").

    To keep things simple lets say I want to update the "Products" table that has the following fields:

    ID
    Name
    Description
    Price
    Comments

    The Datafield txt file has these fields (note that it doesn't have the "Comments" field):

    ID
    Name
    Description
    Price

    The SQL Reference I have has the follwing instructions below but I would like to know what statement I would use to do what I want which would also include deleting any records that are not on the new datafeed file & updating new prices etc.

    Any help would be really apreciated thanks,

    rAd


    Create a table
    CREATE TABLE tableName
    (
    columnName1 dataType,
    columnName2 dataType,
    .......)

    Create index
    CREATE UNIQUE INDEX indexName
    ON tableName (columnName)

    Delete index
    DROP INDEX tableName.indexName

    Delete table
    DROP TABLE tableName

    Select
    SELECT columnName(s) FROM tableName

    Select with GroupBy
    SELECT column,SUM(column) FROM table GROUP BY column

    Insert new rows
    INSERT INTO tableName (column1, column2,...)
    VALUES (value1, value2,....)

    Update
    UPDATE tableName SET columnName = newValue
    WHERE columnName = someValue

  2. #2
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    Seems like a lot of effort to me.

    If you're using Access, concentrate on that step because your mysql db will just be a clone of that anyways.

    Write the code to read the datafeed file in to your blank Access file and/or update your existing. The wizards are sort of o.k. for this, depending upon your knowledge of Access.

    Once you've read the datafeed, 'kill' the mysql database and use the ODBC bridge to upload your Access file. Five thousand records should take maybe five, maybe six minutes at the most unless they're gigantic records.

    Once the initial work has been done, this is maybe four-five minutes work a week.

    CodeJockey.

  3. #3
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Hi CodeJockey,

    thanks for your reply, I'm not using mysql db but using a dns less ole-db which is only for a short period and once I start getting some traffic I will update it to SQL Server. I'm using ASP for my coding and I have a web based
    application to do this updating which I downloaded for free from here: http://www.comersus.com/1dbmanager.asp

    I'm just stuck on how to write the correct SQL Statement to do the updating correctly and the application works well as I tested it on a couple of simple statements?

    I did think of just basicaly copying & pasting the records from by blank database into my main database but this would mean downloading it & then uploading it which isn't going to take long but I thought doing it live would be smoother and would also be be better once I have more sites.

    There is a couple of things you mentioned that I'm not that clear about...

    Quote: << Write the code to read the datafeed file in to your blank Access file and/or update your existing. The wizards are sort of o.k. for this, depending upon your knowledge of Access.>>

    I wouldn't know how to do this one, I guess I haven't used that before but I will look into it?

    and Quote: << Once you've read the datafeed, 'kill' the mysql database and use the ODBC bridge to upload your Access file. >>

    I'm not sure what you mean or how I would do that either?

    rAd

  4. #4
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    Ah. Well. I thought that you were using mysql... so used to seeing 'sql' and not used to seeing 'asp'.

    Most of our dbs are Access-based for one reason or another, so with the new information that you've provided, I can now only help up to a point.

    Reading the datafeed: The Import Wizard is adequate enough to read your datafeed and create a new Access db, but it does stuff like labelling the fields like Field1, Field2, Field3. If you haven't written all of this stuff down, sometimes it takes a while to figure out what fields are what data. Once a month usage will probably mean wasting a lot of time translating what column equates to what data field from one month to the next. I prefer to write something that is basically a one-button-solution. The Import Wizard is started from right-mouse-click, Import, etc.

    The ODBC bridge is a piece of software that was developed to allow MySQL to deal with other databases. (ODBC stands for Open DataBase Connectivity. The original ODBC standard was developed by Microsoft... the bridge program was a MySQL creation.) If you have a MySQL db on your server and run Access dbs on your desktop, then the ODBC bridge program will translate "Access data" to "MySQL data" and move the data from your desktop to web-server and reverse. It's downloadable from MySQL home page, easy to use, and takes like five minutes to set up and deploy. It's the main reason why we stayed with Access on our desktops.

    I can't comment on your code that you posted. If your db only has 5,000 records in it, I think you're making it an unnecessarily complex procedure to update the web-accessed database. This is a nothing sized db and if it's just changed once a month, then delete it on your server and upload another one. Five minutes of downtime a month is not going to significantly impact your sites.

    CodeJockey.

  5. #5
    Member
    Join Date
    January 18th, 2005
    Posts
    142
    If you File > Get External data > Import the datafeed into your table, you can tell it to use an existing table, and therefore maintain your column names.

    Before importing a feed I delete the existing rows from the table with a query:-

    Delete * from tablename;

    The other way is to create a linked table to your datafeed text file, you don't need to import it then. In this case always use the same file name for the datafeed text file and keep it in the same folder, then link an access table to it.

    When you retrieve a new datafeed test file, rename the old one to something else, then rename the new one to the name you link to.

    Which is the best way very much depends on what you do with the feed within access.

    I prefer to use a linked table, then, in the case of the CJ feed, use queries to split the feed up into seperate tables for the different merchants. I also clean up the feed with a query, for example replacing "&" with "and".

    In the case of the CJ datafeed (I think I have 75 merchants on that), I then zip it up, upload it to my server, unzip it and then import straight into Sql Server, deleting the rows on the tables in that in the same process.

  6. #6
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Hi CodeJockey & SeventiesMartin,

    Thanks for your feedback & it has made me realize now that I was probably incorrect in thinking I could update my database live with this many records.

    Yes I do use the export utility often and because I have found that the merchant will add new categories & Manufacturers as well as new products then the only way to do this with my set-up is too import the new records. I like SeventiesMartin idea about "linking table to datafeed text file" although I won't be able to do this because I find each new datafeed has new Categories & manufacturers as mentioned above. The reason why this effects mine is that I have other tables in my Database called "categories" & "Manufactures" and if a Product has a new one then I will get an error on Import that lets me know that there are new records in these fields. In my Products table I have relationships to these other tables. I think I have my system nearly worked out now and it is as follows:

    1./ Download New Datafeed text file
    2./ Import the file into a blank database & use the option - "Use first Rows as Field Names"
    3./ Download current live database
    4./ Import the new database (just the one table) into my current database
    5./ Delete all records from my "Products" Table
    6./ Copy all records from new table into "Products" Table
    7./ Delete New imported Table
    8./ Close Databse & Rename as Backup.mdb etc
    9./ Create new blank database
    10./ Import all the data form the database I just updated into this blank one & rename to the current one. (I do this as it shrinks the size down quite a bit.
    11./ Upload Database

    I also found is that the ID of products isn't set back to zero because its an "Autonumber field so I have to export the "Products" table out as well before hand and only import the structure to set it back to zero. SeventiesMartin, where you said to "Delete * from tablename;", will this set the "ID" to zero and how exactly do I do this?

    If anyone knows how I can streamline this process further then please let me know,

    Another thing I wanted to ask is how often do you update your datafeed? I display prices and the merchant says something about making sure it is updated often. I was thinking of doing it once a week but would prefer to do it once a month.
    thanks rAd

  7. #7
    Member
    Join Date
    January 18th, 2005
    Posts
    142
    Delete * doesn't set the ID back to Zero, this doesn't matter for me because I delete all current data and then import everything as if it was the first time, so any relationships using the ID are maintained.

    I do use a different database for each merchant with a different format feed. But all my CJ merchants are in the one database. 75 merchants and one feed format, this is one thing I do like about CJ, sign up to a new merchant, a week later they are included on the feed and everything is already set up.

    If you upload your database to a server using ftp remember to compact and repair it first. Makes a huge difference to the size. Those deleted rows aren't actually deleted, they still take up space within the database until you compact and repair.

    I try and refresh a datafeed at least once a month in any case. Some merchants seem to change prices, products etc. a lot more than others.

    The CJ datafeed I do weekly. That's because it covers a lot of merchants and I've got it set up to be easy to do:-

    1. Download new feed

    2. Unzip and rename (for the linked table)

    3. Run one query (cleans the feed and refeshes the main table, all others just reflect the new data).

    4. Compact and Repair

    5. Zip up the database.

    6. ftp to server

    7. Unzip the database on the server.

    8. Import into SQl Server

  8. #8
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    SeventiesMartin, it sounds like you have a pretty efficient system worked out. I will be using SQL Server in the future as well & maybe CJ & Linkshare datafeeds.

    I see that Compact & Repair will save me having to do 8 - 9 on my system and I'm sure I have used that utility before but must have completely forgot about. Anyway thats a big time saver thanks!

    I think I will update my feeds once a month at this stage as well.

    Now if I can just find a way to make my system a bit easier with the autonumber part.

  9. #9
    Member
    Join Date
    January 18th, 2005
    Posts
    142
    rAd

    Are you able to use the SKU, ProductID etc. as a unique key, then you wouldn't need to worry about the ID.

    For example, on the CJ feeds I use the SKU, other feeds somtimes use a different key name, but they should always be unique for any feed as they identify the product.

  10. #10
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    thats a good idea SeventiesMartin as I only added the "ID" column in myself as an auto number & Primary field so I really don't need it as I could use the SKU instead. The only problem is that I tried this and I found even after updating my code to use SKU that I would get some errors on my ASP pages which I couldn't work out the problem after trying many things. So I had to go back to using ID for now but I might look into this again & see if I can use the SKU, I guess it is something to do with my ASP code or the difference with the database fields from Autonumer (ID) & Text (SKU)?

  11. #11
    Member
    Join Date
    January 18th, 2005
    Posts
    142
    Maybe it's something specific to do with the database you are using.

    Are you sure you need the ID to start from 0 everytime you update the database.

    Again, where I use the ID I just let it start from whatever high number it wants to, because I delete all old rows and import the new data relationships using the ID are maintained.

  12. #12
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by SeventiesMartin:
    Are you sure you need the ID to start from 0 everytime you update the database.
    <HR></BLOCKQUOTE>

    No I don't need it to so I could just let it use high numbers. Maybe I'll just reset it back every few months and it's not that important. Anyway thank for your help & ideas.

    cheers rAd

  13. #13
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Hi, I have manged to use SKU for a Primary ID field now which makes it much better and will be more search engine friendly since these don't change.

    Now I would like some help with the follwing issue please:

    I want to add another field to my Products table called "Reviews" and I will manually add content into this for each product (not all products). How can I update this table using the Datafeed without overwriting the content I have in place for the products that use the new "Reviews" field?

    thanks in advance rAd

  14. #14
    Member
    Join Date
    January 18th, 2005
    Posts
    60
    rAd,

    one way to accomplish the task is:

    1) import new datafeed to new_table
    2) add new column to new_table
    3) update new_table join table on sku
    4) delete table
    5) rename new_table to table

    In english, import datafeed into a 2nd table, update the 2nd table with the first table column, delete the first table and rename the second table to the first table so you can repeat this when the next datafeed comes in.

  15. #15
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Hi markcw,

    thanks for the info but I'm still unsure how to "update the 2nd table with the first table column" and keep the "Reviews" related to the SKU? Do I use a special tool or function in Access to do this? As you know datafeeds will have new or deleted products nearly everytime it's updated and this is where I'm seeing records getting mixed up on each update.

    rAd

  16. #16
    Member
    Join Date
    January 18th, 2005
    Posts
    60
    rad,

    in Access you will need to write an update statement to update the new table from the original table. You will need to join to the original table using the SKU column or primary key. Update will only happen where the SKU column match so deletes or new rows will not matter.

    You will need to read about the update statement and joining 2 tables.

    Syntax similar to this:

    update new_table set newtable.newcolumn = oldtable.newcolumn
    from oldtable
    where oldtable.sku = newtable.sku.

  17. #17
    Newbie
    Join Date
    January 18th, 2005
    Posts
    49
    Ok thanks a lot markcw for the help & info. I'm going to have a go at this in a couple of days and see if I can work this out, its sounds like it will be perfect if I can understand it & will allow me a lot of flexability.

  18. Newsletter Signup

+ Reply to Thread

Posting Permissions

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