Possible bug: Seatable not recognizing coordinates when importing xlsx

[busybadger]

Your Setup:

• Self-hosted
• SeaTable Version: “6.0.10”

Describe the Problem/Error/Question:

I’m trying to import an xlsx file into my database and one of the columns is Location (Coordinates) and SeaTable isn’t recognizing them as coordinates - it’s reading them as a multi-select instead.

We suspect it’s a bug with SeaTable because we tried exporting the existing database (which contains coordinates) as an xlsx and then re-importing that xlsx to the database, and it does the same thing.

Is there a specific way coordinates should be formatted in a xlsx file (or csv, for that matter) for SeaTable to read them as coordinates? Or is this a bug?

I couldn’t find anything to assist me in the SeaTable manual, so appreciate any help I can get!

Error Messages:

No error messages given.

[bha]

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:

@rdb

I agree, importing a geolocation column is awkward.

This is how you can do it:

• Import the file without doing anything special → you’ll end up with geodata in a multiple select column
• Add a formula column and add the function {<column_name>} where <column_name> is the name of the column storing the GPS data. → the column shows the values a comma-separated text
• Add a geolocation column
• Copy and past the content from the formula column to the new geolocation column

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!