How to import data in MySql table to SharePoint Online List using PowerShell

Question

I want to create a timer job in order to sync data from MySQL database to SharePoint List using PowerShell or C#.

How can i connect to the SharePoint online site and add the data batch?

Can anybody help me with it?

Answer 1

Hi @Andy ,
You can refer to following code to import data from MySql table to SharePoint Online List

#Get Sharepoint List  
$Web = Get-SPWeb "http://portal.crescent.com/ProjectPipeline/"  
$List = $Web.Lists["Projects"]  
   
$ConnectionString = "Server=CrescentSQL;Database=CRM_Integrated_DB;Integrated Security=True"  
$SPName = "rpt_Project_pipeline"  #Stored Procedure outputs data. This can be a table, view or sp  
   
#execute the Stored Procedure to Fetch Data  
$sqlConnection = new-object System.Data.SqlClient.SqlConnection  
$sqlConnection.ConnectionString = $ConnectionString  
$sqlConnection.Open()  
$sqlCommand = new-object System.Data.SqlClient.SqlCommand  
$sqlCommand.CommandTimeout = 120  
$sqlCommand.Connection = $sqlConnection  
$sqlCommand.CommandType=[System.Data.CommandType]'StoredProcedure'  
$sqlCommand.CommandText = $SPName  
      
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
$SqlAdapter.SelectCommand = $sqlCommand  
$DataSet = New-Object System.Data.DataSet  
$SqlAdapter.Fill($DataSet)  
$SqlConnection.Close()  
$sqlCommand.Dispose()  
   
## Fetch data from Stored Procedure  
write-host "Data Import Starts..."  
ForEach ($Project in $DataSet.Tables[0])  
{  
 #Update Existing Project if the match found  
    $SPQuery = New-Object Microsoft.SharePoint.SPQuery  
    $SPQuery.Query = "<Where><Eq><FieldRef Name='ProjectName' /><Value Type='Text'>" + $Project.'Project Name' + "</Value></Eq></Where>"  
    $ListItems = $List.GetItems($SPQuery)  
    if($ListItems.Count -gt 0)  
    {  
        foreach($Item in $ListItems)  
        {  
            #update existing Project data  
            $Item["Company Type"] = $Project.'Company Type'  
            $Item["Sector"] = $Project.'Sector'  
            $Item["Country"] = $Project.'Country'  
            $Item["Deal Lead"] = $Project.'Deal Lead'  
            $Item["Investment Date"] = $Project.'Investment Date'  
            $Item["Investment Amount"] = $Project.'Investment Amount'  
            $Item["Industry"] = $Project.'Industry'  
   
   try  
            {  
                $Item.Update()  
            }  
            catch  
            {  
                write-host "Item ID: " $Item["ID"] "not updated"  
            }  
        }     
    } # Update List Item complete  
     
   else #Add New Item in case no match for Project Name  
   {  
        if([string]::IsNullOrEmpty($Project.'Project Name')){  
            #write-host "Project Name is empty and data not updated"  
        }  
        else{  
            $newItem = $List.Items.Add()  
            $newItem["Project"] = $Project.'Project Name'  
            $newItem["Company Name"] = $Project.'Company Name'  
            $newItem["Company Type"] = $Project.'Company Type'  
            $newItem["Sector"] = $Project.'Sector'  
            $newItem["Country"] = $Project.'Country'  
            $newItem["Deal Lead"] = $Project.'Deal Lead'  
            $newItem["Investment Date"] = $Project.'Investment Date'  
            $newItem["Investment Amount"] = $Project.'Investment Amount'  
            $newItem["Industry"] = $Project.'Industry'  
          
            TRY  
            {  
                $newItem.Update()  
            }  
            CATCH  
            {  
                Write-Host "Project Name: " $Project.'Project Name' "NOT UPDATED FROM TABLE"  
            }  
        }  
    }  
}  
write-host "Data Import completed!"  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---