Line breaks in data imported from Excel

[drglov]

I have imported an Excel spreadsheet into an Access database. In Excel, a line break in a cell is entered by ALT+Enter. After importing my spreadsheet, all my line breaks appear as "□" and the text all runs together (no line breaks) in the table. The "□"s are also printed in reports. I need a way to either (1) make the "□"s appear as line breaks (e.g., by changing some Access property) or to convert the "□"s to something that shows as a line break in an the Access table memo field (e.g., CNTRL+Enter). Can anyone tell me how to make this happen?

I've tried to copy/paste the "□" into the Find/Replace dialog, but nothing appears when I paste. I understand the memo field is unicode, but I have not been able to determine what the "□" character actually is. It is not the same as the only similar squared shaped character I find in the Arial character map (U+25A1: White Square). I can copy and paste the "□", if surrounded by some other text, e.g., "xxx□yyy", into a Notepad document and it shows up as "xxx□yyy". Pasting the same into a Word doc and I get
"xxx
yyy" with the line break properly displayed. I can copy and paste just the □ itself in Notepad.

 

[Sergeant]

For what it's worth:

Cells.Replace What:=Chr(10), Replacement:=Chr(32)

will replace all those manual line breaks with spaces (in Excel).
It may be adaptable to Access table find/replace or update query???

 

[neileg]

The line breaks won't work in table view, but have you looked at the data in a form or a report? You may find that it works there.

 

[drglov]

Thanks for the suggestions. The problem I referenced does occur in reports as well as table view. In doing some more research, I found references to using the Replace function in an update query to do the job. Chr(10) does match the ALT+Enter from Excel, and in a select query I am able the enter "Cntrl+Enter" as the replacement and get the correct results in a query output. But when I change to query to update query, it won't allow the Cntrl+Enter characters. So if I could determine what the ASCII code is for the character entered in Access when you enter Cntrl+Enter, I would probably be in business. I tried some of the obvious options in ASCII 0-128, but no luck.

Expr1: Replace([ColumnName],Chr(10),Chr(???))

 

[Sergeant]

Ctl+Enter in Access produces Chr(13)

 

[jfagan]

I've had similar problems with this issue all day long.

I have a memo field with rich-text formatting. I wanted to import some data that is about 520+ characters with line-breaks. When importing the data I found I didn't even have the Chr(10) imported.

It seems the formatting is stored as HTML in table. So I prepared the Excel sheet by replacing all the line breaks with <br>. When the data is viewed in the table you see <br>, but when viewed in the forms you see line breaks. Perfect.