How to Open CSV Files Correctly in Excel｜Prevent Garbled Text and Data Corruption

Opening a CSV file in Excel may seem simple — just double-click and it opens, right?
However, that seemingly convenient method is also the most common cause of garbled characters, misaligned data, or corrupted numbers and dates.

If your CSV file contains Japanese, accented letters, or leading zeros (like “00123”), Excel’s automatic interpretation can easily alter or destroy your data.

In this guide, we’ll show you how to open CSV files correctly in Excel, how to prevent garbled text (文字化け), and how to maintain data integrity with the right settings and encoding.

This article covers step-by-step import methods, encoding options, and advanced tips for professionals managing multilingual or system-exported data.

---

✅ Why Opening CSV Files Properly Matters

CSV stands for Comma-Separated Values — a simple format where data fields are separated by commas or other delimiters.
Although Excel can open CSV files directly, it often interprets data automatically, causing problems such as:

• 文字化け (Garbled Text): When encoding (UTF-8, Shift-JIS, etc.) doesn’t match.
• Data Corruption: Numbers, codes, or leading zeros disappear (e.g., “00123” → “123”).
• Date Misinterpretation: “2025-10-15” becomes “Oct-15-25.”
• Column Shift Errors: Wrong delimiter detection (comma vs semicolon).

✅ Example:
A CSV file containing “アイテムA” (UTF-8) may appear as “アイテムA” when opened directly by double-click.

To avoid this, you need to control how Excel reads the file, including encoding, delimiter, and column formats.

---

✅ Step 1: Avoid Double-Clicking CSV Files

When you double-click a CSV file, Excel opens it using default system encoding (e.g., ANSI or Shift-JIS).
This method doesn’t let you specify the correct encoding or delimiter.

✅ Result:

• UTF-8 text often appears as garbled characters.
• Fields may shift if commas are misinterpreted.
• Numeric codes and dates auto-format incorrectly.

✅ Rule:
Never open CSVs by double-clicking — always import them manually with proper settings.

---

✅ Step 2: Open CSV Files Using Excel’s Import Function

The safest way to open CSV files is through Excel’s “Get Data” (Power Query) feature, which allows full control over encoding, delimiters, and data types.

・Steps

1. Open a blank Excel workbook.
2. Go to the Data tab.
3. Click Get Data → From File → From Text/CSV.
4. Browse and select your CSV file.
5. A preview window appears — review the content.
6. In File Origin, select the correct encoding (e.g., 65001: Unicode (UTF-8)).
7. Verify delimiter settings (Comma, Tab, or Semicolon).
8. Click Load to import the file into Excel.

✅ Result:
Excel reads your CSV exactly as intended — with proper characters, column separation, and data structure intact.

✅ Tip:
If the text still looks wrong, try changing File Origin to another encoding (e.g., Shift-JIS for Japanese systems).

---

✅ Step 3: Fixing Garbled Text (文字化け)

Garbled text (文字化け) occurs when the encoding of the CSV file differs from Excel’s reading method.

・Common Encodings

Encoding	Description	Use Case
UTF-8	Global standard for multilingual data	Recommended for all modern systems
Shift-JIS	Older Japanese encoding	Used in legacy systems
ANSI (Windows-31J)	Windows regional encoding	Default for older Excel versions

✅ How to Fix:
During import, explicitly select the correct encoding under “File Origin.”
If you’re unsure, open the CSV in Notepad and check its encoding via “Save As” dialog.

✅ Pro Tip:
If you frequently handle UTF-8 CSVs, change Excel’s default import behavior (see Step 9 for advanced settings).

---

✅ Step 4: Preventing Data Corruption in Numbers and Codes

Excel often interprets certain data types automatically:

• Postal codes like 012-3456 become dates.
• Product codes like 00123 lose leading zeros.
• Long numeric strings (e.g., account numbers) convert to scientific notation (1.23E+15).

To prevent this, define column data formats manually during import.

・Steps

1. Open Excel → Data → Get Data → From Text/CSV.
2. After selecting the file, click Transform Data.
3. In Power Query, select each column.
4. Change Data Type from “Any” to:
   • Text for IDs, codes, or numbers with leading zeros.
   • Whole Number for numeric data.
   • Date for dates (if formatted correctly).
5. Click Close & Load.

✅ Result:
Excel preserves your data exactly as in the source file — no auto-formatting errors.

---

✅ Step 5: Correctly Handling Delimiters (Comma, Tab, Semicolon)

Not all CSV files use commas.
Depending on your system’s regional settings, Excel might expect semicolons (;) instead of commas (,).

・How to Fix

1. During import, check Delimiter in the preview window.
2. Try selecting Semicolon or Tab if columns don’t align properly.

✅ Example:
Name;Age;City → Excel treats as one column unless you set delimiter to “Semicolon.”

✅ Tip:
Use “From Text/CSV” instead of “Open” so you can adjust this setting manually.

---

✅ Step 6: Using the Legacy Text Import Wizard (Excel 2019 and Earlier)

If you prefer the older import method, you can re-enable the Text Import Wizard.

・Steps to Enable

1. Go to File → Options → Data.
2. Check “From Text (Legacy)” under “Show legacy data import wizards.”
3. Click OK.

Now you can use it anytime:

1. Go to Data → Get Data → Legacy Wizards → From Text (Legacy).
2. Choose your CSV file.
3. In Step 1, select Delimited.
4. Choose File origin: 65001 (UTF-8) if necessary.
5. In Step 2, select the correct delimiter.
6. In Step 3, set each column’s format manually (e.g., “Text” for codes).
7. Click Finish → Load.

