ACC: Imported MS Excel Carriage Returns Become Vertical Bars

ID: Q115576

The information in this article applies to:

SYMPTOMS

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

When you import a Microsoft Excel spreadsheet into Microsoft Access, the carriage return (CR) character appears as a vertical bar or a small box, depending on your version of Microsoft Excel and Microsoft Access. For simplicity, this article refers to vertical bars to indicate either vertical bars or small boxes.

For example, if you import a Microsoft Excel spreadsheet mailing list with complete addresses stored in single cells formatted with carriage returns, the addresses appear in Microsoft Access as single lines with vertical bars between the address items.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

CAUSE

The CR character used in Microsoft Excel (ALT+ENTER) differs from that used in Microsoft Access (CTRL+ENTER). As a result, the CR characters in Microsoft Excel spreadsheets are not parsed into carriage returns in Microsoft Access, but into small vertical bars.

RESOLUTION

You can use the following sample user-defined function to convert the CR character used in Microsoft Excel to the CR character used in Microsoft Access. To create the function, follow these steps:

1. Start Microsoft Access and open any database.

2. Create a module and type the following line in the Declarations 

   section if it is not already there:

      Option Explicit
3. Type the following procedure. 

   NOTE: In the following sample code, an underscore (_) at the end of a
   line is used as a line-continuation character. Remove the underscore
   from the end of the line when re-creating this code in Access Basic.

      Function ChangeStr (s As Variant, a As String, n As String,_
                          c As Integer) As Variant
         ' This function changes all substrings "a" and string "s"
         ' to "n." The parameter "c" has the same function as in the
         ' InStr() function.
         Dim temp As String, pos As Integer
         temp = ""
         If IsNull(s) Then
            ChangeStr = Null
            Exit Function
         End If
         If a = "" Or s = "" Then
            ChangeStr = s
            Exit Function
         End If
         pos = InStr(1, s, a, c)
         While pos > 0
            temp = temp & Mid$(s, 1, pos - 1) & n
            s = Right$(s, Len(s) - pos - Len(a) + 1)
            pos = InStr(1, s, a, c)
         Wend
         ChangeStr = temp & s
      End Function
3. Create a new update query. Drag the field that you want converted to 
   the query grid.
4. In the Update To row of the query grid, type the following expression: 

      ChangeStr([<fieldname>],Chr$(10),Chr$(13) & Chr$(10),0)

   where <fieldname> is the name of the field that you want to convert.
5. Run the query. Note that text containing vertical bars is divided into 
   separate lines with carriage returns.

MORE INFORMATION

Steps to Reproduce Behavior

1. In Microsoft Excel, create a spreadsheet and type the following data 

   into cell A1:

      Fred Test
      111 Main
      Anytown, USA

   To insert a new line within a cell, press ALT+ENTER. Then type the
   following data into cell A2:

      Joe Test
      222 Broadway
      Anytown, USA
2. Save the spreadsheet and quit Microsoft Excel.

3. Start Microsoft Access and open any database.

4. In Microsoft Access 7.0 and 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.
5. In Microsoft Access 7.0 and 97, in the Import dialog box, select 
   Microsoft Excel (*.xls) in the Files of type box.

   In Microsoft Access 2.0, in the Import dialog box, select Microsoft
   Excel 2.0, 4.0, or 5.0, depending on the version of Microsoft Excel
   you are using, and then click OK.
6. In all versions, select the Microsoft Excel file that you created in 
   step 1, and then click Import.
7. In Microsoft Access 7.0 and 97, in the Import Spreadsheet Wizard, note 
   the vertical bars in the Sample data box. Click Cancel to return to the
   Database window.

   In Microsoft Access 2.0, click OK in the Import Spreadsheet Options
   dialog box. Close the Select File dialog box and open the new table.
   Note the vertical bars between the data elements of each field value.

REFERENCES

For more information about importing spreadsheet data, search for "spreadsheets, importing or linking" using the Microsoft Access for Windows 95 Help Index.

Additional query words: pipes 

Keywords          : kbinterop
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb

Last Reviewed: November 20, 1998