Data Import & Export

Import Comma-Separated Excel Data into Access Relationships

Olivia MillerOlivia Miller Nov 13, 2026 869 views

Question details

The user needs to import comma-separated Excel values into an Access database and relate them to their original records.

Import Comma-Separated Excel Data into Access Relationships
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
An Excel column contains multiple comma-separated values for each record, and the user wants to represent those values in Access relationships.
Observed behavior
Importing the values into an Access multivalued field can fail and may create a structure that is difficult to maintain.
Before you start

Make sure each Excel source record has a unique ID and that commas consistently separate individual values. Identify the Access table that will hold the original records and check that its ID can be matched to the Excel IDs.

Solution 1Recommended

Split the values and create a junction table in Access

Use separate value and junction tables to model the data as a maintainable many-to-many relationship instead of importing into a multivalued field.

The steps below use RecordID for the original record key and ValueText for each split value. Adjust the table and column names to match your database.

If the original records are not already in Access, import them separately with one row per record and set RecordID as their primary key.

1
Split the Excel column into rows

In Excel, select the source data and choose Data > From Table/Range. In Power Query, select the comma-separated column, choose Split Column > By Delimiter, select a comma, and under Advanced options choose Split into Rows. Trim spaces from the resulting values using Transform > Format > Trim.

2
Import the split rows

Use Home > Close & Load in Power Query to load the result to a worksheet. In Access, choose External Data > New Data Source > From File > Excel and import the split rows into a staging table named Staging, retaining RecordID and ValueText.

3
Create the value table

In Access, use Create > Table Design to create a table named ValueTable with an AutoNumber primary key named ValueID and a Short Text field named ValueText. Set ValueText to Indexed: Yes (No Duplicates). In Create > Query Design > SQL View, run: INSERT INTO ValueTable (ValueText) SELECT DISTINCT ValueText FROM Staging WHERE ValueText Is Not Null AND ValueText <> '';

4
Create and populate the junction table

Use Create > Table Design to create a table named RecordValues with RecordID and ValueID fields that match the data types of their referenced keys. Select both fields and click Primary Key to create a composite key. In SQL View, run: INSERT INTO RecordValues (RecordID, ValueID) SELECT DISTINCT S.RecordID, V.ValueID FROM Staging AS S INNER JOIN ValueTable AS V ON S.ValueText = V.ValueText WHERE S.RecordID Is Not Null;

5
Define the relationships

Open Database Tools > Relationships and add the original records table, ValueTable, and RecordValues. Drag the original table's RecordID to RecordValues.RecordID, then drag ValueTable.ValueID to RecordValues.ValueID. Select Enforce Referential Integrity for each relationship and click Create.

Split the values and create a junction table in Access
Check key values: Every RecordID in the junction table must exist in the original records table before referential integrity can be enforced. If a referenced key is AutoNumber, its matching foreign key is typically Number with Field Size set to Long Integer.
Free Microsoft Office alternative

Prepare Excel Source Data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface and compatibility with Microsoft Office formats, making it easy to bring existing spreadsheets into your workflow. Use WPS Spreadsheets to review source data, then use Microsoft Access to build the database relationships.

Review record IDs and comma-separated source values in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceMove existing Office files into a lightweight, free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should I use a junction table instead of an Access multivalued field?

A junction table stores one link per record and value, making a many-to-many relationship easier to query and maintain. Create it in Access with foreign keys for the original record and the separate value table.

How can I split comma-separated values into separate rows in Excel?

Select the data and choose Data > From Table/Range. In Power Query, select the column, choose Split Column > By Delimiter, and select Split into Rows under Advanced options.

What if the same value appears for several records?

Store that value once in ValueTable, then add a separate RecordValues row for each original record linked to its ValueID. The composite primary key prevents duplicate links between the same record and value.

Why won't Access enforce referential integrity?

Check in Database Tools > Relationships that the linked fields have compatible data types and that every junction-table key has a matching record in its parent table. Remove or correct unmatched keys before enabling the option.