Automatic Data import

[Florent SEGUI]

Hello,

I'm putting up in place a sharepoint site (using Sharepoint Online) for my company.

I'd like to create an Infopath form that'll retrieve datas from a sharepoint list (customers list).

I'd like this list to be automatically updated from an external source (csv file, power bi...)

What would be the best way to proceed?

Thanks

[#2]

Hi @Florent SEGUI,

There are many ways to do this. Do you want to ijmport the data from the external sources or do you want to reference the data.

You could use external lists using the BDC/BCS to talk to your databases. Please be aware of this in Office 365. I've seen very poor performance on very wide tables.

You could use an import scripts (PowerShell could do this for you).

If you want to go for a coding solution then you could access your data maybe through web services.

Alternative option of course are creating a SharePoint Add-in or maybe even the new SPFx could be of some help.

[#3]

Hello Pieter,

Thanks for your quick reply.
We have a CRM that can export automatically the datas into csv or txt format.
So I think I'd go for the import solution from external source.

How to proceed?

I've exlcuded BCS as the CRM database has already some slowness.

[#4]

There might be a better way as you could let CRM do the update work. I could imagine creating a CRM module doing the work. I'm not 100% sure about the options available in CRM but I guess this could be a workflow in CRM doing the update work in SharePoint.

If you have the csv files ready already. then you could read these files using PowerShell and then update SharePoint. Personally I like the PnP PowerShell options available. https://github.com/SharePoint/PnP-PowerShell.

With these commands it is quite easy to create items in SharePoint and/or update them. How are your Powershell skills? I would expect that developing these scripts as somethign that is produciton ready could take a few days.

[#5]

For the CRM, I have to contact the software company developping it to check if something already exist.

As for my powershell skills, they are a bit rusty :) but I can try.

[#6]

Why do you want to use InfoPath? if users can ever be on a mobile device, their experience will not be very good.

You may want to take a look at PowerApps, you can set up a Data Gateway to provide access to on-prem data

[#7]

Hi,
Using csv is simplest method but you need to create list columns with corresponding csv header name.then try below script, here i create simple script with two column and two csv header so you can change column name and header value in the script and this script based on pnp sharepoint online powershell module so you need to install pnp sharepoint online powershell module . 

$cred= Get-Credential
$Listname= "Listname"
Connect-PnPOnline -Url https://TenantName.sharepoint.com/sites/contosobeta -Credentials $cred
$csv = Import-Csv "C:\Users\csv\users2.csv" 
$ctx=Get-PnPContext
$list= Get-PnPList -Identity $Listname
foreach ($line in $csv)  
{  

$listItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
    $listItem = $list.AddItem($listItemInfo)  
    $listItem["Listcolumn1"] = $line.Csvheadername1 
    $listItem["Listcolumn2"] = $line.Csvheadername2 
    $listItem.Update()      
    $ctx.load($list)      
    $ctx.executeQuery()  
}

Note:

Before run the script youneed modify following value in the script

$Listname= "Listname"
$listItem["Listcolumn1"] = $line.Csvheadername1 
$listItem["Listcolumn2"] = $line.Csvheadername2 

[#8]

Hi @Manidurai Mohanamariappan,

Your script will work for simple text fields but what if the SharePoint fields are of a diffferent type.

Also using Add-PnPListItem and Set-PnPListItem and Get-PnPListItem will be better.

Also you would need to handle things like "the item already exists"....

[#9]

Hi pieter,

thanks for info, i already tried with  Add-PnPListItem but it's make trobule so that i used csom within pnp script . 

[#10]

Use Add-PnPListItem without the Value parameter. Then use Set-PnPListItem to set the fields. This is a known issue.

https://github.com/SharePoint/PnP-PowerShell/issues/778