Data Import & Export

How to Map Excel Forms to Another Workbook for CRM Import

Amos Gikunda Sep 30, 2026 868 views

Question details

The user needs to map data from four varying Excel form templates into a single destination workbook with a strict format for Dynamics CRM import.

How to Map Excel Forms to Another Workbook for CRM Import

Product: Excel, Power Automate, Power Query
Device & OS: not provided
Scenario: Consolidating multiple inconsistent Excel forms into a standardized format for a CRM system.
Observed behavior: Data exists in four different layouts and must be systematically transformed and written into a fixed-format destination workbook.

Before you start

Ensure all source Excel workbooks and the destination workbook are formatted as Tables and saved in a cloud location like SharePoint or OneDrive if you plan to use Power Automate.

Solution 1Recommended

Use Power Automate to Map Cloud-Based Excel Files

Power Automate is ideal for automatically reading data from multiple cloud-stored Excel files and writing mapped values to a destination workbook.

Since the source forms are inconsistent, you must define a clear mapping table for each source template before creating your automation flow. Normalizing the source data first will prevent import errors in Dynamics CRM.

Store Files in the Cloud

Upload your four source Excel templates and your destination CRM workbook to SharePoint or OneDrive for Business.

Format Data as Tables

Open each Excel file, select your data range, and press Ctrl+T to format the data as a Table. Power Automate requires Tables to read and write rows.

Create a New Flow

Log into Power Automate, click 'Create', and select an 'Automated cloud flow' or 'Instant cloud flow' depending on your preferred trigger.

Extract and Map Data

Add the 'List rows present in a table' action for your source files. Then, add the 'Add a row into a table' action for your destination file, manually mapping the dynamic content from the source fields to the correct CRM destination columns.

Use Power Automate to Map Cloud-Based Excel Files

Community Support: For highly specific field mapping syntax or flow troubleshooting, consult the Microsoft Power Automate Community forums.

Solution 2

Use Power Query for Complex Transformations

If your data requires heavy normalization or you prefer working directly within the Excel desktop application, Power Query is the most robust tool.

Map and Consolidate Excel Forms Using WPS Spreadsheet

WPS Spreadsheet provides powerful built-in data tools to map, consolidate, and export inconsistent forms into a single standardized XLSX file for CRM import.

1. Open All Workbooks: Launch WPS Spreadsheet and open your four source templates along with the destination CRM format workbook.
2. Standardize the Data: Use WPS Spreadsheet's built-in formatting tools and functions like VLOOKUP or XLOOKUP to map inconsistent fields to the correct columns.
3. Consolidate the Forms: Navigate to the Data tab and use the 'Consolidate' feature, or simply copy the mapped tables into your final master sheet.
4. Export for CRM: Click 'Menu', select 'Save As', and choose the standard .xlsx or .csv format required by your Dynamics CRM system.

Fully compatible with Microsoft Excel (.xlsx) formats used by CRM systemsBuilt-in data consolidation and mapping toolsLightweight application that processes large data files quicklyAdvanced filtering and formatting to normalize inconsistent source data

microsoft office alternative - wps office

Frequently Asked Questions

Can I automate Excel data mapping without Power Automate?

Yes. You can use Power Query directly within Excel to map and transform data, or use VBA macros to write customized scripts that pull data from inconsistent forms into your master template.

Why is my destination workbook formatting breaking during CRM import?

Inconsistent source data types often cause formatting errors. Ensure you normalize your source data, such as standardizing date formats and converting text to numbers, before loading it into the final CRM import workbook.

What is the best file format for Dynamics CRM data imports?

Dynamics CRM typically accepts standard Excel (.xlsx) or Comma Separated Values (.csv) files. Ensure your final mapped workbook contains only the required headers and data rows, without any extraneous formatting or blank sheets.

Excel & Data Problems