How to Parse UTF-16 CSV Files with Tabs and Line Breaks in Excel
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.

- 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.
Verify that your CSV file uses proper text qualifiers (usually double quotation marks) around any fields that contain internal line breaks or tabs.
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.
Open Excel, navigate to the 'Data' tab on the ribbon, and click on 'From Text/CSV'.
Locate and select your UTF-16 CSV file from your computer, then click 'Import'.
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).
Make sure 'Data Type Detection' is active. Check the preview grid to confirm that text containing line breaks is kept within a single cell.
Click 'Load' to import the correctly parsed data into your Excel worksheet.

Convert the CSV from UTF-16 to UTF-8 Encoding
Converting the file to UTF-8 before processing can simplify the parsing process, especially when using older versions of Excel or specific VBA macros.
Use Built-in Functions or VBA for Cleanup
If minor parsing errors occur during import, use Excel functions or VBA scripts to identify, handle, or clean up hidden control characters like carriage returns.
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. Navigate to Import Data: Open WPS Spreadsheet, go to the 'Data' tab, and click on 'Import Data'.
- 2. Choose File Source: Select 'Import Data' from the dropdown and browse to locate your UTF-16 CSV file.
- 3. Configure the Text Import Wizard: Choose 'Delimited' in the wizard and set your File Origin to Unicode UTF-16.
- 4. Set Text Qualifiers: Specify the text qualifier (usually double quotation marks) to protect fields containing embedded line breaks.
- 5. Finish the Import: Click 'Finish' to instantly load your flawlessly parsed data into the spreadsheet.

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.




