SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to update Table data with Excel data

    Hi,

    I have data in Excel . these are latest data.

    I want to update mysql table data with Excel data.

    How do I do it ? Please guide

    N.B: I use SQLYog , MySQLAdministrator tool to manage db work. Can these tool be of help for this work. how ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    import the excel data into a table, then update your main table from this imported table using a joined update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    import the excel data into a table, then update your main table from this imported table using a joined update
    what is a joined update ? Do I need any tool to execute joined update ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    what is a joined update ?
    Code:
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;
    source: http://dev.mysql.com/doc/refman/5.0/en/update.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;
    source: http://dev.mysql.com/doc/refman/5.0/en/update.html
    I want to add trim into query....

    here is a bad query ..like this

    UPDATE items,month SET items.price=month.price
    WHERE TRIM(items.id)=TRIM(month.id);


    Can you please tell what would be the correct version of this ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    Can you please tell what would be the correct version of this ?
    i have no idea

    it all depends on how your tables are related

    so far you have not revealed what your tables look like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i have no idea

    it all depends on how your tables are related

    so far you have not revealed what your tables look like
    It may not have much things to do with table structure.


    I'm worried because of this ...

    suppose Excel data contains some SPACES ...and after import this SPACE goes to the CELL.

    I don't want this extra SPACE damage my joined update query. ...now I have two choices to get rid of this space..

    (a) just need a TRIM to work ....I asked the same for this .....This could be a solution also.

    (b) remove SPACE from the EXCELL cell itself so that after import into table there will no SPACE in the CELL.... but unfortunately I don't know how to remove SPACE from Excel cell ....Do you know this ? This could be a solution also

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    (a) just need a TRIM to work ....
    well, in that case, i would use TRIM

    you haven't showed us any data where it didn't work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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