Import Multiline Text data from Excel

[Max Yaffe]

Dear Group,

I'm trying to import or link text data from an Excel file. Some cells
contain multiline strings with CR-LF pairs terminating lines. I know
this because I copied the strings into a text editor & looked at the
hex files.

However, when I import the table into Access, the CR-LF pairs are
replaced with CR only. These, then, are not formatted correctly in
Access. Same thing happens when I link directly to the table in the
spreadsheet.

Is there any cure for this other than editing the data after import?

I'm using Access2003 & Excel2003 or Excel2000

Thanks,
Max

 

[Ken Snell \(MVP\)]

EXCEL uses just the LF character for multiple lines within a single cell.
ACCESS uses CR+LF characters for multiple lines within a field.

My assumption is that you need to import the data to ACCESS, then run an
update query that changes all LF characters to the combination of CR+LF
characters:

UPDATE TableName
SET FieldName = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1, 1);