Access import not handling ALT+ENTER char from Excel

[Guest]

When importing from Excel into Access, the ALT+ENTER (CRLF) char gets
translated to a graphical box when giong from a multiple line text Excel cell
to an Access memo field. How do I get Access to recognize the CRLF so a
subsequent report will display the text properly?

 

[Rick Brandt]

When importing from Excel into Access, the ALT+ENTER (CRLF) char gets
translated to a graphical box when giong from a multiple line text Excel cell
to an Access memo field. How do I get Access to recognize the CRLF so a
subsequent report will display the text properly?

I believe the issue is that Excel doesn't use both the CR and LF but only one of
them (LF I think), whereas Access requires both in CRLF order. You could run an
update query with the Replace() function to replace the single character with
the CrLf pair.

 

[Douglas J. Steele]

Actually, the problem is that Alt-Enter isn't CRLF (Chr(13) + Chr(10)). It's
only a Line Feed (Chr(10)). You need to use the Replace function to convert
all LF to CRLF:

Replace(TheValue, Chr(10), Chr(13) & Chr(10))

 

[Jim]

I would love to reopen this four-year-old issue, as it's been plaguing me for
a while now. I came to this forum and was excited to see my exact problem
captured in the subject header, but the end of the thread isn't quite what
I'm looking for.

Alt-Enters in the middle of an Excel cell are read by MS Access as an
end-of-record marker. By way of example, the spreadsheet I need to import
into Access has rows with 20 columns. The Alt-Enter character occurs in the
4th column of the 10th row. My first nine rows in Access will therefore have
the appropriate 20 columns, but the 10th row STOPS at the Alt-Enter, and the
5th column of row 10 is wrongly treated as the first column of row 11.

I need to be able to replace that Alt-Enter character with something less
impactful on the import, such as a comma. I have tried these variations
(vbScript) without success:

TheVariable=act.readline
TheVariable=replace(TheVariable,chr(10),", ")
TheVariable=replace(TheVariable,chr(13),", ")
TheVariable=replace(TheVariable,chr(138),", ")
TheVariable=replace(TheVariable,chr(141),", ")
TheVariable=replace(TheVariable,chr(166),", ")

The chr(166) was an attempt based on having read that Alt-Enter is part of
the EXTENDED ascii set (achieved by holding Alt and pressing 0166 on the
numeric keypad), but none of these work.

Can anyone tell me what to do to render this linefeed character as something
harmless in MS Access?

Thanks very much!

 

[Jim]

Sorry -- I did not mention that this is a web app that imports Excel data
into an Access database. That's why the language is vbScript. Any solution
that involves opening the desktop Excel or Access app (like
http://support.microsoft.com/kb/115576) won't solve this for me.