SOLVED
Home

GeoLocations - Upload values to existing/new List

Highlighted
Frequent Contributor

What are the possible options to upload geo coordinates into a GeoLocation field of a list.

Can the values be uploaded from Excel?

Can we convert & assign single text values to a GeoLocation column?

Powershell?

There are 2 options to code it, as documented in the link below, but are there other options?

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-add-a-geolocation-column-...

Tx & brgds.

8 Replies
Solution

Hi @Eric Deferm

unfortunately Geolocation fields are unsupported for user in MS Flow, you could use this template, but it's not supported :( I tried.

https://us.flow.microsoft.com/en-us/galleries/public/templates/5b923a189f4448bd95d05def287f0d9d/crea...

Immagine.pngMS PowerApps too doesn't support GeoLocation fields..

 

You can create text column and populate your geolocation field with a Remote Event receiver https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/create-a-remote-event-receiver-in-sharepo... or an Azure Function using webhooks https://docs.microsoft.com/en-us/sharepoint/dev/apis/webhooks/sharepoint-webhooks-using-azure-functi... , in you want to use Web UI Experience (your text field).

 

A simply way could be powershell import from csv / Excel https://gallery.technet.microsoft.com/office/Add-SPOListItemsFromCSV-76077b14 

 

Cheers,

Federico

 

ps: I just add a uservoice, you can vote for it if you want :) https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/36941659-geolocat...

Tx @Federico Porceddu  for your extended reply, elaborating on several potential options/alternatives.

Apologies for my delayed reply, finally I decided to go for some C# coding & Visual Studio. As a newby to VS, it took me a while to get everything right. As there's no attach option :-( , I'm sharing code below (probably not fully according to best practices on C# programing, but it worked for me).  I'll sure will also check the PS solution and let you know.

 

// https://coderwall.com/p/app3ya/read-excel-file-in-c
// https://docs.microsoft.com/en-us/sharepoint/dev/general-development/integrating-location-and-map-fun...
// https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-add-a-geolocation-column-...
// upload the necessary assmeblies & references in your solution

using Microsoft.SharePoint.Client;
using System;
using System.Security;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("c:\\temp\\Sites2.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

ClientContext context = new ClientContext("https://<tenant>.sharepoint.com/<site-collection>");
setOnlineCredential("<username>", "<password>", context); //required in our case due to SSO
AddListFields(context);
Console.WriteLine("Fields added successfully");
AddListItem(context, rowCount, colCount, xlRange);
Console.WriteLine("List items added successfully");
}

private static void AddListFields(ClientContext context)
{
List oList = context.Web.Lists.GetByTitle("SitesGeoLocationList");
// oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Name'/>", true, AddFieldOptions.AddToAllContentTypes); > covered by Title field
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Status'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Type'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Mineral'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Technique'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='LegalEntity'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Country'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Region'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Latitude'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Longitude'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Geolocation' DisplayName='Location'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Update();
context.ExecuteQuery();
}

public static void setOnlineCredential(string userName, string password, ClientContext context)
{
SecureString secureString = new SecureString();
foreach (char c in password.ToCharArray())
{secureString.AppendChar(c);}
context.Credentials = new SharePointOnlineCredentials(userName, secureString);
}

private static void AddListItem(ClientContext context, int rowCount, int colCount, Excel.Range xlRange)
{ // Replace site URL and List Title with Valid values.
List oList = context.Web.Lists.GetByTitle("SitesGeoLocationList");

for (int i = 2; i <= rowCount; i++) //row 1 contains column headers
{
Console.Write("\r\n");
Console.Write(i + " - " + xlRange.Cells[i, 1].Value2 + "\t");
ListItemCreationInformation itemCreationInfo = new ListItemCreationInformation();
ListItem oListItem = oList.AddItem(itemCreationInfo);
oListItem["Title"] = xlRange.Cells[i,1].Value2;
oListItem["Status"] = xlRange.Cells[i,2].Value2;
oListItem["Type"] = xlRange.Cells[i,3].Value2;
oListItem["Mineral"] = xlRange.Cells[i,4].Value2;
oListItem["Technique"] = xlRange.Cells[i,5].Value2;
oListItem["LegalEntity"] = xlRange.Cells[i,6].Value2;
oListItem["Country"] = xlRange.Cells[i,7].Value2;
oListItem["Region"] = xlRange.Cells[i,8].Value2;
oListItem["Latitude"] = xlRange.Cells[i,9].Value2;
oListItem["Longitude"] = xlRange.Cells[i,10].Value2;
FieldGeolocationValue oListItemGeoValue = new FieldGeolocationValue();
if (xlRange.Cells[i, 10].Value2 != null)
{ oListItem["Location"] = "POINT (" + xlRange.Cells[i, 10].Value2.Replace(',', '.') + " " + xlRange.Cells[i, 9].Value2.Replace(',', '.') + ")"; }
oListItem.Update();
context.ExecuteQuery();
}
}

Hi @Eric Deferm , nice :)

 

instead use

oListItem["Location"] = "POINT (" + xlRange.Cells[i, 10].Value2.Replace(',', '.') +

you can use (numbers 123 are i.e.)

  
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse("123"),
Latitude = Double.Parse("123"), 
Longitude = Double.Parse("123"),
Measure = Double.Parse("123"),
};
oListItem["Location"] = geolocationValue;

Cheers, Federico

Hi @Federico Porceddu , didn't know one could also specify the Altitude & Measure. The programming interface supports 4 values, whereas the user interface only allows you to enter 2 fields (Long. & Lat.) or does your interface look different from mines? Tx.

GeoLocationField-EditDialogBox.png

Hi @Eric Deferm , try classic experience :)

Hi Federico, that's strange. The list from which I start is configured for Classic experience. Any idea what other configuration settings could have an impact?

Hi,

there is no difference from classic or modern experience in creation / configuration.

I see difference only in editing.

What about your screenshot? Is it modern experience?

Hi, tx for your reply. The screenshot is classic experience.
Related Conversations
External groups in Yammer EU Geo
Jennymh in Yammer on
1 Replies
Clarification on the Yammer EU Geo for the UK
Charlie_Lee in Yammer on
2 Replies