XL: CSV Text File with Smart Quotes Not Parsed As Expected

ID: Q103023

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

SYMPTOMS

The versions of Microsoft Excel listed above may incorrectly parse text files that contain curly quotation marks ("smart quotes").

WORKAROUND

For Microsoft Excel to correctly parse the data, you must manually set the points at which the text string should be parsed. The steps below describe how to do this:

Select the data in the worksheet that you want to parse.

On the Data menu, click Parse.

In the Parse line, use brackets to enclose the text that you want to remain together.

Click OK.

NOTE: This procedure must be done accurately; otherwise, you will receive a "Parse line error" message.

Or, before you import the text file in Microsoft Excel, open the file in any text editor that has a replace feature and replace the smart quotes with normal quotation marks.

MORE INFORMATION

In most word processors, when you create a comma-delimited file, text strings can be kept together by enclosing the string in "smart" quotes or in normal quotation marks, as in the following example:


   "123 Main Street, NE",123 South Street, WA

When you parse the above text string in Microsoft Excel, "NE" correctly remains part of the text string "123 Main Street, NE," and the text strings "123 South Street" and "WA" are correctly considered two separate text strings and parsed into separate columns.

However, if the text string above contains "smart" quotes instead of normal quotation marks, the text may not be parsed as expected. If the text file is imported with the delimiter set as a comma, the text string will be parsed as if the quotation marks were not there. That is, in the example above, the text "123 Main Street" is entered in one cell and "NE" in another.

REFERENCES

"User's Guide 2," version 4.0, pages 150-151
"User's Guide," version 3.0, page 340

Additional query words: 4.00a XL4 XL3 curly curled \* \* 3.0a 3.00a \* M_eXceL


Keywords          : 
Version           : WINDOWS:2.0,3.0,4.0,4.0a; OS/2:2.2,3.0; MACINTOSH:2.2,3.0,4.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        :

Last Reviewed: April 6, 1999