✅ Result:
This classic method still gives full control and prevents Excel’s auto-conversion errors.

---

✅ Step 7: Opening CSV Files with Power Query (Advanced)

Power Query provides even greater flexibility for automation and complex data preparation.

・Example Workflow

1. Go to Data → Get Data → From Text/CSV.
2. Click Transform Data.
3. Use Power Query Editor to:
   • Split columns by custom delimiters.
   • Replace or clean text.
   • Combine multiple CSV files from a folder.
   • Apply filters or rename columns.
4. Save the query and click Close & Load.

✅ Benefit:
Next time you refresh, Excel automatically re-imports the latest CSV with all settings preserved — no manual reconfiguration required.

✅ Use Case:
Perfect for daily sales, inventory, or system-exported reports.

---

✅ Step 8: Preventing Date and Time Misinterpretation

Excel often converts values like 2025-10-15 into “Oct-15-25” automatically, or treats them as serial numbers.

To prevent this:

1. During import, set column Data Type = “Text.”
2. If already imported, use TEXT function to reformat:
   =TEXT(A2,"yyyy-mm-dd")
3. Alternatively, use Power Query to change column type → Text before loading.

✅ Tip:
Keep raw date data in text form when exporting to systems that require consistent formats (e.g., “YYYY-MM-DD”).

---

✅ Step 9: Changing Excel’s Default Encoding for CSV Imports (Advanced Users)

⚠️ This step involves system settings and is for advanced users.

By default, Excel uses your Windows locale encoding when opening CSVs (e.g., Shift-JIS for Japanese).
You can modify this behavior to prioritize UTF-8.

・Option 1: Registry Setting

1. Press Windows + R, type regedit, press Enter.
2. Navigate to: HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Excel\Options (Replace <version> with 16.0 for Excel 2016/2019/365)
3. Right-click → New → String Value.
4. Name it: ImportUTF8
5. Set the value to "1".
6. Restart Excel.

✅ Result:
Excel now opens CSVs using UTF-8 by default, reducing garbled text in multilingual files.

---

・Option 2: Change System Locale to UTF-8

1. Go to Settings → Time & Language → Region → Administrative Language Settings.
2. Under “Region,” click Change system locale.
3. Check “Beta: Use Unicode UTF-8 for worldwide language support.”
4. Restart your computer.

✅ Effect:
UTF-8 becomes your default encoding system-wide, improving compatibility across apps — not just Excel.

✅ Caution:
Some older software may not handle UTF-8 system locale properly. Test before deploying on corporate systems.

---

✅ Step 10: Exporting CSV Files Back from Excel Correctly

When saving back to CSV, you must also ensure correct encoding and data preservation.

・Steps

1. Go to File → Save As.
2. Choose location.
3. Select CSV UTF-8 (Comma delimited) (*.csv) as file type.
4. Click Save.

✅ Result:
The file saves in UTF-8 encoding — fully compatible with most systems and web applications.

✅ Tip:
Avoid saving as standard “CSV (Comma delimited)” — it uses ANSI or Shift-JIS depending on locale, which may cause garbled output.

---

✅ Step 11: Testing CSV Integrity After Import

After opening a CSV file, verify that all characters and structures imported correctly.

・Checklist

• ✅ No garbled characters (especially Japanese or accented text).
• ✅ Column alignment matches source file.
• ✅ Numeric codes retain leading zeros.
• ✅ Dates appear correctly.
• ✅ Total record count matches original.

If discrepancies exist, reopen using Power Query and test different File Origin options.

---

✅ Step 12: Practical Business Use Cases

・1. Sales Data Imports

Import daily POS or e-commerce CSV exports while keeping SKU codes intact (avoid 00123 → 123).

・2. Accounting Systems

Load CSV exports from bookkeeping tools without corrupting Japanese characters or numbers.

・3. HR Attendance Reports

Prevent garbled text in employee names when using UTF-8 CSVs from cloud HR systems.

・4. ERP and CRM Integration

Ensure consistent encoding and field alignment when sharing CSV data between Excel and enterprise software.

✅ Result:
Properly opened CSV files maintain 100% data fidelity across systems and reduce manual corrections.

---

✅ Step 13: Troubleshooting Common Issues

Problem	Cause	Solution
Garbled text	Encoding mismatch	Choose UTF-8 or Shift-JIS manually during import
Columns not aligned	Wrong delimiter	Adjust delimiter in preview window
Missing zeros	Auto-number formatting	Set Data Type = Text
Date changed automatically	Auto-date conversion	Format column as Text
Foreign characters missing	Non-UTF-8 import	Use “CSV UTF-8” file type or Power Query

✅ Pro Tip:
Save your import configuration once in Power Query — you can reuse it anytime with one click.

---

✅ Summary: Open CSV Files in Excel the Right Way

• Don’t double-click CSV files — use Data → Get Data → From Text/CSV.
• Always select the correct encoding (UTF-8 recommended).
• Check and adjust delimiter settings.
• Define data types manually (Text, Number, Date) to prevent corruption.
• Save exports as CSV UTF-8 for consistent cross-system compatibility.
• Advanced users can modify registry or system locale for default UTF-8 behavior.

By mastering these import techniques, you’ll ensure every CSV file opens cleanly in Excel — without garbled characters, lost zeros, or misaligned columns.

With proper settings, Excel becomes a powerful data gateway that connects smoothly to your databases, ERP systems, and global business workflows — all while keeping your data accurate, readable, and reliable.