Keep Excel Data Linked When Refreshing a CSV File
Question details
Keep additional product or brand information associated with the correct records when a CSV file is refreshed.

- 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.
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.
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.
In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data to open it in Power Query.
On a separate worksheet, enter an ID column and the extra product or brand fields. Select the range and choose Insert > Table.
Select a cell in the information table, choose Data > From Table/Range, and use Close & Load To to create a connection.
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.
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.

Use XLOOKUP to match CSV data to an information table
Use a lookup when you want to maintain a separate list of IDs and retrieve fields from the refreshed CSV.
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. Open the CSV: In WPS Spreadsheets, select Open and choose the CSV file. Confirm that the ID column preserves its intended format.
- 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. 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. 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. 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.

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.




