Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.


I'm managing a small website, which allows people to register as members. Previously I used a google form to manage member registration, but now as the number of users becomes quite big I am switching to mysql. Currently I have around 500 members in the database, saved in a google spreadsheet. How can I do a bulk import from a google spreadsheet to a table in mysql? BTW I'm using phpmyadmin, so a solution for phpmyadmin is preferable :)
Thanks.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

Now import the table query again, it will replace the dummy table you created before.

share|improve this answer
    
do you mean, I would have to copy and paste 500 times? –  phunehehe Nov 9 '09 at 15:30
    
No just one paste action. I can't check the exact punctuation of SQL right now, but the only thing that could be needed is one search and replace action to replace a return with ),( or so. –  bert Nov 9 '09 at 18:53

Google Docs Spreadsheet:

  1. File > Download as... > CSV

phpMyAdmin:

  1. Import > Format: CSV
  2. ✔ The first line of the file contains the table column names
  3. [Go]

As it's plain-text, there are countless ways that it could be done although if you're using an older version of phpMyAdmin (or find yourself without it at all) an alternative is to use PHP's fgetcsv() or str_getcsv() to process the file and import it.

share|improve this answer

SQL is just plain readable text. Make a new table in PHPmyAdmin with the structure you want, two dummy entries and export the query for it to a .sql text file with a "drop table" mark. With the spreadsheet, export a comma-separated text file. Now cut and paste the parts from the csv file into the sql file, and adjust the number of fields in the SQL header. If needed use search and replace to match punctuation. Use Notepad++ (Smultron on a Mac) for easy editing of .sql files. You can use 2 search and replace actions to convert the CSV to SQL

replace , with ','

replace return with ');INSERT INTO tablename fieldname1,fieldname2) VALUES (

Then copy paste the edited list into the dummy SQL query.

Now import the table query again, it will replace the dummy table you created before.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.