Need to write a find/replace script for items in a text file

Question

I'm faced with a problem. I have a text list of thousands of cities and their gps coordinates. The format is:

City,longitude,latitude,Parent

so an example might be:

Los Angeles,34.11N,118.41W,California 

The problem is that I need the long/lat to be in + and - values so the correct entry would be:

Los Angeles,34.11,-118.41,California

So the script would be something like

• Find "W" or "S" (west and south get the "-" symbol)

• if you find either of those letters within the 2nd or 3rd "," insert a "-" symbol before the first number. (we don't want to affect the words in the 1st or 4th field)

• then delete any letter from the long/lat field.

This could be done a zillion ways - excel, .bat file, something else?

If anyone could help with this I'd be very grateful. Thanks

Answer 1

Try this Batch file:

@echo off
setlocal EnableDelayedExpansion
if exist thefile.tmp del thefile.tmp
for /F "tokens=1-4 delims=," %%a in (thefile.txt) do (
    set longitude=%%b
    set sign=!longitude:~-1!
    set longitude=!longitude:~0,-1!
    if /I !sign! == S set longitude=-!longitude!
    set latitude=%%c
    set sign=!latitude:~-1!
    set latitude=!latitude:~0,-1!
    if /I !sign! == W set latitude=-!latitude!
    echo %%a,!longitude!,!latitude!,%%d>> thefile.tmp
)
del thefile.txt
ren thefile.tmp thefile.txt

Answer 2

The following formula should get you started:

Using the following as input (Note that you need to convert the "." to "," in excel"):

A2          B2      C2      D2
Los Angeles 34.11N  118,41W California 

This is from my swedish version of excel

=OM(HITTA("W";C2);-1*(BYT.UT(C2;"W";""));)

I think this translates to:

=IF(FIND("W";C2);-1*(REPLACE(C2;"W";""));)

The outcome of this is that cell C2 is converted to -118,41.

Update

Excel/open office is great for many things but I always found the text-handling to be extremely awkward... You seem to be open for alternative solutions and my suggestion is that you install python. It looks like you are on windows; here there are two ways to get python:

1. install python from python.org

2. install cygwin from cygwin.com and with that python

alternative 2. has the advantage that you will get a compete unix/linux environment on windows!

This is how a (bit verbose) python solution might look; even though perhaps you never seen python before you should be able to understand the code quite easily...

#!/usr/bin/env python

def convert(word):

    if 'W' in word or 'S' in word:
        word = word.replace('W', "")
        word = word.replace('S', "")
        word = '-' + word
    return word

with open("input") as fd:
    for line in fd:
        line=line.strip()
        line = line.split(',')
        line[1] = convert(line[1])
        line[2] = convert(line[2])
        print ",".join(line)