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.
This article uses two sample CSV files: Sales_Report_January.csv and Sales_Report_February.csv.When you open the links, you can download the files for free by clicking the download button in the top-right corner of your screen.Once downloaded, move both files to a new, dedicated folder on your computer.
Power Query tames messy CSV files instantly Fixing broken data automatically 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.Related You don't need VBA to auto-refresh your Power Queries in Excel Stop relying on manual clicks and clunky code—let Excel refresh your queries automatically.Posts 2 By Tony Phillips 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.
To follow along in this section, use the above-linked Sales_Report_January.csv file.Here's the workflow: Open a blank workbook and go to the Data tab.Click Get Data > From File > From Text/CSV.
Locate and select the CSV file, then click Import.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.
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.
This section continues from the previous step, so you should still be working with the imported Sales_Report_January.csv file.Here are some of the most useful cleanup actions you can perform: Split full names Right-click the Full Name column.Choose Split Column > By Delimiter.
Choose Space to separate the column into first and last names, and click OK.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.Microsoft 365 Personal 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.
$100 at Microsoft Expand Collapse 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: In the Applied Steps pane, select the first Changed Type step.Click the data type icon in the relevant column headers and select Text.
If prompted, click Insert to add the intermediate step.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: Hold Ctrl while selecting the Region and Internal Notes columns.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.Related 5 everyday actions that Power Query does better than regular Excel tools Replace manual Excel tasks with conditional columns, smart merging, the unpivot tool, and more.Posts By Tony Phillips 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.
This section starts a new workflow using both the original Sales_Report_January.csv and Sales_Report_February.csv files, so double-check that they're saved in the same folder before you start.Because this is a new workbook and a new query, your earlier single-file cleanup work won't carry over.First, follow these steps to connect Excel to the folder containing your reports: In a new Excel workbook, click Data > Get Data > From File > From Folder.
Select the dedicated folder containing both CSV files.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: Click Combine > Combine & Transform Data.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.Click OK.
In the Power Query Editor, review the combined data and perform additional cleanup if needed.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.
If you work with CSV files too large for a normal worksheet (millions of rows), load them into Excel's Data Model instead of a sheet.This lets you build PivotTables using the raw data without hitting Excel's row limits.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.
Read More
