How to import a Word table that contains carriage returns (290169)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q290169
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 198919.

SUMMARY

When you import a Microsoft Word table that contains carriage returns in its cells, the table is not imported correctly because Microsoft Access interprets each carriage return/line feed as a new record delimiter. To avoid this problem, you can surround the contents of the table cells with quotation marks so that the carriage returns are interpreted as part of the text in the cell, and not as the end of a record.

This article shows you how to export a Word table that contains carriage returns into a tab-delimited text file that you can import into Microsoft Access.

MORE INFORMATION

In this example, you convert the table and its contents to a tab-delimited format; then, you save the file as text and import it into a Microsoft Access database. The techniques in this example assume that you know how to create a table in Microsoft Word, and that the table is the only object in your document.

Creating the Microsoft Word table

  1. Start Microsoft Word and create a new blank document.
  2. Insert a table with three columns and four rows.
  3. Enter the following data in the table, including the column headings. Press ENTER at the end of the first line in each address so that the city, state, and postal code appear on a separate line:
          +-------------+--------------+----------------------+
      |FirstName    |LastName      |Address               |
      +-------------+--------------+----------------------+
      |Bob          |White         |12 Rocky Rd.          |
      |             |              |Anywhere, TX  43210   |
      +-------------+--------------+----------------------+
      |Sal          |Vation        |1000 Pearly St.       |
      |             |              |Noble, NM  33333      |
      +-------------+--------------+----------------------+
      |Art          |Work          |120/98 Scenic Way     |
      |             |              |Goodview, WI  12332   |
      +-------------+--------------+----------------------+
  4. Save the document as Address.doc, and leave the document open.

Formatting the document as tab-delimited text

  1. Temporarily replace all paragraph marks (carriage returns) in the document with a character that is not already used in your table, such as the tilde (~). To do so, follow these steps:
    1. On the Edit menu, click Replace.
    2. In the Find and Replace dialog box, type ^p in the Find what box and type ~ in the Replace with box.
    3. Click Replace All.
    4. When you receive a message that the search and replace is complete, click OK.
    5. Close the Find and Replace dialog box.
  2. Convert the table to tab-delimited text format. To do so, follow these steps:
    1. Click in any cell in the table.
    2. On the Table menu, point to Select, and then click Table.
    3. On the Table menu, point to Convert, and then click Table to Text.
    4. In the Convert Table to Text dialog box, click Tabs, and then click OK.
  3. Disable smart quotes. To do this, follow these steps:
    1. On the Tools menu, click AutoCorrect, and then click the AutoFormat As You Type tab.
    2. Under Replace as you type, click to clear the "Straight quotes" with "smart quotes" check box, and then click OK.
  4. Type a quotation mark (") at the very beginning of the document, to the left of the word "FirstName." This is the first text qualifier that will surround each field in the table.

    NOTE: If the text in your table contains any quotation marks, you must remove or replace them with another character, such as an apostrophe ('), before you proceed; otherwise, the table will not be imported correctly.
  5. Replace all paragraph marks in the document with a paragraph mark surrounded by quotation marks. To do so, follow these steps:
    1. On the Edit menu, click Replace.
    2. In the Find and Replace dialog box, type ^p in the Find what box and then type "^p" in the Replace with box.
    3. Click OK when you receive a message that the search and replace is complete.
    4. Close the Find and Replace dialog box.
  6. Replace all TAB characters in the document with a TAB character surrounded by quotation marks. To do so, follow these steps:
    1. On the Edit menu, click Replace.
    2. In the Find and Replace dialog box, type ^t in the Find what box and type "^t" in the Replace with box.
    3. Click Replace All.
    4. Click OK when you receive a message that the search and replace is complete.
    5. Close the Find and Replace dialog box.
  7. Restore the original paragraph marks in your document by replacing all tildes (~) with paragraph marks. To do so, follow these steps:
    1. On the Edit menu, click Replace.
    2. In the Find and Replace dialog box, type ~ in the Find what box and type ^p in the Replace with box.
    3. Click Replace All.
    4. Click OK when you receive a message that the search and replace is complete.
    5. Close the Find and Replace dialog box.
    6. On the File menu, click Save As.
    7. In the Save As dialog box, select Text Only in the Save as type box, and type Address.txt in the File Name box. Click Save.
    8. Close the document and quit Word.

Importing the table into Access

  1. Start Access and open the sample database Northwind.mdb.
  2. On the File menu, point to Get External Data, and then click Import.
  3. In the Import dialog box, select Text Files in the Files of type box, and then select Address.txt in the appropriate folder. Click Import.
  4. Follow these steps:
    1. In the Import Text Wizard, click Delimited - Characters such as comma or tab separate each field, and click Next.
    2. Under Field Delimiter, click Tab.
    3. In the Text Qualifier box, click ", and then click Include Field Names on First Row.
    4. Click Finish to import the table.
  5. Open the Address table in Datasheet view.
  6. On the Format menu, click Row Height. Set the row height to 25, and then click OK.
  7. Click anywhere in the Address column, and then on the Format menu, click Column Width. Set the column width to 50, and then click OK. Note that you can see both lines of each address, and that each address includes the carriage return.

REFERENCES

For more information about importing files that contain carriage returns, click the following article numbers to view the articles in the Microsoft Knowledge Base:

208404 Fixed-width text with embedded carriage returns is imported incorrectly

210057 Import Text Wizard does not import data correctly

Modification Type:MinorLast Reviewed:12/27/2005
Keywords:kbhowto KB290169
©2004 Microsoft Corporation. All rights reserved.