best way to manage mysql database

Discussion in 'MySQL' started by luigi777, Jul 7, 2013.

0
  1. #1
    I have an excel table which I imported into mysql, however the excel file will be updated regularly with fields changing values and rows being added.

    Now what is the easiest way to keep the mysql database up to date? I tried linking with ms access but it only adds the table as new lines. How can I update?

    thanks!
     
    luigi777, Jul 7, 2013 IP
  2. convertin

    convertin Greenhorn

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #2
    Hi! There's no way to do this without manual coding. MS Excel does not have unique indexes, so it's hard to find what has been changed. But if you know some usefull particularity of the data (e.g. first column is incremented unique ID), you can write a simple program in your favorite language that looks for changes the XLS file and write them into MySQL using ODBC connection. Hope, this will help
     
    convertin, Jul 9, 2013 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,832
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #3
    Why do you need the excel spreadsheet anyway. Can't you do it all via online forms?
     
    sarahk, Jul 10, 2013 IP
  4. o_iSniPe_xX

    o_iSniPe_xX Well-Known Member

    Messages:
    482
    Likes Received:
    44
    Best Answers:
    0
    Trophy Points:
    155
    Digital Goods:
    1
    #4
    You will need some type of custom script I believe.
     
    o_iSniPe_xX, Jul 10, 2013 IP
  5. Original Hosting

    Original Hosting Active Member

    Messages:
    203
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    50
    #5
    I would advise using PHPMyAdmin, not sure what it allows you to import though.
     
    Original Hosting, Jul 11, 2013 IP
  6. vineld

    vineld Greenhorn

    Messages:
    53
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    18
    #6
    PHPMyAdmin will not help you. You need a script to handle the Excel file. If you know how to code yourself, this is what you should do every time you deal with the updated version:

    1. Read every row from the Excel file
    2. Check to see if the row is new or existing in the MySQL database. To be able to do this properly you will need to find at least one constant field in the Excel file. If there isn't one you should consider adding one.
    3. Add / update database accordingly

    You could add some value sanity checks also if you wish. Also consider the suggestion to not use Excel at all if you don't really have any use for it.
     
    vineld, Aug 29, 2013 IP