SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is it pos to import data from another type of database such as Excel?

    Hi All,
    I'm quite a novice with SQL databases, so please excuse me if this has been covered before.

    I have data in .xls files and I would like to load it into my tables in my MySQL database.

    Is there a quick way to do this via import, or something similar?

  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)
    save the excel sheet as a CSV file, then use the mysql LOAD DATA INFILE command (you may need to look up the syntax)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    save the excel sheet as a CSV file, then use the mysql LOAD DATA INFILE command (you may need to look up the syntax)
    Thank you, I'll try that tomorrow

  4. #4
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've found the syntax for LOAD DATA INFILE:

    LOAD DATA INFILE Syntax

    LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
    ]
    [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

    But, at present I'm not confident about what values to enter. 'file_name' is obvious, but I don't know enough about working with MySQL to fill the rest in.

    However, you have answered my question, and it's good to know that this time saving procedure is pos.

    Thank you r937

    PS I've got your book and I love it and I'm working my awl through it.

  5. #5
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PPS
    If you have time to help me through the syntax above that would be great, but it's a lot to ask and if that's not pos I understand.

  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)
    it is a lot to ask, especially since i've only done it twice

    but the syntax is really easy, you should be able to put together a test case in minutes

    if it fails to load properly, i can help you with error messages or incorrect results etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, many thanks, I'll have a go at it,

    Cheers

  8. #8
    SitePoint Enthusiast cheekyboy's Avatar
    Join Date
    Apr 2010
    Location
    Yorkshire, England
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it is a lot to ask, especially since i've only done it twice

    but the syntax is really easy, you should be able to put together a test case in minutes

    if it fails to load properly, i can help you with error messages or incorrect results etc.
    Well, I had a go at this but did have errors and it failed to work. I didn't really want to trouble you as I feel I don't really know what I'm doing.

    While I was sulking at my failure, I had a look around in phpMyAdmin and saw the import button, tried it and wonderful, it imported my csv file.

    I had to change the database and table names which was easy and bingo, database and table created.

    Thank you for you help and encouragement

  9. #9
    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)
    encouragement is contagious... pass it on
    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
  •