How to populate sharepoint list with excel data?

1. Go to your list
2. Create a view with same columns than your Excel
3. In this view, go to Ribbon / List / Quick Edit

4. Copy your data from Excel
5. Paste your data in SharePoint using Quick Edit

I cobbled together a CSOM script a while ago that does this and works a lot better than copying and pasting data in in Quick View. Note to run this you'll need to import the Microsoft.Sharepoint.Client namespace, I always run it in the Sharepoint Online Management Shell.

//Set filename

$file = "local path to your Excel file"

// Set the sheet name to use

$sheetName = "Sheet1"

// Create Excel COM object

$objExcel = New-Object -ComObject Excel.Application

// Open the file

$workbook = $objExcel.Workbooks.Open($file)

// Open the sheet

$sheet = $workbook.Worksheets.Item($sheetName)

// Make sure the window is hidden (remove this if you need to make sure Excel is opening with the right file)

$objExcel.Visible=$false

// Get the number of rows

$rowMax = ($sheet.UsedRange.Rows).count

// For each column you want to get, set an X and Y start position (i.e. row and column). Create these two variables for every piece of data being loaded in (in the example below my cross reference is row 2, column A)

$rowDataSet1=2
$colDataSet1=1

// Set Sharepoint variables for your site

$Site = "site collection url"
$DocLib = "list name"
$name = "username@tenant or domain\username"
$pwd = "password"

// Open site context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Site)
$Credentials = New-Object System.Net.NetworkCredential($name, $pwd)
$Context.Credentials = $Credentials

// Load the list

$List = $Context.Web.Lists.GetByTitle($DocLib)
$Context.Load($List)
$Context.ExecuteQuery()

// Loop thru each row - this is where you put in your cross reference variables you created before, using them to assign the cell contents to a new variable

for ($i=1; $i -le $rowMax; $i++)
{
$valofDataSet1 = $sheet.Cells.Item($rowDataSet1+$i,$colDataSet1).text

// Create and populate list item - remember you need to use the STATIC NAME of the field, not the DISPLAY NAME (edit the column and inspect the URL to get this)

$ListItemCreationInformation = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$NewListItem = $List.AddItem($ListItemCreationInformation)
$NewListItem["ColumnStaticName"] = $valofDataSet1

$NewListItem.Update()
$context.load($newlistitem)
$Context.ExecuteQuery()
}

// Quit Excel

$objExcel.quit()