Convert between formats

 

I have a file I created with the following type coordintates

39.311844 76.626258

I save it in a .csv format and my Garmin can read it.

I just checked other poi files and find they are in this type format (which Garmin as no trouble reading)

-68.30356 44.35395

Is there a program that will do the conversion for me?

Thanks,
Reb

lat/lon or lon/lat

Garmin format CSV files have long been "backwards" in that they required the longitude come before the latitude. When POI Loader was redone somewhere in the 2.7 releases they allowed either format. What I haven't tested (because I don't make CSV files except from the GPX files) is if this is per location or per file. As most programs return the conventional Lat/Lon coordinates, it was wise of Garmin to allow this in their loader.

--
Illiterate? Write for free help.

Convert Lat or Long coordinates

Hopefully, you are a little familiar with Excel and can use the following quite easily.
Of course you can use your GPS to convert each entry one by one.
Note that if a Latitude is in the Southern Hemisphere (U.S. is not) or Longitude is in the Western Hemisphere (U.S. is), precede the designation with a “-“ sign.
There are 360 degrees in a circle and each degree is divided into 60 Minutes and each Minute is divided into 60 Seconds; therefore, no Minute or Second figure will ever be equal to or greater than 60.
Do NOT precede any latitude or longitude number to be converted with a “-“ minus sign or as a negative number when using the formulas below.

To Convert Decimal to Degrees: [Do not use the minus sign!!]
Example of Decimal Longitude -121.135 converted to Degrees -121°8’6”
1. The whole units (121) will remain the same for the Degrees.
2. Multiply the decimal by 60 ( .135 x 60 ) = 8.1
3. The whole number of the above result becomes the Minutes ( 8’ )
4. Take the remaining decimal from step 3 ( .1 ) and multiply by 60 ( .1 x 60 ) = 6
5. The resulting number becomes the seconds ( 6” )
6. Take the 3 sets of numbers and put them together, using the Degrees / Minutes / Seconds with their symbols: 121° 8’ 6” or the seconds can be displayed as a decimal by dividing it by 60: 121° 8.1’

