SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Import data from MS Excel to Mysql

    I want to port data from a .xls(Excel file) to mysql database. I am not sure how to go about doing that. Excel can store the file as text in three options: tab, comma or space delimited. I have comma and space in my data so I think I will have to use the tab delimiter. Correct me if I am wrong. Also I would like to know how do I input the file into mysql database once the text file is ready. I would appreciate the actual commands.

    Thanks for the help in advance.

  2. #2
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's ok to use CSV when you have commas and spaces because the quotes will separate them for example


    "don joe","33,4.5,34","dorkus-majorus","etc etc etc"


    that's be fine.



    Here's how you do this in unix via mysqladmin:

    http://www.mysql.com/doc/L/O/LOAD_DATA.html



    Go read.
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  3. #3
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the link and the reply. My database has a column called id and the php script I use genrate a unique value through uniqid() everytime a new record is added to the database.

    However, right now, I am trying to input data into the database from a text file using the LOAD data INFILE command of mysql.

    Now, how can I set the unique id for all the records that gets inputted from the text file? I cant think of any solution for this right now.

    Thanks for the help in advance. This forum has been of great help to me.

  4. #4
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    find hte highiest current value, then for each new entry, add 1 to hte previous value, that way none will be the same

    I would reccomend just making a little script to do this for you.. it would only take 20 mins tops

  5. #5
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Or.. you could just set that column to "NULL" for all rows in your Excel file before you export it as CVS....
    Wayne Luke
    ------------


  6. #6
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    will that automaticly make that value unique?

  7. #7
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi slightlywacked,

    Thanks for the suggestion. I guess I will do it the script way. However, I was more inclined towards direct msql upload was that I have never worked with file handling feature of php. Hence I am not sure how to get the data from the text file and put it in database. Any pointers/suggestion/sample script to get me started would be greatly appreciated.

    Thanks again for the help once again.

  8. #8
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI Luke,

    What happens if I set the column to null in the excel file? How will get stored in the database as a unique value?

    Thanks.

  9. #9
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump


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
  •