1

I'm running an eCommerce store and I am being provided with a daily data feed of info like Item Number, Price, Description, Inventory Status, etc.

Some notes:

  • I know the URL of the .xls file
  • I need to modify the Item Number on the .xls for all products to add two letters to the beginning
  • Price and Inventory Status on the website database need to be updated daily for each item, matched up by Item Number
  • If Item Number does not exist, new item is created with all information contained in the excel sheet
  • This all needs to be fully automated (this is the part I need the most assistance with)

I used to have a company that took care of this for $50/month, but I now have access to the data myself. My programming experience is limited to web languages (PHP, HTML, etc.) and some basic C++. A side question would be whether or not it's worth taking this responsibility upon myself or if I should continue working with a company who has systems in place to handle this already.

Nick
  • 8,822
  • 6
  • 41
  • 65

2 Answers2

2

If you can get the CSV instead of the XLS, load it yourself into a new table, update what you need and then insert the rows into your production table.

If you're stuck with the XLS, find a library for PHP that will allow you to parse it and then write the records to the new table.

As for your second question, yes, it's absolutely worthwhile to cutout the thieves who are charging you $600/year for something that should take you an hour or two to write yourself.

Good luck.

Alain Collins
  • 15,596
  • 2
  • 29
  • 53
  • I changed it so that it gives me a delimited text file, with the delimiter being a comma. However, uploading the data is not too much of an issue - the important part of what I need to do figure out is daily automation. I don't personally know how to create an automated process to do these things. – Nick May 04 '11 at 20:07
0

There are two suggestions here. One involves using mysqlimport, the other TOAD. If you need more explanation of how to implement this, expand your question.

Community
  • 1
  • 1
wallyk
  • 53,902
  • 14
  • 79
  • 135
  • Thanks for the suggestion. One thing I didn't emphasize properly is that it needs to be an automated process. I'm more or less familiar with the downloading and uploading process, but the automation itself is the problem. – Nick May 04 '11 at 20:59
  • If you have a Linux server, schedule a periodically run job through `cron`. For Windows use the Windows Task Scheduler. See http://stackoverflow.com/questions/132971/what-is-the-windows-version-of-cron – wallyk May 04 '11 at 21:15