'%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 Split JSON Data from CSV in Excel with Power Query
Question details
The user needs to import weekly CSV files containing JSON-formatted strings into Excel and separate the JSON keys and values into distinct columns automatically.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Processing weekly CSV data exports where specific columns contain embedded JSON data that must be extracted and formatted for data analysis.
- Observed behavior
- The user wants to establish a repeatable workflow so that when a new CSV export is generated each week, the JSON splitting transformation can be applied without repeating the steps manually.
Ensure your weekly CSV exports are saved in a consistent folder location with the exact same file name and structure so that Power Query can seamlessly locate and refresh the data.
Use Power Query to Parse JSON and Automate Data Refresh
This method allows you to import the CSV, transform the JSON strings into separate columns, and refresh the query weekly without redoing the transformation work.
Power Query is a powerful data transformation engine built into Excel. By recording your transformation steps, it creates a pipeline that automatically processes new data whenever the source file is updated.
Open Excel, navigate to the 'Data' tab on the ribbon, and select 'Get Data' > 'From File' > 'From Text/CSV'. Locate your weekly CSV export, click 'Import', and then click 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, right-click the header of the column containing your JSON data. Select 'Transform' > 'JSON' from the context menu. This action converts the text strings into recognizable Record objects.
Click the expand icon (two diverging arrows) located at the top right of the newly transformed column header. Uncheck 'Use original column name as prefix' if you prefer cleaner headers, select the specific data fields you want to extract, and click 'OK'.
Once the JSON attributes are split into distinct columns, go to the 'Home' tab in the Power Query Editor and click 'Close & Load'. The parsed data will be loaded into a new Excel table.
When you receive your next weekly CSV export, save it over the old file in the same folder location to replace it. Open your Excel workbook, navigate to the 'Data' tab, and click 'Refresh All'. The JSON split transformation will automatically apply to the new data.
Try WPS Office for Seamless Data Processing and Analysis
While Power Query is a Microsoft-specific feature, WPS Office provides a highly compatible, lightweight, and free alternative for managing spreadsheets, importing data, and handling CSV files with a familiar interface that requires zero learning curve.
- 1. Download and install: Visit the official WPS Office website and download the free version suited for your operating system.
- 2. Open your CSV file: Launch WPS Spreadsheet and open your exported CSV file directly to view and manage the raw data.
- 3. Process your data: Navigate to the Data tab and utilize WPS Spreadsheet's built-in Text-to-Columns or Smart Split features to organize delimited data efficiently.
Frequently Asked Questions
Why am I getting an error when parsing JSON in Power Query?
Errors usually occur if the JSON string is poorly formatted, contains unescaped quotes, or has inconsistent structures across rows. Ensure your source CSV properly formats and escapes JSON delimiters before importing.
Can I change the source file location for my Power Query later?
Yes. Open the Power Query Editor, click on 'Data source settings' under the Home tab, and select 'Change Source' to browse for the new folder or file path of your CSV export.
How do I split multiple JSON columns in the same CSV?
You can repeat the 'Transform > JSON' and 'Expand' steps for each individual column containing JSON data within the same Power Query Editor session before finally clicking 'Close & Load'.
Does clicking 'Refresh All' update pivot tables connected to this data?
Yes, clicking 'Data > Refresh All' in Excel will first update the Power Query table from the newly saved CSV file and subsequently update any PivotTables that rely on that loaded table.
