Data Import & Export

How to Parse UTF-16 CSV Files with Tabs and Line Breaks in Excel

Phi Hung VoPhi Hung Vo Oct 29, 2026 870 views

Question details

The user needs to correctly parse UTF-16 encoded CSV files that contain embedded tabs and line breaks without improperly splitting the data into new rows or columns.

How to Parse UTF-16 CSV Files with Tabs and Line Breaks in Excel
Product
Excel
Device & OS
not provided
Scenario
Importing or processing a complex UTF-16 CSV file where certain text fields contain carriage returns, line feeds, or tabs.
Observed behavior
Excel or basic parsers may mistakenly split data at every tab or line break if not strictly adhering to CSV quoting rules and the file's UTF-16 encoding.
Before you start

Verify that your CSV file uses proper text qualifiers (usually double quotation marks) around any fields that contain internal line breaks or tabs.

Solution 1Recommended

Parse Using Standard CSV Quoting Rules via Get & Transform Data

Use Excel's built-in Power Query tool to accurately interpret the UTF-16 file, honoring text qualifiers so that embedded tabs and line breaks remain inside their respective cells.

When dealing with UTF-16 CSV files, Excel relies on quotation marks to encapsulate fields that contain delimiter characters like tabs or line breaks.

If a field is properly enclosed in quotes, the Power Query import engine will keep the internal line breaks intact rather than starting a new row.

1
Launch the Import Wizard

Open Excel, navigate to the 'Data' tab on the ribbon, and click on 'From Text/CSV'.

2
Select and Open the File

Locate and select your UTF-16 CSV file from your computer, then click 'Import'.

3
Configure File Origin and Delimiter

In the preview window, ensure 'File Origin' is set to '1200: Unicode (UTF-16)'. Set the 'Delimiter' to the appropriate character (e.g., Comma or Tab).

4
Verify Data Type Detection

Make sure 'Data Type Detection' is active. Check the preview grid to confirm that text containing line breaks is kept within a single cell.

5
Load the Data

Click 'Load' to import the correctly parsed data into your Excel worksheet.

Parse Using Standard CSV Quoting Rules via Get & Transform Data
Advanced Data Handling

Easily Import Complex CSV Files in WPS Spreadsheet

WPS Spreadsheet features a highly intelligent text import wizard that accurately identifies UTF-16 encodings and applies standard CSV quoting rules. This ensures embedded line breaks and tabs are securely imported without manual data cleanup.

  1. 1. Navigate to Import Data: Open WPS Spreadsheet, go to the 'Data' tab, and click on 'Import Data'.
  2. 2. Choose File Source: Select 'Import Data' from the dropdown and browse to locate your UTF-16 CSV file.
  3. 3. Configure the Text Import Wizard: Choose 'Delimited' in the wizard and set your File Origin to Unicode UTF-16.
  4. 4. Set Text Qualifiers: Specify the text qualifier (usually double quotation marks) to protect fields containing embedded line breaks.
  5. 5. Finish the Import: Click 'Finish' to instantly load your flawlessly parsed data into the spreadsheet.
Seamless compatibility with Microsoft Excel CSV formatting and text qualifiers.Smart Text Import Wizard for accurate UTF-16 and UTF-8 parsing.Automatically honors quoted fields containing line breaks or delimiters.Free and lightweight alternative for robust data management tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel split my CSV data into new rows when there is a line break?

Excel splits data into new rows at line breaks if the text containing the line break is not properly enclosed in text qualifiers, typically double quotation marks. According to standard CSV rules, any field containing a delimiter or line break must be quoted.

What is the difference between UTF-16 and UTF-8 when parsing CSV files?

UTF-16 uses 2 or 4 bytes per character, while UTF-8 uses 1 to 4 bytes. While modern Excel handles both natively via Power Query, some older scripts or basic parsers default to UTF-8. Converting a UTF-16 file to UTF-8 can sometimes resolve import anomalies.

How do I remove invisible tabs and carriage returns in Excel after importing?

You can use the CLEAN function (e.g., =CLEAN(A1)) to strip out most non-printable characters. Alternatively, the Find and Replace tool allows you to search for line breaks by pressing Ctrl+J in the 'Find what' field and replacing them with spaces.

Can I use VBA to parse UTF-16 CSV files with quoted line breaks?

Yes, but standard VBA text stream reading (like Line Input) often splits exactly at carriage returns. To prevent data corruption, you must build or use an advanced CSV parsing function that reads character by character and ignores vbCr and vbLf if they appear inside a quoted string.