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.

All quiet

Three proposals currently in with prospective clients. No word as yet. All hell will break loose if they all come back at the same time!

Update : Finally got a response beginning of January 2010! Still no content though. Luckily was busy elsewhere.

Stats

Working on compiling useful web statistics for my sites. Info such as page views, number of visits and referrers can give a real insight into the two crucial bits of information about your site. How your site is being used and how people are finding you.

Are you paying an expensive subscription to a business listing site? If so, you need to know exactly how much traffic they are driving to your site. If that's not much at all then you can question the value of their service to your business.

CMS

Got a prospective new client with a requirement for some self management. Looking at drupal again as a possibility for a CMS due to its ease of use.

Exporting a psd to pdf for print

Been updating the business card design for Sarah Bowden at smart-temp. I created the original photoshop psd as portrait document 6.1cm by 9.1cm standard business card size. To get the required printing resolution that needs to be at 944.882 pixels per cm (or 2400 pixels per inch in old money).

In photoshop cs2 I then selected Print with preview and selected the following options.

Then click page setup and then printer. In the printer box choose adobe pdf as the printer tne then click properties.

In the Adobe PDF settings tab choose High Quality Print as the default settings. And away you go.

(Images for this to follow soon!)

Feedback

Still waiting on feedback on my latest sites before I can proceed. In the meantime, enjoy some Duffy

Good clean fun!

I've been working on another project for a Newport based accountancy firm. I've gone for a very clean design on a white background. Still a work in progress so some of the content doesn't match (I copied it from this site) but you can get the idea. http://root.sjinfosystems.co.uk/oneonone