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