Hi @busybadger,
Thanks for reporting this, this is a known issue. Unfortunately for now there is no one-step direct straightforward process to get your GPS coordinates from a xslx file to a Geolocation column, but based on this recent topic here is a (quite) simple to do it:
And if copying/pasting the content from the formula column to the new geolocation column doesn’t work, you can use the following Python script. Please note that you will have to modify the TABLE_NAME, TXT_COORDS_COL and GEOLOCATION_COL at the beginning of the script to match your needs
import re
from seatable_api import Base, context
TABLE_NAME = 'Table1'
TXT_COORDS_COL = 'GPS'
GEOLOCATION_COL = 'RealGPS'
def dms_to_dd(dms_str: str) -> float:
dms_str = dms_str.strip().upper()
direction_match = re.search(r'[NSEW]', dms_str)
if not direction_match:
raise ValueError("Missing N, S, E or W direction.")
direction = direction_match.group()
dms_str = dms_str.replace(direction, '').strip()
pattern = r"""
(?P<degrees>-?\d+(?:\.\d+)?)\s*°?\s*
(?P<minutes>\d+(?:\.\d+)?)?\s*[\'′]?\s*
(?P<seconds>\d+(?:\.\d+)?)?\s*["″]?
"""
match = re.search(pattern, dms_str, re.VERBOSE)
if not match:
raise ValueError("Format DMS invalide.")
degrees = float(match.group('degrees'))
minutes = float(match.group('minutes')) if match.group('minutes') else 0.0
seconds = float(match.group('seconds')) if match.group('seconds') else 0.0
dd = degrees + minutes / 60 + seconds / 3600
if direction in ('S', 'W'):
dd = -abs(dd)
else:
dd = abs(dd)
return dd
base = Base(context.api_token,context.server_url)
base.auth()
rows = base.list_rows(TABLE_NAME)
rows_data = []
for row in rows:
lat = dms_to_dd(row[TXT_COORDS_COL].split(',')[0])
lng = dms_to_dd(row[TXT_COORDS_COL].split(',')[1])
print(str({'lng': lng, 'lat': lat}))
if lat and lng:
rows_data.append({
"row_id" : row['_id'],
"row" : {
GEOLOCATION_COL : {'lng': lng, 'lat': lat}
}
})
base.batch_update_rows(context.current_table,rows_data)
Bests,
Benjamin
PS: Welcome to the SeaTable forum!
