Forum Discussion

EricDeferm's avatar
EricDeferm
Steel Contributor
Feb 21, 2019
Solved

GeoLocations - Upload values to existing/new List

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-to-a-list-programmatically-in-sharepoint

Tx & brgds.

8 Replies

  • Hi EricDeferm

    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/create-an-item-in-sharepoint-for-a-selected-row/

    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-sharepoint-add-ins or an Azure Function using webhooks https://docs.microsoft.com/en-us/sharepoint/dev/apis/webhooks/sharepoint-webhooks-using-azure-functions , 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-geolocation-field-compatibility-with-excel-flo 

    • EricDeferm's avatar
      EricDeferm
      Steel Contributor

      Tx FedericoPorceddu82  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-functionality-in-sharepoint
      // https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-add-a-geolocation-column-to-a-list-programmatically-in-sharepoint
      // 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 EricDeferm , 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

Resources