ACC2000: Imported Excel Carriage Returns Become Square Boxes
This article applies only to a Microsoft Access database (.mdb).
For example, if you import a Microsoft Excel spreadsheet mailing listwith complete addresses stored in single cells formatted with carriagereturns, the addresses appear in Microsoft Access as single lines withsquares between the address items.
- Start Microsoft Access and open any database.
- In the Database window, click Module, click New, and then type the following line in the Declarations section if it is not already there:
- 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 & strOriginalEnd Function
- Create a new query in Design view, and add the table containing the inappropriate text data.
- On the Query menu, click Update Query.
- Drag the field that you want converted to the query design grid.
- 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.
- Run the query.
NOTE: You may need to increase the row height in Datasheet view to observe the multiple lines.
Steps to Reproduce Behavior
- 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 ChaiThen type the following data into cell A2:
Anytown, USAKaren Berge
- Save the spreadsheet and quit Excel.
- Start Access and open any database.
- On the File menu, point to Get External Data, and then click Import.
- In the Import dialog box, click Microsoft Excel (*.xls) in the Files of type box.
- Click the Excel file that you saved in step 2, and then click Import.
- 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.
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.
Article ID: 210372 - Last Review: 12/05/2015 11:30:47 - Revision: 2.0
- kbnosurvey kbarchive kbinterop kbprb KB210372