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.
Add a comment
|
3 Answers
Now import the table query again, it will replace the dummy table you created before.
-
-
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.– bertCommented Nov 9, 2009 at 18:53
Google Docs Spreadsheet:
- File > Download as... > CSV
phpMyAdmin:
- Import > Format: CSV
- ✔ The first line of the file contains the table column names
- [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.
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.