Import a CSV file into Excel and fix common errors

Importing a CSV file into Excel often seems like a simple operation — open the file and everything displays. In practice, this action hides several pitfalls: incorrect character encoding, merged columns, dates converted into numbers, or inconsistent separators that confuse the data. This guide takes you step-by-step, from preliminary checks to fine corrections and automation, so that the import becomes reproducible and reliable.

In brief

Why CSV files cause so many problems

A CSV (Comma-Separated Values) is intentionally simple: lines and columns in text form. This simplicity is also its weakness. The format does not standardize character encoding, field separators, or the way to indicate values containing line breaks. Result: the same file opened on two machines can display differently depending on the operating system, Excel’s regional settings, or the application that generated it.

Common cases

You will often see recurring problems: accents turned into symbols, columns stuck together because the expected separator was not the one used, or dates read as numbers. If the data is financial, a misinterpretation of the decimal separator (comma vs period) can reverse the interpretation of amounts.

Preliminary steps before import

Before opening the file, do some simple checks that will save you long manipulations later.

1. Open the CSV in a text editor

A basic editor (Notepad, TextEdit, Visual Studio Code) allows you to observe the separator, the presence of headers, and the apparent encoding. Note if fields are surrounded by quotes (“). This indicates that the generator protected commas inside the fields.

2. Check the encoding

If you see characters like � or strange sequences instead of accents, there is probably an encoding problem. If possible, ask the file source for the encoding used or convert the file to UTF-8 without BOM, which is the most robust for modern Excel.

3. Check regional settings

Excel sometimes adapts the decimal separator and date format to the regional settings of Windows or macOS. If you work with international colleagues, confirm whether the file uses a comma or a period as the decimal separator and whether the field separator is a comma, semicolon, or tab.

Import the CSV with Excel’s wizard

Opening the file directly by double-clicking may work, but the import wizard (or the “Data > From Text/CSV” option) gives you the necessary control to avoid unpleasant surprises.

Quick steps

• In Excel, go to Data then From Text/CSV.
• Select the file. Excel then offers a preview and auto-detects the encoding and separator; verify these choices.
• If necessary, change the encoding (UTF-8 recommended) and force the separator (comma, semicolon, tab).
• Choose the column format for each field (Text for codes that should not be converted to numbers, Date for dates, etc.).
• Validate and import into a new or existing sheet.

Use Power Query for recurring imports

Power Query (In the Data tab > Get & Transform) is the tool that turns a one-time import into a reproducible flow. You clean once, then apply the same rules to new versions of the file.

Advantages of Power Query

• Automatic normalization of columns and encoding conversion.
• Filtering and removal of empty rows or duplicates before arriving in the Excel model.
• Ability to write advanced transformations (split, merge, replace, type transformation) without macros.

Fix common errors — summary table

Here is a summary table identifying common problems and the fastest method to fix them.

Problem	Symptom	Quick solution
Wrong encoding	Unreadable accented characters	Re-import in UTF-8 or convert the file via an editor; use the encoding option in the wizard
Incorrect separator	Columns stuck in a single cell	Force the separator (comma/semicolon/tab) in the wizard or replace the separator beforehand
Dates transformed	Dates as numbers or US/DD/MM format	Import as text then convert via Date.Series or use formatting and the DATEVALUE function
Loss of leading zeros	Postal codes or truncated IDs	Set the column to Text during import
Multi-line field	Line breaks breaking row alignment	Check for quotes around fields and enable quote handling

Concrete examples of corrections

If your amounts use a comma as the decimal separator and a semicolon as the field separator (common case in Europe), Excel may misinterpret the columns. In practice, you first import leaving the columns as text, then replace commas with periods in the amounts column and then convert to number. This technique avoids loss of information and facilitates consistency checks.

Bulk column conversion

With Power Query, select the concerned column, apply Replace Values (comma -> period) then change the type to Decimal. The process is recorded in the steps and automatically reapplied to the next file.

Best Practices to Avoid Errors

• Ask the file exporter for the encoding and the separator used.
• Prefer sending in UTF-8 and with a stable separator (tabulation if commas may appear in the texts).
• Avoid ambiguous date formats; provide an ISO format (YYYY-MM-DD) if possible.
• Use Power Query to automate and document the transformation.

Tips for Automation and Verification

After importing, always perform a quality check: counts per column (number of rows), sum of amounts, error search (unexpected null values, inconsistent string lengths). These quick checks detect serious problems before you use the data in analyses or reports.

Quick Checklist

• Do the headers match the expected fields?
• No extra columns or undesired merges?
• Are sums and totals consistent with the figures provided by the source?
• Uniform date and number formats?

FAQ

Why do my accents display incorrectly even after choosing UTF-8?

Sometimes the file contains a BOM (Byte Order Mark) or was encoded in a particular subset. Open it in an editor like Visual Studio Code and explicitly re-save it as UTF-8 without BOM. If the problem persists, the source may have exported in ANSI : request a re-export.

How to force Excel not to automatically convert a field to a date?

During import, choose the Text type for that column. If you already have the information in a sheet, temporarily prefix the value with an apostrophe or use Power Query to set the text type before any conversion.

My file contains thousands of rows, how to quickly verify integrity?

Use aggregations: calculate the number of rows, the sum of amounts, and a distinct count on identifiers. Any variation from expected values signals an anomaly. Power Query allows automating these checks at each import.

What to do if columns appear shifted because a field contains separators?

Fields containing separators must be enclosed in quotes. If not, you will need to preprocess the file to encapsulate these fields, or use a script (Python, awk) to correctly reconstruct the columns before import.

Quick Resources

To go further, explore Excel’s documentation on text import and practice on samples representing problematic cases from your source: mixed encoding, multiline fields, numeric formats. Repetition will save you time and reduce human errors.

{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Importing and Correcting a CSV File in Excel”
},
“keywords”: [“import CSV”, “Excel”, “separator”, “encoding”, “Power Query”]
}