1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Import an Excel File into MySQL database..

Discussion in 'MySQL' started by whiteseoparrot, Jul 14, 2006.

  1. #1
    I want to import an excel database into MySQL

    Can this be done...

    And if yes, how?

    Arthur.
     
    whiteseoparrot, Jul 14, 2006 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Use phpMyAdmin. Save Excel as CSV, then use phpMyAdmin's import function.
     
    T0PS3O, Jul 14, 2006 IP
  3. whiteseoparrot

    whiteseoparrot Peon

    Messages:
    5
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3

    When I tried it, it dumped all the fields into ONE field in the mySQL. Is there a format for the first line in CSV file to define fields?

    Arthur.
     
    whiteseoparrot, Jul 14, 2006 IP
  4. dddougal

    dddougal Well-Known Member

    Messages:
    676
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    108
    #4
    You didnt change the escape character from a ; to a , i thinks.....loading data into mysql from a CSV file is the industry standard for affiliate catalogues and things.
     
    dddougal, Jul 15, 2006 IP
  5. jalika

    jalika Peon

    Messages:
    126
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    jalika, Jul 15, 2006 IP
  6. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #6
    sacx13, Jul 17, 2006 IP
  7. mipa jim

    mipa jim Peon

    Messages:
    261
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #7
    I import my excel file into access then export the file from MS access to txt file and use a |pipe to seperate fields. It works pretty well for me.
     
    mipa jim, Jul 24, 2006 IP
  8. esnstudio

    esnstudio Peon

    Messages:
    12
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #8
    You can also use LOAD DATA INFILE Syntax to load flat files into MySQL tables.

    For example:

    LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';
     
    esnstudio, Jul 28, 2006 IP
  9. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #9

    if you are under a windows system and is not working :) try to replace '\n' with '\r\n'
     
    sacx13, Oct 20, 2006 IP
  10. Affector

    Affector Peon

    Messages:
    32
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    LOAD DATA INFILE - does not work on Netfirms, great $10 per year for 1 GB/750 GB, but realy very hard to upload even 5 MB mySQL :-(
     
    Affector, Oct 20, 2006 IP
  11. Amilo

    Amilo Peon

    Messages:
    624
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Thanks esnstudio that is a great tip;)
     
    Amilo, Oct 21, 2006 IP
  12. deepesh_tamhane

    deepesh_tamhane Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    1. create a table in MySQL with appropriate columns.
    2. Create a csv file of the data you want to import. (I tried with no headings in the csv file).
    3. While importing in MySQL, fields terminated by ; replace by ,
    4. hit Go and you should have all the data in MySQL.

    e.g.
    table user: user_id (primary key, auto increment), user_first_name, user_last_name

    CSV should look like (without headings):
    1 John Smith
    2 Alan Smith

    works like a charm.
     
    deepesh_tamhane, May 31, 2012 IP
  13. brigzy

    brigzy Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    If you would like to pre-validate and rollback on errror - you might like check out this post/tip on CSV import from Excel to MySQL

    leansoftware.net/tabid/123/g/posts/t/17/How-to-validate-and-upload-CSV--comma-seperated-value--data-to-a-database.aspx

    Could save you some time
    Thanks
     
    brigzy, Aug 8, 2012 IP
  14. electroze

    electroze Active Member

    Messages:
    179
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #14
    I definitely would not install someone else's software they're pushing. It's almost always the developer trying to get recognition or someone making a commission.

    It was probably stated above that you use phpmyadmin, import as csv- make sure you have your mysql structure already made to match the excel file data you want to import, change delimiter to , and import. Lots of examples in more detail online if this doesn't suffice. No use reinventing the wheel when tutorials exist everywhere for this basic action.
     
    electroze, Aug 19, 2012 IP
  15. lifeguard2

    lifeguard2 Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #15
    You could just use this FREE online converter http://excel2sql.esy.es/ to solve your problem easy for a few secconds.
     
    lifeguard2, May 7, 2015 IP
  16. lifeguard2

    lifeguard2 Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #16
    You could just use this FREE online converter http://excel2sql.esy.es/ to solve your problem easy for a few secconds.
     
    Last edited: May 7, 2015
    lifeguard2, May 7, 2015 IP
  17. pmf123

    pmf123 Notable Member

    Messages:
    1,447
    Likes Received:
    75
    Best Answers:
    0
    Trophy Points:
    215
    #17
    you have to create the blank table with all the fields before trying to load the data
     
    pmf123, May 13, 2015 IP