Data Import & Export

Keep Excel Data Linked When Refreshing a CSV File

Steve KSteve K Nov 20, 2026 868 views

Question details

Keep additional product or brand information associated with the correct records when a CSV file is refreshed.

Keep Excel Data Linked When Refreshing a CSV File
Product
Microsoft Excel
Device & OS
not provided
Scenario
A CSV import gains or loses rows, while extra information must remain linked to each record.
Observed behavior
Information associated by row position can become misaligned after the CSV data changes.
Before you start

Identify a unique ID that stays the same for each record across CSV updates. Make sure the ID uses the same format in both datasets, including any leading zeros.

Solution 1Recommended

Merge the CSV with a separate information table in Power Query

Use this method to rebuild a combined view when the CSV gains or loses records.

Keep the additional information in an editable Excel table, not in the merged query output. Each ID should appear only once in the additional information table.

1
Import the CSV

In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data to open it in Power Query.

2
Create the information table

On a separate worksheet, enter an ID column and the extra product or brand fields. Select the range and choose Insert > Table.

3
Load the table into Power Query

Select a cell in the information table, choose Data > From Table/Range, and use Close & Load To to create a connection.

4
Merge by ID

Open the CSV query from Data > Queries & Connections. In Power Query, choose Home > Merge Queries, select the information table, and select the ID column in both tables. Choose a left outer join to retain all CSV records.

5
Expand and refresh

Use the expand button on the merged column to select the extra fields, then choose Home > Close & Load. After updating the CSV or information table, select Data > Refresh All.

Merge the CSV with a separate information table in Power Query
Unmatched records: New CSV IDs without a matching information-table entry return blank values for the extra fields. Add those IDs and their details to the information table, then refresh again.
Work with CSV data in WPS Office

Match refreshed CSV records by ID in WPS Spreadsheets

Use a separate ID-based information sheet and a VLOOKUP formula in WPS Spreadsheets to keep your extra details associated with the right CSV records.

  1. 1. Open the CSV: In WPS Spreadsheets, select Open and choose the CSV file. Confirm that the ID column preserves its intended format.
  2. 2. Create a workbook: Choose File > Save As and save an XLSX workbook. Rename the CSV data sheet Data, with IDs in column A and a field to retrieve in column B.
  3. 3. Store extra information separately: Add a worksheet for your extra details. Enter the stable IDs in column A and your additional information in other columns.
  4. 4. Look up CSV values: In an empty column beside the first ID, enter =IFERROR(VLOOKUP(A2,Data!$A:$B,2,FALSE),"Not found") and fill the formula down. Adjust the range and column number for the field you need.
  5. 5. Replace the CSV data: When a new CSV arrives, open it in WPS Spreadsheets. Clear the old data range on the Data sheet, then copy and paste the new CSV contents there with IDs still in column A. Add any new IDs to the extra-information sheet.
Match records by a stable ID instead of row positionOpen CSV files and work with Microsoft Excel XLSX filesKeep editable product or brand details separate from imported dataUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why do my extra details move to the wrong records after a CSV refresh?

If details are associated by row position, inserted, removed, or reordered CSV rows can shift the relationship. Store the details with a stable unique ID and match on that ID instead.

What happens when a new CSV record has no matching extra information?

A left outer merge keeps the CSV record but leaves the extra fields blank. Add its ID and details to the information table, then select Data > Refresh All.

Can I use VLOOKUP instead of XLOOKUP?

Yes. Use VLOOKUP with FALSE for an exact match, and place the ID in the first column of the lookup range. For example, =VLOOKUP(A2,Data!$A:$B,2,FALSE) returns the matching value from column B.

Why is an ID not matching even though it looks the same?

Check whether one ID is stored as text and the other as a number, or whether leading zeros were lost. Set consistent data types when importing the CSV and in the information table.

Should I type extra details into the Power Query output?

Enter them in the separate source information table instead. The query output is rebuilt during refresh, so it is not the right place to maintain manual entries.