ACC: Imported MS Excel Carriage Returns Become Vertical Bars (115576)

SYMPTOMS

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.

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:

Start Microsoft Access and open any database.
Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit

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

Create a new update query. Drag the field that you want converted to the query grid.
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.
Run the query. Note that text containing vertical bars is divided into separate lines with carriage returns.

MORE INFORMATION

Steps to Reproduce Behavior

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
Save the spreadsheet and quit Microsoft Excel.
Start Microsoft Access and open any database.
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.
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.
In all versions, select the Microsoft Excel file that you created in step 1, and then click Import.
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.