switching Lat/Lon to Lon/Lat (and back)

As the rest of the world operates using the standard notation of Lat/Lon that's the way I build my source files. For GPX files, it makes no difference where they reside in a source record as they are identified as being the value. Properly identifying which column/cell has which value to EPE lets this program spit out the correct value in the right spot when saving as a Garmin CSV. No pain, no fuss.

You can use MS Excel. If you do not have it, then you can go to openoffice.org and download Open Office. That will give you compatable software.

You can open the .csv file, then swap the columns (you'll have to create a new column, paste one column into it and the delete the old column - there may be a better way, but I'm not that proficient with Excel), then save it as a .csv.

Right click on Column A, select CUT. Right click om Column C, select Insert Cut Cells. Done.

That is how I do it, sometime EPE can't find the location and I do it with Google, just copy and paste, why bother opening another program to invert a single coordinate.

You could also insert a formula in an empty column to merge the data

=CONCATENATE(B1,",",A1)

I'm having some difficulties with Excel. If I create a .CSV file in the text editor, then open it with excel any commas "inside, of, quotes" each create a new column. Haven't found a way to deal with this otherwise I'd like to use Excel for this (to flip flop lat/long). It's not "supposed" to create new columns for commas that are inside of quotes!

You must have something set wrong. I just created a text file using Notepad and created the file with a .csv extension using the following line:

123456,789000,"Williamsburg, VA",xxx,yyy

Excel brought it in perfectly. Maybe you could post a "sample " line for us to look at. I'm using Office 2010.

What exact steps are you using to open the file in Excel?

I gave up on having any commas at all. Too many problems with other programs mis-interpreting commas inside of quotes.

Instead, I use "Alt + Enter" to create a new line. I don't put any commas in addresses and instead create a new line.

I do all my editing inside of Excel. It's got features that most text editors do not, such as Sort and Find by column.

Thanks for the good suggestions, everyone.

@deere478:

In Windows Explorer does your file have an icon that looks similar to the Excel icon, or does it look quite different? If you double-click the file does it open in Excel directly?

I ask because when people try to create a CSV file in Notepad they sometimes inadvertently create a file called "myfile.csv.txt" and if Windows Explorer is set to "Hide extensions for known file types" (the default) then the file name appears as "myfile.csv" (the ".txt" part is hidden).

I did the same thing using Notepad and Excel 2003.

I saved my file by putting "NotepadExcel.csv" (ir. name surrounded by quotes) in the File name line so that it has the proper extension and remains text.

What text editor were you using?

OK, I think I see the problem.

I used Notepad, but saved it a different way.

Select "File + Save as"
In the panel, select the "Save as type:" pulldown and select "All Files".
Then type williamsburg.csv without any quotes.

That should work.

I think your file was named "williamsburg.csv".txt and the .txt file extension made Excel handle it differently when it imported it.

You could probably just edit the file name to get rid of the quotes and the .txt and it would import OK.

Copy the following string including the double quotes:

"inside, of, quotes"

and paste it in Notepad (I assume you have Windows) then save it as CSV file. Double click that file (Excel should launch automatically). Do you see 1 column or 3?

@metricman, I doubt that deere78 used quotes. That is just a technique that one can use in Windows Explorer to make sure that the file name is saved exactly as the characters within the quotes USING NOTEPAD. Explorer USING NOTEPAD does not include the quote marks in the file name - it assumes that the user wanted that exact name regardless of what was in the "save as type" box.

I think he was using something other than Notepad because when I used Wordpad and the same technique of surrounding the file name in quotes to get a file extension of .csv, the resulting .csv file contains information like
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}

When I used Wordpad and changed the "Save as type" to text and put something.csv (not using quotes this time) as the File name and saved it, Excel has 5 columns when I opened the file.

Hopefully deere78 will get back to us on what he used.

You can drag a column (or row) to a new location in Excel by highlighting it, then holding down the shift key while dragging it. The cursor in Excel will change to a "T" shape while the shift key is held down, indicating where the moved row will be inserted. This will insert the row at the new location without overwriting any other data.

Thanks for the help, and sorry it took me a while to get back to this. I tried a new file with the line above, in Notepad (I have mistakenly used Wordpad also and learned not to do that). Everything opened in Excel as it should. The problem I was having was with the Hobby Shop CSV file after I had edited it and made some additions. First, it uses some of the Alt+Enter return lines, and I couldn't see the second lines. I thought they had disappeared but if I make the rows taller I now see those lines (row height settings don't get saved in a CSV file).

Long story short, the problem with ,'s inside of quote lines causing extra columns was that on all my edited lines I had a Space after a comma and before the " on each line. That caused the quotes to be unrecognized it seems.

In other words:
123456,789000,"Williamsburg, VA",xxx,yyy
would open in 5 columns as it should.
123456,789000, "Williamsburg, VA",xxx,yyy (note extra space before the first quote)
opens in 6 columns in Excel (at least on my computer!)

If I use Notepad and tighten all the lines up removing that space, it opens as it should in Excel! Good to go

That's great! Sorry, I forgot about spaces causing problems.