To Convert Degrees to Decimal:
Using Excel, there are 3 formulas depending on whether the Degree value is 1-digit, 2-digits or 3-digits.
Format the destination cell for at least 4 decimal places, 5 is recommended for matching the Garmin format.
CAUTION: A lot of systems display in Degrees and Minutes only, with the Seconds as a Decimal extension of the minutes ( e.g.: -88° 6.262’ ). In this situation the 6 becomes 06 and the decimal ( .262 ) is multiplied by 60 resulting in ( 15.72 ) which I would round off to 16. You would then be working with input parameters of 880616 for your conversion. You should always have 2 digits for the minutes and 2 digits for the seconds using the formulas below.
2-digit degree: [If a Lat. or Long. # is preceded by a minus sign, do NOT use the minus sign in the formula!!]
Example: 75° 15’ 20” will be entered into cell A1 as 751520. If the 15 was a 5 then it would entered as 05.
Formula: =LEFT(A1,2)+MID(A1,3,2)/60+MID(A1,5,2)/3600 This formula could go into cell B1 and would have a result of: 75.25556

3-digit degree: [If a Lat. or Long. # is preceded by a minus sign, do NOT use the minus sign in the formula!!]
Example: -180° 3’ 45” will be entered into cell A2 as 1800345. Note the 3 was entered as 03.
Formula: =LEFT(A2,3)+MID(A2,4,2)/60+MID(A2,6,2)/3600 This formula could go into cell B2 and would have a result of: 180.0625

1-digit degree: [If a Lat. or Long. # is preceded by a minus sign, do NOT use the minus sign in the formula!!]
Example: 5° 59’ 8” will entered into cell A3 as 55908. Note the 8 was entered as 08, and the degree (5) as is.
Formula: =LEFT(A3,1)+MID(A3,2,2)/60+MID(A3,4,2)/3600. This formula could go into cell B3 and would have a result of: 5.98556
Note: You will never have a Longitude or Latitude degree in the U.S., Mexico, or Canada with a single digit for the degree.
Good Luck.

--
Kudos to all at POI Factory - a great site!

Not what I expected.

Box Car wrote:

Garmin format CSV files have long been "backwards" in that they required the longitude come before the latitude. When POI Loader was redone somewhere in the 2.7 releases they allowed either format. What I haven't tested (because I don't make CSV files except from the GPX files) is if this is per location or per file. As most programs return the conventional Lat/Lon coordinates, it was wise of Garmin to allow this in their loader.

Box Car, I tested this when they added the feature and it didn't work the way I expected. I sent a support request to Garmin and here is the response I got:

Quote:

Original Message Follows:
------------------------
I would like to use the new feature of POI Loader 2.6.0 where the .CSV file can list latitude first instead of longitude first. How do you indicate this to POI Loader 2.6.0?

Reply Follows:
------------------------
Dear Alan,

Thank you for contacting Garmin International.

I would be more than glad to assist you with you question, and do apologize it took so long to respond to your intial inquiry. What this
means is that if you mistakenly put in longitude then latitude the POI loader now has the ability to catch this and put it into the correct format latitude then longitude. Please let us know if you have any additional questions.

With Best Regards,

Angelique L.
Product Support Specialist

I suspect they are determining whether to reverse the coordinates based on the fact that a high percentage of land based coordinates resolve to a place in the ocean if the coordinates are reversed.

--
Alan - Android Auto, DriveLuxe 51LMT-S, DriveLuxe 50LMTHD, Nuvi 3597LMTHD, Oregon 550T, Nuvi 855, Nuvi 755T, Lowrance Endura Sierra, Bosch Nyon

I learning something

Box Car wrote:

Garmin format CSV files have long been "backwards" in that they required the longitude come before the latitude. When POI Loader was redone somewhere in the 2.7 releases they allowed either format. What I haven't tested (because I don't make CSV files except from the GPX files) is if this is per location or per file. As most programs return the conventional Lat/Lon coordinates, it was wise of Garmin to allow this in their loader.

I totally missed that change, but I went to Garmin and found this

Quote:

Changes made from version 2.5.4 to 2.6.0:
Added ability to specify a custom POI file's name
Added ability to handle swapped lat and lon values in CSV files
Fixed issue with multi-line CSV files not being read correctly
Fixed issue with extended ASCII characters in CSV files
Fixed issue with reading large gpx and csv files making the application unresponsive

Interestingly enough, POI Loader Help still says:

Quote:

POI Loader assumes a .csv file utilizes the following format for each POI (brackets [ ] denote optional text):

<Lon>,<Lat>,["]<Name>[@<Alert Speed>]["],["] [comment]["]

Longitude and latitude must appear in WGS84 decimal degrees format (ddd.ddddd; negative numbers indicate West and South).

I was assuming that the negative numbers were the trigger, but when I tried just reversing my lon and lat in a file, that did not work.

Has anyone figured out what you have to do to get POI Loader to handle swapped lon and lat?

Perhaps

it only 'catches' the 'mistake' if the longitude is numerically greater than +/-90 degrees...

--
Striving to make the NYC Metro area project the best.

Re: Perhaps

camerabob wrote:

it only 'catches' the 'mistake' if the longitude is numerically greater than +/-90 degrees...

I wondered that, too. If so, it's not much help to the Europeans....

The post from the OP was not

The post from the OP was not entirely clear. But I took it to be that he had a CSV file with more than two columns with two errors. His col A had correctly formatted latitude and his col B has a longitude without the minus for western longitudes. I understood he wanted to get a correctly formatted col A with the longitude and col B with the latitude. His example of the incorrectly formatted stuff was, 39.311844 76.626258. He wanted -76.626258 39.311844.

This can be done in Excel with a little work.

When a CSV file is loaded into Excel, it converts it to an Excel file for all operations in Excel. That is important.

Solution:
Col B is the problem. It needs to become col A with all of its data as negative numbers and existing col A becoming col B.

Steps:
1. Load the CSV file into Excel. The format will be as in the CSV file (39.626258 76.626258)

2. Insert a new column before current col A. The inserted column now becomes col A and we have three new columns, empty col A, new col B with old col A data (39.311844). And new col C with old col B data (76.626258)

3. In cell A1 enter the formula, = - $C1, enter. The new value of A1 is now -76.626258.

4. Copy A1, Ctrl+C.

5. Move cursor to A2. Press and hold shift key. Move cursor to last cell in col A that has data. Release shift key. Press Ctrl + V. All of the values in col A are the respective negative values of the data in col C.

If we could just delete col C we would be finished, but Excel will keep the reference between col A and col C. An error in col A will occur if we delete col C.

The solution to this problem is to save the file as a CSV file – not an Excel file. Use he Save as command and select CSV as the file type.

Exit Excel without saving. Then start Excel again and reload the CSV file.

All the reference links are gone, and the values in each cell are now absolute.

Now delete col C and no errors will occur.

Save the file and you are done with the desired format and data for col A and col B.

Good luck!
Wil

--
wil01

Another way

Wil01 wrote:

...
If we could just delete col C we would be finished, but Excel will keep the reference between col A and col C. An error in col A will occur if we delete col C.

...

copy the entire Col A; then, put the cursor at the top of Col A and do a "Paste Special" choosing values as the option from the drop down box;

Now delete Col C

Bingo! I couldn’t find

Bingo! I couldn’t find that function in the help file. Thanks! Since my post was kind of a tutorial, I’ll show the edited steps to make it easier for the OP, and maybe others.

Steps:
1. Load the CSV file into Excel. The format will be as in the CSV file (39.626258 76.626258)

2. Insert a new column before current col A. The inserted column now becomes col A and we have three new columns, empty col A, new col B with old col A data (39.311844). And new col C with old col B data (76.626258)

3. In cell A1 enter the formula, = - $C1, enter. The new value of A1 is now -76.626258.

4. Copy A1, Ctrl+C.

5. Move cursor to A2. Press and hold shift key. Move cursor to last cell in col A that has data. Release shift key. Press Ctrl + V. All of the values in col A are the respective negative values of the data in col C.

6. Move cursor to col A and copy all cells in col A with data.

7. Move cursor to A1. Right click. Select “Paste Special.” From the dropdown, select “values”. Now the link between col C and col A is removed.

8. Delete col C. You’re done!

Good luck!
Wil

--
wil01