Wednesday, February 24, 2010

Importing Spreadsheet Address Data in Google Maps

Recently I've been looking into schools in Monmouthshire for my two boys and wanted to put them onto map to get a rough idea of where they are. Putting little stickers on a paper map is so last millennium, but how do you take a list of addresses and quickly plot them onto Google Maps without having to manually look up each address?

The solution lies with this really great website which will take a load of tab delimited address data (plus any associated info, email addresses, urls etc) and plot them onto a google map for you. The instructions on the site are clear and simple. You arrange your data in the format defined by the site (it's an excel spreadsheet) and then just paste it into the textbox on the page. From there you can validate your data is good and adjust various settings to get the data parsed just right. Once done you end up with a google map with placemarks for your addresses. The placemarks contain your address data plus any other additional info you included. I put the telephone number, headteacher's name and links to the school's inspection reports for example

The only slight problem with this is that the info is contained within the batchgeocode system rather than the google maps system.

But there is a way around this. Once completed in batchgeocode you can export the info as Google Earth KML data. This can then be imported into My Maps in Google as detailed here

There you go, a load of addresses converted into placemarks on a google map.

I don't make any guarantees as to the accuracy of this data as it's purely for my own use.

Friday, February 12, 2010

Heavy Goods

If you're a HGV driver, have a look at this site.

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.