SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there an easy way to insert this data into MySQL?

    Hi there

    I've been given loads of Excel spreadsheets which contain specification data for bikes. In some cases I've been able to transfer the data from Excel into MySQL without any trouble by copying and pasting SQL INSERT code into the spreadsheets and then copying and running everything in MySQL. For example, here is a spreadsheet which contains all the data for 1 bike, this is simply copied into MySQL and executed:



    So that was pretty easy and quick, but the rest of the specs that I need to add are laid out completely different in the spreadsheet.... Instead of having a single bike on one sheet with all the specs laid out vertically, the specs for every bike are laid out horizontally on one page so I can no longer just paste in the SQL INSERT code for each bike Heres a screeny of what I mean:



    Soooo, is there a way of switching all the data round to display vertically? Or are there any other ways I can do this quickly?

    Really hope someone can help me here. Many thanks

  2. #2
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can save your Excel files as CSV (comma separated) and then -- import to mysql database using a phpMyAdmin installation -- just click on a table name you want to import the data to, click the "SQL" button on the top menu --> "Insert data from a textfile into table".
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  3. #3
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there

    I am using version 2.5.3 of phpMyAdmin and when I try to import the data it says:
    ******************
    MySQL said:

    #1148 - The used command is not allowed with this MySQL version
    ******************

    Is this normal or is it likely that I have made a mistake?

    Many thanks

  4. #4
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does your MySQL installation have LOAD DATA INFILE function enabled? What is your MySQL version?
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  5. #5
    SitePoint Addict o~~Goatee~~o's Avatar
    Join Date
    Jan 2004
    Location
    North Yorkshire UK
    Posts
    350
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again. I am running MySQL 4.0.15-max-debug. I don't know if I have LOAD DATA FILE enabled, how do I fing out?

    Thanks

  6. #6
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suggest you to move to the stable 4.0.20 release. To check if you have LOAD DATA INFILE enabled try to run the SQL query yourself (the "SQL" button of the phpMyAdmin and follow the documentation).

    Also you can try to use the LOAD DATA INFILE command in the mysql command-line interface while connected to your server using Telnet or SSH.

    Another way to move the data to MySQL would be to convert the spreadsheet into an MS Access database. Once that has been done, then you can use AccessDUMP to turn the Access DB into a usable SQL schema dump. AccessDUMP (or ADUMP for for more advanced users) is a free GUI-based application, and can be downloaded here:

    http://www.intranet2internet.com/public/default.asp

    Once you have converted the Access database to an SQL schema file, then you can use phpMyAdmin to upload the file, using the "Run SQL query/queries on database - textfile" feature on your phpMyAdmin database table listing page.

    I hope this helps.
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!


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
  •