snippets/microsoft/ms-office/access/Import-multivalued-data-into-SharePoint.md

Load multivalued data into a SharePoint list

Problem Statement

We have

• A SharePoint list with a multivalued lookup list referring to another table
• An Excel sheet with comma-seperated mutlivalued data

We want

• Import the Excel sheet into SharePoint via Access

Issue

• Access is not capable to translate the multivalued fields during the import :-(

Steps to Workaround

1. Prepare your data in Excel for import

   1. Split your data into two tables, one with and another without the multivalued field. Both tables have to be equipped with a unique ID to match them together in a later step.
   2. Load the table with the lookup references into Excel
   3. In the table with the multivalued fields, translate the bound display values in bound values using vreplace-function
      

2. Open the SharePoint list as linked table in Access

   1. Import the data from Excel, without multivalued fields into the SharePoint list
   2. Import the data with the multivalued data into a temporary table
   3. Run the macro to populate the multivalued fields
      

Reference

• Query MVFs
• Macro to translate CSV to MVF