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

 Last reviewed: February 2, 1998
Article 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:

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

  2. On the Data menu, click Parse.

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

  4. 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: 3.00 4.00 4.00a XL4 XL3 curly curled
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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.