GeoLocations - Upload values to existing/new List

[Eric Deferm]

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.

[#2 · Accepted 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...

MS 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...

[#3]

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();
}
}

[#4]

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

[#5]

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.

[#6]

Hi @Eric Deferm , try classic experience :)

[#7]

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?

[#8]

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?

[#9]

Hi, tx for your reply. The screenshot is classic experience.