Data Import & Export

How to Read and Format Microsoft Purview Audit CSV Exports in Excel

Ayan Masood Sep 25, 2026 871 views

Question details

The user needs to transform disorganized Microsoft Purview audit CSV export data into a clean, readable spreadsheet format.

How to Read and Format Microsoft Purview Audit CSV Exports

Product: Microsoft Excel / Microsoft Purview
Device & OS: not provided
Scenario: Opening a Microsoft Purview audit CSV export directly in a spreadsheet results in disorganized data that is difficult to analyze.
Observed behavior: The CSV data appears cluttered in raw format, requiring parsing, splitting, and expanding of specific columns to become readable.

Before you start

Ensure you have exported the audit log from the Microsoft Purview Compliance portal and saved the CSV file to an easily accessible folder on your computer.

Solution 1Recommended

Use Power Query Editor in Excel to Format CSV Data

Importing the CSV via Power Query allows you to automatically split and expand complex columns into a clean data table.

Microsoft Purview audit logs often contain nested JSON data within a single column. Power Query provides built-in tools to parse and expand these records so they are properly separated into their own columns.

Import the CSV File

Open Excel, navigate to the 'Data' tab, click on 'Get Data' (or 'From Text/CSV'), and select your exported Purview audit CSV file.

Open Power Query Editor

In the preview window that appears, click 'Transform Data' to open the Power Query Editor instead of loading the raw data directly.

Parse and Expand Columns

Locate the column containing the JSON audit details. Right-click the column header, select 'Transform', and choose 'JSON'. Then click the expand icon at the top of the column to extract the nested properties into separate columns.

Load Cleaned Data

Once the data looks organized, click 'Close & Load' in the Home tab to import the formatted data into your Excel worksheet.

Use Power Query Editor in Excel to Format CSV Data

Save Query for Future Use: You can save this Excel workbook as a template. The next time you export a Purview audit log, you can simply refresh the query to format the new CSV automatically.

Solution 2

Parse Audit Records Using PowerShell

If you prefer scripting, PowerShell can pre-process and transform the CSV audit records before you open them in any spreadsheet application.

WPS Office Spreadsheet

Easily Import and Format CSV Data in WPS Office

WPS Spreadsheet provides powerful data import and text-to-column features, allowing you to quickly organize and format CSV exports without complex query setups.

1. Launch WPS Spreadsheet: Open WPS Office, create a new spreadsheet, and navigate to the 'Data' tab on the top ribbon.
2. Import the Data: Click 'Import Data' -> 'Import Data', choose 'Data Source', and select your Purview audit CSV file.
3. Run the Text Import Wizard: Follow the on-screen wizard to choose your delimiters (such as commas) to correctly parse the columns.
4. Split Complex Columns Manually: If specific columns contain joined data, select the column, click 'Text to Columns' under the Data tab, and define custom separators to further split the contents.

Seamlessly open and edit .csv, .xls, and .xlsx files with full Microsoft Excel compatibility.Use 'Text to Columns' to instantly split disorganized data by commas or custom delimiters.Lightweight and optimized for processing large spreadsheet files smoothly.Free to use with an intuitive, familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Microsoft Purview CSV look messy when opened directly?

When opened directly by double-clicking, all values and embedded JSON strings are placed into cells without proper parsing. The 'AuditData' column specifically contains a raw JSON payload that needs to be expanded into separate columns to be readable.

Can I format these CSV files without using Power Query?

Yes, you can use the 'Text to Columns' feature in your spreadsheet software to manually separate data using specific delimiters, or use PowerShell scripts to process and flatten the file before opening it.

Are there specific delimiters I should use when splitting Purview audit logs?

Purview audit logs generally use commas for standard columns. However, if you are manually splitting the internal AuditData JSON payload, you may need to use colons or quotation marks as custom delimiters to separate the keys and values.

Excel & Data Problems