How to import buoy coordinates from MS Excel

Stephen P. is trying to import some buoy data into ExpertGPS, and keeps getting an error message. He asks:
quoteWhen I try to paste and after I go through the paste procedure, click finish I keep getting this message which makes no sense at all: “The Import Waypoints in a Tabular Format command could not be completed. Minutes of longitude must be between 0 and 59.00000, 0 82.00000 is not valid”. The process worked several times when I tried samples, but now not at all.quote

How can I convert buoy data from MS Excel into GPS waypoints?

Convert buoy coordinates and marine data into GPS waypoints

When you paste delimited data from MS Excel into the Waypoint List in ExpertGPS, the program brings up the Import Text wizard to help you successfully convert your data into GPS waypoints. It’s extremely important that you select the correct coordinate format and datum on the first page of this dialog. Here’s a sample of Stephen’s data:

Record Number Buoy Number/Owner Buoy Coordinate La Buoy Coordinate Lo
1 South Private 34 42.599 82 56.333
2 South Private 34 42.765 82 56.397

Stephen’s buoy data is in Deg min.min format, which is pretty standard for marine data. Both the degrees of latitude and minutes of latitude are in the same cell in Excel. Same for longitude. In the Import Text wizard, select DMM WGS84 (or DMM NAD27, as appropriate) as the coordinate format. (If you don’t have DMM as a listed format, click Add, and add this format to the list of coordinate formats you work with.) Because latitude is in one column, and longitude is in a second column, select The coordinates are in 2 columns (Latitude, Longitude). It’s easy to overlook this setting when importing data, but it’s probably the most important part of the first page of the import wizard.

Convert buoy coordinates and marine data into GPS waypoints

Once you’ve selected the coordinate format and number of coordinate columns, click Next to move to the main page of the wizard. Match the columns in the top of the dialog with the appropriate data sources in your Excel data in the lower list. You can leave some of the columns blank, but you have to enter data for the coordinates (latitude and longitude). Click Next again, and you’re done.

Data in the wrong hemisphere

There’s one problem with Stephen’s data – the longitudes listed in the Excel file are all positive, which places them somewhere in Europe, rather than in the United States. Longitudes in the US are always negative, but you’ll often encounter online data tables where the longitude has been given as a positive number. This is extremely common in US marine data, for some reason.

Convert marine waypoints to lat/lon for GPS with ExpertGPS

To fix this for Stephen’s data, I had to go into Excel and write some formulas to prepend a – sign in front of all the data in the Longitude column. Since that gets tedious, I’ve modified the next version of ExpertGPS to detect this specific condition, and offer to fix the data for you. If you normally view data in the Western hemisphere and you attempt to import a bunch of data with positive longitudes, ExpertGPS will ask if you want to automatically change the longitudes back to the Western hemisphere. I hope this new feature takes some of the pain out of creating GPS waypoints in ExpertGPS out of tabular and delimited data.