ACC2000: Imported Excel Carriage Returns Become Square Boxes

Article translations Article translations
Article ID: 210372 - View products that this article applies to.
This article was previously published under Q210372
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SYMPTOMS

When you import a Microsoft Excel spreadsheet into Microsoft Access, the carriage return character (<CR>) appears as a small square.

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 squares between the address items.

CAUSE

This behavior occurs because the carriage return character (<CR>) used in Excel (ALT+ENTER) differs from that used in Access (CTRL+ENTER). As a result, the <CR> characters in Excel spreadsheets are not parsed into <CR> characters in Access, but into graphics characters.

RESOLUTION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To resolve this behavior, you can convert the carriage return characters used in Excel to those used in Access. To create a user-defined function to do that, follow these steps:
  1. Start Microsoft Access and open any database.
  2. In the Database window, click Module, click New, and then type the following line in the Declarations section if it is not already there:
    Option Explicit
    					
  3. Type the following procedure:
    Function ChangeStr (strOriginal As Variant, strOldChar As String, strNewChar As String, intMatchCase As Integer) As Variant
       ' This function changes all substrings strOldChar in string strOriginal
       ' to strNewChar.
       ' The parameter intMatchCase has the same purpose as in the
       ' InStr() function, i.e. 1 makes the function case-sensitive, 0 does not
       Dim temp As String, pos As Integer
       temp = ""
       If IsNull(strOriginal) Then
          ChangeStr = Null
          Exit Function
       End If
       If strOldChar = "" Or strOriginal = ""  Then
          ChangeStr = strOriginal
          Exit Function
       End If
    
       pos = InStr(1, strOriginal, strOldChar, intMatchCase)
       While pos > 0
          temp = temp & Mid$(strOriginal, 1, pos - 1) & strNewChar
          strOriginal = Right$(strOriginal, Len(strOriginal) - pos - Len(strOldChar) + 1)
          pos = InStr(1, strOriginal, strOldChar, intMatchCase)
       Wend
       ChangeStr = temp & strOriginal
    End Function
    					
  4. Create a new query in Design view, and add the table containing the inappropriate text data.
  5. On the Query menu, click Update Query.
  6. Drag the field that you want converted to the query design grid.
  7. In the Update To row of the query design grid, type
    ChangeStr([fieldname],Chr$(10),Chr$(13)& Chr$(10),0)
    where fieldname is the name of the field that you want to convert.
  8. Run the query.
Notice that text is now presented as separate lines with carriage returns.

NOTE: You may need to increase the row height in Datasheet view to observe the multiple lines.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Excel, create a spreadsheet and type the following data into cell A1, pressing ALT+ENTER to insert a new line within a cell:
    Sean Chai
    111 Main
    Anytown, USA
    Then type the following data into cell A2:
    Karen Berge
    222 Broadway
    Anytown, USA
  2. Save the spreadsheet and quit Excel.
  3. Start Access and open any database.
  4. On the File menu, point to Get External Data, and then click Import.
  5. In the Import dialog box, click Microsoft Excel (*.xls) in the Files of type box.
  6. Click the Excel file that you saved in step 2, and then click Import.
  7. In the Import Spreadsheet Wizard, note the graphic squares in the Sample data for spreadsheet data box. Click Cancel to return to the Database window.

REFERENCES

For more information about importing spreadsheet data, click Microsoft Access Help on the Help menu, type import or link data from a spreadsheet in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about InStr() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type InStr in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred.

Properties

Article ID: 210372 - Last Review: June 23, 2005 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbinterop kbprb KB210372

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com