Sync/Map/Copy Excel columns with SharePoint List columns in SP Online

Question

I have SP Online environment, where i am trying to map/sync/copy multiple excel column values to SharePoint list columns.

Scenario: I have a Excel spreadsheet with Question, Answer, Category and Name columns and about 3000 rows of data and this spreadsheet is updated regularly. I have a SharePoint list with Question, Answer, Category, Name, Date expiration, Search index (Metadata navigation column), and Published date as columns and about 2500 list items. The columns (Question, Answer, Category) of excel document and SharePoint list are same field/data types. Name column in excel is Text field, where as in SP list is People picker field. Is there a way to copy data/columns from excel on to SP list?

Note:

• I cannot import the excel spreadsheet on to SharePoint as excel has only 4 columns where as the list has 7 columns and there is search functionality associated with Search Index (metadata navigation)list column, so i cannot ignore it.
• The excel spreadsheet is updated regularly and more data will be
  added sequentially. So, i have to replace data on list with Excel
  data regularly.

Solutions tried:

• Quick Edit option, I tried to copy the excel data on to list. But as the list has 3 extra columns, the data doesn't get copied over.

• Tried to import the excel to list, this doesn't work in my case.

Any suggestions will be helpful. Thanks

Answer 1

I would think the copy/paste with Quick Edit would let you find a solution.

If not, you should investigate options with 3rd party software such as SharePoint List Synchronizer. This lets you synchronize your SharePoint list with an Excel file. Once in Excel, you can manipulate your SharePoint list, do advanced copy/paste with your other Excel file, and once you're happy with your changes, synchronize your changes back from Excel to the SharePoint list. Disclaimer: this product is edited by SoftFluent, my employer.