How to Import Fixed-Width Text with Literal Quotes into Excel
Question details
The user needs a way to import fixed-width text files containing literal double quotes into Excel without the wizard applying standard CSV parsing rules.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing a text file into a spreadsheet where the data is structured by fixed character widths but contains internal double quotes.
- Observed behavior
- Excel's default From Text/CSV wizard treats stand-alone double quotes as text qualifiers, incorrectly combining lines or altering the fixed-width data structure.
Ensure you have the exact file path of your source text file ready, and make a note of the character positions where your fixed-width columns need to be divided.
Use Power Query to Bypass CSV Quoting Rules
This method forces Excel to read the text file line-by-line in raw format, completely bypassing the default CSV quotation parsing that causes data merging.
By utilizing the Lines.FromBinary function in Power Query, Excel will treat every row exactly as it is written in the source file. This preserves all literal quotes.
In Excel, navigate to the Data tab, click on 'Get Data', select 'From Other Sources', and choose 'Blank Query'.
Click on 'Advanced Editor' in the Home tab. Replace the existing code with: Table.FromColumns({Lines.FromBinary(File.Contents("C:\your_file_path\quotes.txt"), null, null, 65001)})
Select the newly created column. Go to Home > Split Column > By Positions, and type in the specific character lengths where the columns should divide.
Look at the 'Applied Steps' pane on the right side. If there is a step named 'Changed Type', click the 'X' next to it to remove it. This ensures all values remain as verbatim text.
Click 'Close & Load' in the top-left corner to import the correctly formatted data into your Excel worksheet.
Use a VBA Macro with Line Input
A VBA script using the Line Input # statement reads the file line by line as raw strings, safely ignoring standard CSV text delimiters.
Try WPS Office for Seamless Data Handling
If you frequently struggle with Excel's complex data import rules and Power Query configurations, WPS Office provides a lightweight, highly compatible, and user-friendly alternative. WPS Spreadsheet supports robust data processing and traditional text import wizards that are easy to configure.
Frequently Asked Questions
Why does Excel mess up my fixed-width text import?
Excel's modern 'From Text/CSV' wizard automatically applies standard CSV quoting rules. If your fixed-width file contains stand-alone double quotes, Excel misinterprets them as text qualifiers, causing it to merge multiple lines or incorrectly shift columns.
Can I use the traditional Text Import Wizard in Excel instead of Power Query?
Yes. You can enable the legacy Text Import Wizard in Excel by going to File > Options > Data, and checking 'From Text (Legacy)' under the 'Show legacy data import wizards' section. However, depending on your exact data structure, the Power Query bypass method is often more reliable for literal quotes.
What does the 65001 code mean in the Power Query M-code?
The number 65001 is the Windows code page identifier for UTF-8 encoding. Including this in the Lines.FromBinary function ensures that any special characters in your text file are read and displayed correctly.
