'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Extract Raw SharePoint Choice Values for Data Migration
Question details
The user needs to retrieve and display the underlying raw values from SharePoint choice columns rather than the display labels for a data migration to SQL.
- Product
- SharePoint, Power Automate
- Device & OS
- not provided
- Scenario
- Performing a data migration where underlying raw choice values submitted via Power Apps are required for proper SQL database mapping.
- Observed behavior
- When exporting or viewing SharePoint list choice columns, the system displays user-friendly choice labels instead of the required underlying raw values.
Ensure you have the necessary permissions to create flows in Microsoft Power Automate for your SharePoint site, and prepare a destination Excel spreadsheet formatted with a table to receive the data.
Use Power Automate to Export Raw Values to Excel
Create a Power Automate flow to extract the underlying values of the choice columns and write them into a target Excel file for data migration.
SharePoint defaults to showing the display label of a choice column. To retrieve the backend raw value for migration mapping, you can use Power Automate to read the item data and populate an Excel table with the raw Choice values instead.
Log into Power Automate and create an 'Instant cloud flow' or 'Automated cloud flow'. Select the 'When an item is created or modified' trigger for SharePoint.
In the trigger configuration, select your Site Address and the specific List Name containing the choice columns you need to migrate.
Click 'New step' and search for 'Excel Online (Business)'. Select the 'Add a row into a table' action. Point this action to the target Excel file and table where your migration data will be stored.
Click into the fields of your Excel action to open the dynamic content menu. For your choice column, explicitly select the '[ColumnName] Value' dynamic content instead of '[ColumnName] Item' or Label.
Save your flow and trigger it manually or modify a list item. Open your Excel file to verify that the raw underlying values are now successfully populating the spreadsheet.
Use WPS Office to Manage Your Migrated Excel Data
While you need Power Automate to extract the SharePoint data, you can seamlessly open, clean, and prepare your exported Excel migration files using WPS Office. It provides comprehensive data management tools without expensive subscriptions.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Migration File: Launch WPS Spreadsheet and open the .xlsx file generated by your Power Automate flow.
- 3. Prepare for SQL Mapping: Use WPS Spreadsheet's built-in formulas and data cleaning tools to finalize your raw values for database mapping.
Frequently Asked Questions
Why does SharePoint export choice labels instead of raw values by default?
SharePoint prioritizes a user-friendly experience, so its default export tools output the display labels. To access the underlying raw data needed for backend systems, developers must use API calls or automation tools like Power Automate.
Can I display raw values directly in a SharePoint list view?
Yes, you can create an additional 'Calculated Column' in your SharePoint list. Set the formula to reference your existing choice column, and it will output the raw text value which can then be added to your default list view.
How do I handle multi-select choice columns in Power Automate?
Multi-select choice columns return an array of values. To extract them in Power Automate, you must use an 'Apply to each' loop or a 'Select' and 'Join' action to flatten the array of raw values into a single comma-separated string for Excel.
Can WPS Spreadsheet open the Excel files created by Power Automate?
Absolutely. Power Automate outputs standard Microsoft Excel (.xlsx) files. These files are fully compatible with WPS Spreadsheet, allowing you to open, edit, and analyze your migration data seamlessly.
