Tuesday, February 2, 2010

How to import an excel spreadsheet into a mysql table using phpmyadmin

This post assumes familiarity with the use of Microsoft Excel, PhpMyAdmin and copying and pasting. It's more about the process of juggling your data around to get it into an importable format than a step by step "press this next" guide.

Problem : Import a large multi-column spreadsheet into a mysql table.

Not as straightforward as you might think. MySQL can import a comma separated variable file, but what if your content has got commas in it?

The answer...

  1. Using phpmyadmin create a table with corresponding fields to match the columns of your spreadsheet.
  2. We now need to export the data in the spreadsheet into a .CSV format suitable for importing into MySQL.
  3. If your data does not contain any commas load up your spreadsheet in excel and skip to step 11, skipping step 13.
  4. If your data does contain commas we need to replace them all with some other combination of unusual symbols as Excel will only export with a comma delimeter and we do not want to confuse delimiting commas and language commas.
  5. In Excel create a new (blank) spreadsheet.
  6. Go back to the spreadsheet with the data in and 'Select All' by pressing Ctrl-A.
  7. Copy the data by pressing Ctrl-C
  8. Go to the blank spreadsheet and from the Edit menu select Paste Special.
  9. In the Paste Special dialogue box select to only paste 'values'. All other entries can be left at their default settings. This will paste only the values into the new spreadsheet and leave out all formulas etc.
  10. Now do a "Replace" by pressing Ctrl-H. Enter a comma in the 'Find' box and a double-caret ^^ in the replace box. Click "Replace All"
  11. Now do a File, Save As and change the file type to .CSV and save the file in the folder of your choice.
  12. Open the folder and load the .CSV into notepad or similar. Do a Replace (Ctrl-H) and in the find box put a comma and in the replace box put a semi-colon. Then click replace all. The file is now delimited by semi-colons.
  13. Next another replace and in the find box put a double caret (^^) and the replace box put a comma. Then click replace all.
  14. Then press Ctrl-S to save the file again. We now have a semi-colon delimited file with comma punctuation marks back where they were.
  15. Now back to phpmyadmin. Browse to the correct table in your database and click on the import tab.
  16. On the import tab, click the browse button on the File to import section and locate your CSV file.
  17. Deselect the Partial Import check box
  18. Now check the CSV using LOAD DATA check box. I find this works best. You may need to fiddle around with the other options to get it working
  19. The default values shown should now work. These are Fields delimted by ';'; Fields enclosed by '"'; Fields escape by '\'; Lines terminated by 'auto'; column names blank and 'Use LOCAL keyword' checked.
  20. Click 'Go'. Job done.

No comments:

Post a Comment