Yes, I learned a long time ago to never copy anything from Wordpad with out first pasting it into Notepad. That's the only way to get rid of all the control characters that are hidden in Wordpad. If you paste it directly into any MS Office product, it can really screw things up.

In Excel - click on the little arrow in the upper left corner of the fields (at junction of the columns and rows).
Result: All fields are highlighted.

Place the cursor over the divider between A & B (or B & C or C & D, etc. at the top).
Result: Cursor changes to a bar with 2 arrows, one pointing left and one pointing right.

While holding in place - Left Double click.
Result: All columns are adjusted to the maximum width of the data in a given column.

Repeat for rows (1 & 2 or 2 & 3, etc.)

@deere478

Easy way to correct multiple errors that are repeating. This works in Excel or Notepad.

If you hold the Ctrl key down and press the "H" key, the "Replace" window will open.

In the "Find" field type:

, "

That's Comma, Space, Double Quote

In the "Replace with" field, type:

,"

That's Comma, Double Quote
(No Space)

You can "Replace All" or do one at a time, if you want. Works really well.

Note: In Excel, you have to select a column (or row or all) to make it work.

I totally got away from Notepad for editing, except to copy addresses from web pages (the HTML can get copied into Excel, but not to Notepad) before copying and pasting the text into Excel.

Thanks. I've used that in MS Word but didn't realize it worked in Notepad and Excel.

That really works great metricman. This general idea helped me to change things on hundreds of locations all at once such as removing a coma (to combine street & state columns for example):
1. Insert a column between street & State
2. Type a unique character such as ZZ and copy it down in all cells of that column
3. opened it up in Notepad. Find & Replace ",ZZ' with a "(space)", and wala that coma is gone on all lines (street & state combined into the same field).

Of course there's probably an even easier way to do that I didn't think of.

If you download the ASAP utilities for Excel, under TEXT is an option to delete a specified number of ending characters in a column. So, highlighting a column containing say Tampa, you tell it to delete the last character. What you are left with is the contents of all the cells in that column untouched except the last character, the comma deleted.

ASAP Utilities is indispensable for me when it comes to the massive changes I make to the US Rest Area file whenever I download it. It's the only way I've found, other than trying to write a macro, to change words written in all capital letters (i.e. unreadable) into mixed case (initial letter capitalized, lower case).

When I created my POIs (in CSV format) I found the "just throw everything into these four fields" approach of CSV files to be a bad way to organize data. So I went ahead and organized my data in a cleanly labeled organized text file format and wrote a tiny AWK script to convert it into the final CSV file format. There were many benefits for me in doing this, but on the lat,long issue I quickly realized that I didn't have to manually swap anything, I could just store the coordinates into my data in the normal format that I got from Google or other sources and let AWK do the swap for me.

AWK is a nifty little scripting language. There are free versions for Windows (and Linux and other OSs). I used the free gnuAWK. When I decided it was best to let the script do the swapping, I only added one line to my script to make this happen:

/LAT&LON:/ { c=index($2,","); lat=substr($2,1,(c-1)); lon=substr($2,c+1,length($2));}

It basically finds the comma in the lat,long data and creates two variables for the coordinates. Lat is everything up to (but not including) the comma, lon is everything after the comma. Then the output line prints them in Garmin backaswards order just by making sure that lon is output first. This is the line that actually prints the output:

printf "%-1s,%-1s,%-1s,\"%-1s\"\n", lon, lat, field3, field4 ;

The entire script is less than 20 lines, basically one line for each type of input data that I might want to process (name, city, state, zip, hours, comment and so on). One nice side effect of this is that after organizing the data I could quickly output it in a different form (such as a xml type file) just by making some changes to the AWK script.

As to getting the data back out into normal form, I have not done that. Mainly I just object to all the junk thrown into field 4 in an unorganized way that would not be easy to process properly, so I have never bothered to write anything to take apart CSV files. But the lat and long are well defined as the first two comma separated fields, so that much would be easy enough to pull apart and then write back in normal form. The third field is often just a name common to the entire POI file (such as "Burger King", and with some POIs you can't count on this even being typed consistently for each entry). Beyond that I would be inclined to just treat the rest (field 4) as "everything else" and leave it as I found it rather than try to process it, but if the POI was well organized (certainly not always the case) it would not be too hard to break it back into useful fields.

I have started maintaining my POI data in Microsoft Access databases so I can keep all of the fields separate and then use Select Queries to pull together the column data that I need for the two basic formats:

• 8-column CSV for EPE, which I then save as GPX, and
• 3-column CSV for TomTom's "makeov2.exe" utility, which creates OV2 files.

The last piece of the puzzle was a quick and dirty way to swap coordinates from text files, so I wrote a little VB6 program that parses the text on the Windows clipboard, swaps the items on either side of the comma, and puts the result back on the clipboard, e.g....

-79.397336,43.701645

...becomes...

43.701645,-79.397336

A keyboard shortcut associated with that application lets me

• copy the original coordinates with [Ctrl-C],
• swap the order with [Ctrl-Alt-A], and then
• paste the swapped coordinates into the other application with [Ctrl-V].

Please share that code.

I also use Access to store my raw data.

See the related thread here.