ACC2000: How to Import a Word Table Containing Carriage Returns (198919)


The information in this article applies to:

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

For a Microsoft Access 2002 version of this article, see 290169.

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 the ENTER key 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, click Select Table.
    3. On the Table menu, click Convert Table to Text.
    4. In the Convert Table to Text dialog box, click Tabs, and then click OK.
  3. 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.
  4. 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 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.
  5. 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.
  6. 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. Click Next on the first screen of the Import Text Wizard. Click to select the First Row Contains Field Names check box on the second screen of the Wizard, and then 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 additional information about importing files that contain carriagereturns, click the article numbers below to view the articles in the Microsoft Knowledge Base:

208404 ACC2000: Fixed-Width Text with Embedded Carriage Returns Is Imported Incorrectly

210057 ACC2000: Text Import Wizard Does Not Import Data Correctly

Modification Type:MajorLast Reviewed:6/30/2004
Keywords:kbhowto kbinterop KB198919
©2004 Microsoft Corporation. All rights reserved.