I need to write a program or script or something that will allow me to update a database with as little effort as possible. Mainly I need help determining what language, products or tools I should look at that will help me to accomplish this goal. One thing to keep in mind is I am planning on building a retail website of my own at some point in the future so if it is possible to learn one thing and kill two birds with one stone then great, if not then the main thing at this point is to write the program to update my database. At the moment I am manually updating the DB with about 150-200 sales a month, not a lot but it is growing and I can see the need to automate as much as possible in order to grow in the future.
Maybe I am expecting too much and am way off base, biting off more than I can chew or there is a better and easier way than what I am trying to do but I am sure it can be automated, please let me know. Also maybe I need to use something other than Access for the DB to make it easier to do this, I’m open to any suggestions. If needed I could update everything I have now and cut over to a new DB system for future items and sales.
The database is a Access 2007 DB. The business is a retail site in Japan that is similar to Amazon where different sellers can list their items under their account. I think there are two possible options. One is to communicate directly with the web page and update the database and the other is to use a excel spread sheet with sales listed and update the Access DB with that info. I update the Access DB with the inventory number and details before it even goes on the site so the data is there but needs to be updated when the items sells.
The main criteria I need is when the items sell is… the amount it sold for, fee’s, the inventory number of the item, and date sold. Maybe a few more but these are the main things.
Option 1: Update from website.
When a item is listed it can be access from a web link like www.site.com/ME/XYZ123 (XYZ123 is the item number) and the basic info I need is listed in different areas as text and numbers. One possible problem is text is in Japanese although I could probably translate the page.
What I would need to do is pull info from this page and input it into a corresponding record in the database based on the item number. Like take the date sold and input it into the DB for that specific item. And even maybe even a check mark field so I know it’s been updated.
Option 2: Export sales records to a Excel spread sheet and then pull from that data to update the DB. On one side it seems like using the Excel file is the easiest way to do it.
I can’t really import the Excel data into the Access DB since the records are already there for inventory. The row headers are in Japanese but it would be very easy to download a month of sales and manually update the headers.
I would need to pull xx data from item xyz123 from the excel file and input the data into the correct field in the DB for item number xyz123.
What I am aiming for is something I can simply run and it will go out and grab the data from the site or a pre-prepared excel file and update all of the records in the DB instead of manually checking the site or sheet and manually updating the info in the DB. This will make my life much easier going into the next year if I can work it out.
Thanks for any help and advice you can offer.