Web apps love exporting CSV files, but they don’t always play nice with Excel. Leading zeros disappear, dates break, and text fields merge together. Most people waste time fixing these problems manually, even though Excel includes a built-in Power Query tool that automatically cleans and reshapes messy data in seconds.

Power Query tames messy CSV files instantly

Fixing broken data automatically

Credit: Lucas Gouveia/How-To Geek

The secret to effortless file cleanup is Power Query, Microsoft’s tool for importing and reshaping data in Excel. Think of it as a built-in data cleaning engine that handles the tedious work for you. Instead of fixing errors cell by cell in a worksheet, you define steps in a separate editing window. Your original CSV stays untouched because Power Query only imports and transforms a copy.

Power Query saves your transformations as reusable steps that run again whenever you refresh the query. Because it remembers everything you do, future imports with the same layout can be updated by simply clicking Data > Refresh All. Excel then automatically reapplies all your steps, eliminating repetitive CSV cleanup from your workflow.

Step 1: Import your CSV files into Excel the smart way

Starting with the right import process

To clean up your files properly, you need to import them through Excel’s data tools instead of simply double-clicking the CSV file.

Here’s the workflow:

1. Open a blank workbook and go to the Data tab.
2. Click Get Data > From File > From Text/CSV.
3. Locate and select the CSV file, then click Import.
4. Review the preview window to make sure Excel has correctly detected the delimiter (usually a comma). If the data is not properly split into columns, change the delimiter.
5. Click Transform Data.

This opens the Power Query Editor workspace where all cleaning happens before the data reaches Excel.

Step 2: Clean up CSV formatting with a few clicks

Scrubbing the junk

Once the Power Query Editor opens, you can start fixing the issues that usually make raw CSV files difficult to work with.

Here are some of the most useful cleanup actions you can perform:

Split full names

1. Right-click the Full Name column.
2. Choose Split Column > By Delimiter.
3. Choose Space to separate the column into first and last names, and click OK.
4. Double-click the new column headers to rename them.

Fix inconsistent date formats

Click the data type icon in the Order Date column header and set it to Date.

Because this column contains mixed date formats (US, EU, and text-based), Power Query may not interpret every value correctly. As a result, some cells in this column will show Error, while the rest of the row remains intact.

Don’t try to fix every invalid date manually. Keep the dataset usable and continue cleaning other fields first. You can handle or filter invalid dates later during analysis or reporting.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.

Preserve ZIP codes and product IDs

If ZIP codes like 02138 or Product IDs like 000245 lose leading zeros, Power Query has likely inferred a numeric type during the automatic Changed Type step.

To fix this:

1. In the Applied Steps pane, select the first Changed Type step.
2. Click the data type icon in the relevant column headers and select Text.
3. If prompted, click Insert to add the intermediate step.
4. Finally, click Replace current to update the existing step instead of creating a new one.

The leading zeros are now preserved correctly when the data is loaded into Excel.

Remove unnecessary columns

To keep the dataset focused on useful fields like customer, order, and product data:

1. Hold Ctrl while selecting the Region and Internal Notes columns.
2. Right-click one of the selected columns and choose Remove Columns.

Load the cleaned dataset

Once you’re happy with how your data looks, click Close & Load in the Home tab to load the cleaned Sales_Report_January.csv into Excel as a structured table.

At this point, you’ve turned a messy raw CSV into something usable in seconds.

Pro tip: Combine multiple CSV reports into a single sheet

Automating repetitive merges

Instead of repeating cleanup steps, Power Query can apply the same transformation logic across multiple CSV files in a folder.

First, follow these steps to connect Excel to the folder containing your reports:

1. In a new Excel workbook, click Data > Get Data > From File > From Folder.
2. Select the dedicated folder containing both CSV files.
3. Click Open.

This tells Excel to treat every CSV in the folder as part of a single combined table.

Next, define how the combined data should be structured:

1. Click Combine > Combine & Transform Data.
2. Choose the file you want to act as the sample file—usually the first or most complete report—and make sure the correct delimiter is selected (usually a comma) so that the columns are correctly separated.
3. Click OK.
4. In the Power Query Editor, review the combined data and perform additional cleanup if needed.
5. Click Close & Load in the Home tab to send the combined, cleaned report to a new Excel worksheet.

From here, whenever you drop new files into the folder, just click Data > Refresh All to update the query.

The automated analytics advantage

Power Query handles two of the hardest parts of working with spreadsheet data: cleaning messy CSV imports and turning them into structured tables for analysis. It helps you spend less time preparing information and more time working with it—and crucially, it’s one of the main reasons many people prefer Excel to Google Sheets and open-source alternatives.