Data Import & Export

How to Handle CSV Encoding, Delimiters, and Regional Settings in Excel

John WilsonJohn Wilson Nov 3, 2026 873 views

Question details

Users need to properly import and export CSV files in Excel without data corruption caused by encoding, delimiters, or regional setting mismatches.

How to Handle CSV Encoding, Delimiters, and Regional Settings in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Importing or exporting CSV files that use specific text encodings (like UTF-8 or Windows-1252) and custom delimiters.
Observed behavior
CSV data may appear garbled, columns might not separate correctly, or date formats may change unexpectedly due to regional settings and locale mismatches.
Before you start

Before modifying any system regional settings, verify the original encoding (such as UTF-8) and delimiter (comma, semicolon, or tab) used in your source CSV file.

Solution 1Recommended

Use Power Query for CSV Import

Power Query is the safest method to handle CSV files because it allows you to explicitly set the locale, encoding, and delimiter without changing your computer's global system settings.

Relying on default double-click actions to open CSV files often leads to garbled text or incorrect column parsing. By using Power Query, you can bypass the global Windows regional settings and instruct Excel exactly how to interpret the data.

1
Launch the Import Tool

Open a blank Excel workbook, go to the 'Data' tab on the ribbon, and click 'From Text/CSV' in the Get & Transform Data group.

2
Select the File

Browse your computer for the target CSV file, select it, and click 'Import'.

3
Adjust File Origin and Delimiter

In the preview window that appears, change the 'File Origin' drop-down to match your file's encoding (e.g., '65001: Unicode (UTF-8)') and select the correct 'Delimiter' (such as Comma or Semicolon) based on the data preview.

4
Load the Data

Once the preview looks correct, click 'Load' to import the formatted data directly into your worksheet.

Use Power Query for CSV Import
Pro Tip: If your dates are still formatting incorrectly, click 'Transform Data' instead of 'Load'. In the Power Query Editor, you can right-click the column header, select 'Change Type', and choose 'Using Locale' to specify the exact region for that column.
Manage CSV Data Easily

Import and Export CSV Files Seamlessly with WPS Office

WPS Spreadsheet provides a straightforward, user-friendly text import wizard that lets you easily select custom delimiters, assign text encodings like UTF-8, and manage data formats without complicated Power Query steps.

  1. 1. Open WPS Spreadsheet: Launch WPS Office, open a new Spreadsheet, and navigate to the 'Data' tab on the top ribbon.
  2. 2. Launch the Import Wizard: Click on 'Import Data' and select the 'Import Data' option again to launch the text import wizard. Browse and open your CSV file.
  3. 3. Configure Encoding and Delimiters: Select 'Delimited', set your specific file origin encoding (such as UTF-8) from the drop-down menu, and check the box for the appropriate delimiter (comma, tab, semicolon, etc.).
  4. 4. Finalize and Import: Preview your columns, assign specific data formats (like Text or Date) to individual columns if necessary, and click 'Finish' to successfully load your data.
Built-in Text Import Wizard handles delimiters and UTF-8 encoding effortlessly.Fully compatible with Microsoft Excel formats (.xls, .xlsx, .csv).Lightweight and runs smoothly on both Windows and Mac.Free to use for everyday data management and spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why are special characters garbled when I open a CSV in Excel?

This happens when the CSV file is saved in a specific encoding like UTF-8, but Excel opens it using the default system ANSI encoding. To fix this, do not double-click the file. Instead, open a blank workbook, use the 'From Text/CSV' import feature, and explicitly select UTF-8 as the file origin.

Does changing Windows regional settings affect my CSV files?

Yes, Excel relies heavily on Windows regional settings to determine the default list separator (comma vs. semicolon) and default date formats. However, it is highly recommended to use Power Query's locale settings for imports instead of changing your global system settings.

How can I export a CSV file with UTF-8 encoding?

When saving your file in Excel, choose 'File' > 'Save As', open the format drop-down list, and select 'CSV UTF-8 (Comma delimited)' rather than the standard 'CSV (Comma delimited)' option.

Why is all my CSV data crammed into a single column?

This occurs when the delimiter used in the CSV (e.g., a comma) does not match the default list separator defined by your computer's regional settings (e.g., a semicolon). You can fix this by using the 'Text to Columns' feature in the Data tab, or by importing the file via Power Query where you can manually define the delimiter.