[Jeremy Young]

Hi
I am in the process of implementing commentics on a largish site and it seems to be working nicely (thank you), but one thing I want to do is to import some saved comments from a previous version of the site. I have an excel table with a couple of hundred comments and ideally i would like to import these into the database.
Can I do something like export the sql database, edit it in an sql database program and reimport into the site? .. and is there any guidance on how to do that (using a Mac) - I am reasonably nerdy but have no sql experience.
Jeremy Young

[Steven]

Hi Jeremy,

Yeah an export and import of the database would be the best way. I'll reply again tomorrow with steps. I don't use Excel (I use OpenOffice) so my instructions will be for that, although I'm sure they'll be very similar.

[Jeremy Young]

Hi Jeremy,

Yeah an export and import of the database would be the best way. I'll reply again tomorrow with steps. I don't use Excel (I use OpenOffice) so my instructions will be for that, although I'm sure they'll be very similar.

thanks - the Excel vs OpenOffice part I am sure I can do easily it is how to get the database in and out I am a bit lost on

cheers
Jeremy (and I will do the survey in a week or two once I have got used to the system),

[Steven]

Hi,

The export/import is probably the easiest bit so you should be fine.

I assume you have phpMyAdmin?

1. Open phpMyAdmin.
2. Select the Commentics database on the left.
3. Click on the 'comments' table.
4. Click the 'Export' tab at the top.
5. The page should say 'Exporting rows from "comments" table'. Make sure the SQL format is chosen and click the 'Go' button.
6. You should have a download called comments.sql. Keep this somewhere safe just as a backup.

7. On the same page select the CSV format.
8. Select the custom option which should open up some settings.
9. Select 'Put columns names in the first row'.
10. Click the 'Go' button.

11. Open the comments.csv file in OpenOffice.
12. A window with options appears. Make sure 'Separated by' is set to comma.
13. You should now have a spreadsheet with the comments. Add the new comments. Make sure the id column increments by one for each new row. Hopefully you find all of the columns to be self-explanatory.
14. When finished, remove the first row with the column names and shift the rows below upwards one.
15. Save and close.

16. In phpMyAdmin go back to the 'comments' table.
17. At the bottom, select 'Check All'.
18. Click the 'Delete' button.

19. Click the 'Import' tab at the top.
20. Click the 'Browse' button and select the comments.csv file.
21. Click the 'Go' button.

Notes:
- You should put the script in maintenance mode (Settings -> Maintenance) while you do all this as any new comments inbetween may mess with the IDs.
- Make sure the user data (name, town, comment etc) in the spreadsheet is encoded as HTML entities so that it won't execute as code when viewed later.
- Any issues, just drop the 'comments' table and import the SQL backup.

[Jeremy Young]

Steve thanks for the detailed advice. The export sequence all worked nice and easily and when I selected export for excel it also opened easily. On import I did run into problems because excel mucked the dates up (they all ended up as 1st Jan 1970), but a little internet searching came up with the solution - convert the dates to text in excel before exporting, using the function =TEXT(A1,"yyyy-mm-dd hh:MM:ss")

I am not quite there as I also need to edit the pages table in order to link to pages which have not been commented on before, but basically I am getting there - and gaining some useful experience with the sql database.