Excel to MysSQL

Discussion in 'Databases' started by Deepprogrammer, Nov 1, 2012.

  1. #1
    Good day
    I have a prospective web client who needs a website db to display data that he changes constantly (weekly) on an excel spreadsheet. He's going to be updating this data "on the fly" while visiting from business to business, so an internet connection is not the most reliable; he will have an ipad with excel doc that he directly types into. He does save it to skydrive.

    His idea is that the website db "grabs" the data constantly from the excel doc stored on skydrive. I told him that most likely was either a.) not possible, or b.) not the simplest solution. I am guessing that some sort of "import" tool would work better.

    What would the simplest way be to solve this issue? Is there a simple script that allows for importing excel into Mysql db? Thank you!
     
    Deepprogrammer, Nov 1, 2012 IP
  2. graformix

    graformix Member

    Messages:
    92
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #2
    Hello,

    IMO, the best way, at first is to migrate Excel data to MySQL. For example have a look at DBConvert for Excel to MySQL
    1. Then update the Excel spreadsheet weekly OR
    2. MySQL as back end -> Web service which interacts with MySQL -> web service passes the data from MySQL to PHP Web form -> iPad safari reads/writes data from the web site.
     
    graformix, Nov 2, 2012 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    For $100 I would be far more inclined to use Navicat which is an amazing program that now works with most DB's.

    Otherwise, do a google search. There are numerous tutorials on how to export to a text or csv file which can then be directly opened or imported into Excel.
     
    jestep, Nov 2, 2012 IP
  4. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #4
    You can use a PEAR library to read an Excel spreadsheet, or you can use http://www.phpclasses.org/package/6279-PHP-Parse-and-retrieve-data-from-Excel-XLS-files.html.

    Getting the files from Skydrive is as simple as getting them from the drive mapped to Skydrive. Use cron to run a script every week to get the file(s) and copy it (them) into the database. The only problem is finding out when the latest time/day your client will be uploading the files to Skydrive. If you can move them, say to a subdirectory of his Skydrive directory, that would make it easier. Run the script every day. If there are no files, exit. If there are files, run the script, then move the converted files to the subdirectory.
     
    Rukbat, Nov 2, 2012 IP
  5. aGnium

    aGnium Peon

    Messages:
    27
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Wow, this could come in handy if you could pull this project off, having synchronized a clients excel work to MySQL DB...
     
    aGnium, Nov 21, 2012 IP