ACC: How to Import a Word Table Containing Carriage Returns

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 demonstrates a method 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 (~):

       a. On the Edit menu, click Replace.
   

       b. In the Find And Replace dialog box, type the following characters,
          and then click Replace All:
   

             Find what: ^p  (the symbol for a carriage return)
             Replace with: ~
   
       c. Click OK when you receive a message that the search and replace is
          complete.
   
       d. Close the Find And Replace dialog box.
   
   

2. Convert the table to tab-delimited text format:

       a. Click in any cell in the table.
   

       b. On the Table menu, click Select Table.
   

       c. On the Table menu, click "Convert Table to Text."
   

       d. 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:

       a. On the Edit menu, click Replace.
   

       b. In the Find And Replace dialog box, type the following characters,
          and then click Replace All:
   

             Find what: ^p
             Replace with: "^p"
   
       c. Click OK when you receive a message that the search and replace is
          complete.
   
       d. Close the Find And Replace dialog box.
   
   

5. Replace all TAB characters in the document with a TAB character surrounded by quotation marks:

       a. On the Edit menu, click Replace.
   

       b. In the Find And Replace dialog box, type the following characters,
          and then click Replace All:
   

             Find what: ^t
             Replace with: "^t"
   
       c. Click OK when you receive a message that the search and replace is
          complete.
   
       d. Close the Find And Replace dialog box.
   
   

6. Restore the original paragraph marks in your document by replacing all tildes (~) with paragraph marks:

       a. On the Edit menu, click Replace.
   

       b. In the Find And Replace dialog box, type the following characters,
          and then click Replace All:
   

             Find what: ~
             Replace with: ^p
   
       c. Click OK when you receive a message that the search and replace is
          complete.
   
       d. Close the Find And Replace dialog box.
   
   

7. On the File menu, click Save As.

8. In the Save As dialog box, select Text Only (*.txt) in the Save As Type box, and type Address.txt in the File Name box. Click Save.

9. Close the document and quit Microsoft Word.

Importing the Table into Microsoft Access

1. Start Microsoft Access and open the sample database Northwind.mdb (or Nwind.mdb in version 2.0).

2. In Microsoft Access 7.0 or 97, on the File menu, point to Get External Data, and then click Import.

   In Microsoft Access 2.0, on the File menu, click Import.

3. In Microsoft Access 7.0 or 97, 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.

   In Microsoft Access 2.0, in the Import dialog box, select Text (Delimited), and then click OK. In the Select File dialog box, select Address.txt in the appropriate folder, and then click Import.

4. In Microsoft Access 7.0 or 97, click Next in the first screen of the Import Text Wizard (or Text Import Wizard in Microsoft Access 7.0). 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.

   In Microsoft Access 2.0, in the Import Text Options dialog box, click to select the "First Row Contains Field Names" check box, and then click the Options button. Select {tab} in the Field Separator box, select " in the Text Delimiter box, and then click OK 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 carriage returns, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q153373
   TITLE       ACC: Importing Fixed-Width Text with Embedded Carriage
               Returns

   ARTICLE-ID: Q149946
   TITLE       ACC: Text Import Wizard doesn't Import Data Correctly

   ARTICLE-ID: Q141700
   TITLE     : ACC95: How to Import Microsoft Word Tables Using OLE
               Automation