Thursday, June 10, 2010

MS Access - How to keep line break when importing from Excel

Scenario: You have an Excel sheet with one column containing text that also has line breaks with the cell, for example bullet lists. You want to import this table to Access and keep the line breaks untouched.


  • Copy and paste the data into Access (or use the import wizard)
  • Make sure that the current column is of type Text or Memo/PM and also check that the field property Unicode Comporession is set to No and the property Text Format is set to Plain Text.
  • Create an update query, where you set the field Update to to Replace([ColumnName];Chr(10);Chr(13) & Chr(10))
  • Run the query